CODEWITHSUNDEEP
excelaverage
Heiko
Heiko

Reputation: 159

Make excel stdev function take specific text as a certain number

I am trying to make excel's standard deviation function to consider a specific text found in the range as a number provided in another cell. Is there any way to do this? If possible, I would not want to use another column of cells as an intermediate step.

I have a series of measurements, however some values are below the detection limit. Instead of calculating the stdev with these values , I want to type 'LDL' (lower detection limit) into these cells. The stdev function should then, every time it encounters 'LDL', fetch the detection limit (LOD) from another cell. Example:

.... LOD= ...2 ....
.... .... .... ....
.... ...5 .... ....
.... ...8 .... ....
.... LDL. .... ....
.... ...7 .... ....
.... ---- .... ....
STD. =STDEV(B3:B6 but consider B5 as C1)

Thanks in advance for all solutions.

Upvotes: 0

Views: 88

Answers (2)

XOR LX
XOR LX

Reputation: 7742

Perhaps an array formula**:

=STDEV(0+SUBSTITUTE(B3:B6,"LDL",C1))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 2

pnuts
pnuts

Reputation: 59475

Please try: 

=(SUM(B3:B6)+COUNTIF(B3:B6,"LDL")*B1)/COUNTA(B3:B6)

Upvotes: 0

Related Questions