STDEV IF when IF="0"
Hello,
I am attempting to calculate standard deviations separately for different conditions (the equivalent of the AVERAGEIF function but for standard deviations).
Sheet 1, Column F contains which condition the row is (values = 0, 0.5, 0.75, etc.)
Sheet 1, Column G contains the data I want to calculate standard deviations with
Sheet 2, Column A specifies the conditions (values = 0, 0.5, 0.75, etc)
The following array formula seems to be working:
=STDEV(IF(sheet1!F:F=sheet2!A3,sheet1!G:G))
EXCEPT when the condition is the "0" value. My best guess is that Excel is not discriminating between a "0" in Column F versus a blank cell in Column F. Any way to fix this?
I'd appreciate any help!
Best wishes
The If comparison looks
The If comparison looks slightly suspect to me because you're comparing a range to a single cell??
IF(sheet1!F:F=sheet2!A3
Try to replace "0" with blank
I am not sure, but it might be due to the fact that STDDEV uses numeric values as arguments, and zero creates problem. Anyway, Excel treats blanks as zeros. Try to replace zeros with blanks and check results. If the problem persists, let me know.