Formula works, then doesn't

[mhessnm]

Hello,

I am at a loss as to what is happening here. I have some survey data on one sheet, and I am putting together a summary on another sheet. I want mean, standard deviation of the sample, and median for some of the data. I am compiling the summary statistics both for all surveys and for surveys entered by month.

For standard deviation according to month on a series of questions, I use the following formula:

=IF(Sheet1!$A:$A="April",STDEV.S(Sheet1!$L:$L))

This works fine, and gives me the standard deviation for column L (formatted as general) for all entries in April.

This next formula returns FALSE:

=IF(Sheet1!$A:$A="April",STDEV.S(Sheet1!$M:$M))

It is the same exact formula, with the column reference changed in the standard deviation function. In stepping through the formula evaluation, whereas in the first formula it finds IF("April"="April"...) in the logical test and returns TRUE, in the second formula it finds IF(0="April"...) in the logical test and returns FALSE.

I have looked at the data, all of which is formatted as general and works fine in the formula until I reach column M - after that it returns FALSE instead of a standard deviation. I have tried changing the IF column reference and the STDEV.S column reference to the exact range with the same result. I have a header row, but I wouldn't think that should affect the logical test. I'm completely baffled. Am I missing something?

Thank you for your help.

 

[Eric W]

Try:

=STDEV.S(IF(Sheet1!$A$2:$A$1000="April",Sheet1!$L$2:$L$1000))

confirmed with Control+Shift+Enter.

 

[mhessnm]

Hi Eric,
Thank you, that worked! I hadn't thought of a solution like that. But, I'm still left with the question why my formula worked for column L, but then didn't when I changed the STDEV.S to column M. Do you have any insight?
Michael

 

[Eric W]

I don't think it actually worked right in either case. As it was coded, it would just return L1, if A1 = April. If A1 is something else, it would return FALSE. If you had confirmed it with Control+Shift+Enter, it still wouldn't have worked right, since it would process the whole column, but it would calculate the standard deviation for each individual cell, not for the range, then again, just return the top value. I'd actually have to see your data to see why it returned what it did. You can probably figure it out yourself, change the range from A:A to A1:A10 and L1:L10, then use the Evaluate Formula option from the Formula tab. You can see what it's trying to do.

 

[mhessnm]

Yeah, I did that on the original formula. It didn't yield any clues to me, only showing that for one "April" = "April" and TRUE and for the other "0 = April" and FALSE. I notice that in the case where there is a blank cell in the column, your formula calculates differently - it must not calculate the blank (which is actually what I prefer).

I'll spend some time trying to figure it out. If only they had a STDEV.SIF!

Thanks again for your solution!

 

[higrm]

You may be able to use a dynamic named range for the rows with the month and another named range for the column you want to do the standard deviation on. The range you want would be the intersection of those two ranges. Just a thought.

If I have some rows defined as a named range april and a column of values call numbers, this gives me a result:

=STDEV.S(april numbers)

 

[mhessnm]

Thank you, higrm - I will give it a try!