Excel Formula: Standard Deviation IF

[Legacy 323112]

Hi all,
Looking for some help with a standard deviation formula, integrated with an if statement. I'm using 'AVERAGEIF' and have read many articles about how to achieve something similar for the standard deviation using an array formula, i.e.;

{=STDEV(IF(B3:B8="TEST1",D3:D8,""))}

This appears to work, however this does not seem to be a successful solution for me when I increase the ranges to 2000 rows. I'm using Excel 2007, and the data I have in these columns is filtered so the number of visible rows will vary (the data filtered out include cells that are empty or have values of '#N/A').

Just wondered if anyone has any suggestions?
Any advice would be most appreciated.

Thanks

 

[joeu2004]

using an array formula, i.e.;
{=STDEV(IF(B3:B8="TEST1",D3:D8,""))}
This appears to work, however this does not seem to be a successful solution for me when I increase the ranges to 2000 rows. I'm using Excel 2007, and the data I have in these columns is filtered so the number of visible rows will vary (the data filtered out include cells that are empty or have values of '#N/A'). Just wondered if anyone has any suggestions?

{=STDEV(IF(ISNUMBER(D3:D2000),IF(B3:B2000="TEST1",D3:D2000)))}

Consider using STDEVP if you want the actual std dev, not the std dev of a sample for the purpose of estimating the actual std dev of a larger population.

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

[EDIT] I assumed you mean that some values in column D (not B) are empty or #N/A. But then I wondered why column B has "TEST1" in such cases. If you mean that some values in column B are #N/A, then:

{=STDEV(IF(ISERR(B3:B2000)=FALSE,IF(B3:B2000="TEST1",D3:D2000)))}

Empty values in column B are covered by the test B3:B2000="TEST1".

 

[Legacy 323112]

Thank you for your response. Unfortunately, the formula does not work.

In answer to your questions, data in rows A:E are filtered based on values in row D. If any values are blank or =#N/A, then the rows will be filtered out. Every row that is filtered out will have a value in column B of #N/A. If the row is not filtered out then the column B will be either TEST1, TEST2 etc, and each will have a unique number in column D.

By using the ISERR formula below, I result in #N/A. If I used the ISNUMERIC formula, I result in a #NAME error. If I trace #NAME error, the error points to the bottom of row 106. The next visible row after 106 is 163, and this is the first instance in the sheet where rows have been filtered out.

Any suggestions?

Thanks again

{=STDEV(IF(ISNUMBER(D3:D2000),IF(B3:B2000="TEST1",D3:D2000)))}

Consider using STDEVP if you want the actual std dev, not the std dev of a sample for the purpose of estimating the actual std dev of a larger population.

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

[EDIT] I assumed you mean that some values in column D (not B) are empty or #N/A. But then I wondered why column B has "TEST1" in such cases. If you mean that some values in column B are #N/A, then:

{=STDEV(IF(ISERR(B3:B2000)=FALSE,IF(B3:B2000="TEST1",D3:D2000)))}

Empty values in column B are covered by the test B3:B2000="TEST1".

 

[joeu2004]

data [...] are filtered based on values in row D. If any values are blank or =#N/A, then the rows will be filtered out. [....] By using the ISERR formula below, I result in #N/A. If I used the ISNUMERIC formula, I result in a #NAME error.

The #NAME error is because the function name is ISNUMBER, as I wrote it, not ISNUMERIC.

Based on your description, you can use the ISNUMBER formula instead of the ISERR formula.

However, you might get the #N/A error from the ISNUMBER formula after you correct the function name.

If so, I think it would be best if you would upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here. Any personal identification information should be redacted from the file.

Note: This forum's moderators don't like that. But that is quickest way for me to help you. The devil might be in details that we cannot see or that are hard to see if you simply post a table of formulas and values.

Alternatively, at the very least, copy the formula as it appears in the Formula Bar, and paste it into a response. Indicate whether or not the formula is surrounded by curly braces in the Formula Bar.

 

[Legacy 323112]

Thank you! I must have been using ISNUMERIC instead of ISNUMBER as it seems to be working fine now.
Thanks again!

The #NAME error is because the function name is ISNUMBER, as I wrote it, not ISNUMERIC.

Based on your description, you can use the ISNUMBER formula instead of the ISERR formula.

However, you might get the #N/A error from the ISNUMBER formula after you correct the function name.

If so, I think it would be best if you would upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here. Any personal identification information should be redacted from the file.

Note: This forum's moderators don't like that. But that is quickest way for me to help you. The devil might be in details that we cannot see or that are hard to see if you simply post a table of formulas and values.

Alternatively, at the very least, copy the formula as it appears in the Formula Bar, and paste it into a response. Indicate whether or not the formula is surrounded by curly braces in the Formula Bar.