If statement with standard deviation not giving correct value

Originally Posted by excel12121

=STDEV(IF(('All Subject Data'!$C$2:$C$5220=6)*('All Subject Data'!$D$2:$D$5220=4.3)*('All Subject Data'!$E$2:$E$5220=1000)*('All Subject Data'!$B$2:$B$5220="Male"),'All Subject Data'!$J$2:$J$5220))

Your formula is syntactically correct.

Did you enter it as an array formula?

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

Originally Posted by excel12121

I even checked it both if it includes blanks as zeros (there are a few but not many) and it still was way way off the actual stdev.

Empty cells in the range 'All Subject Data'!$J$2:$J$5220 will be evaluated as 0.

See post #4 for a method to account for those.

Originally Posted by excel12121

=AVERAGEIFS('All Subject Data'!$J:$J,
'All Subject Data'!$B:$B,"N",
'All Subject Data'!$C:$C,6,
'All Subject Data'!$D:$D,4.3,
'All Subject Data'!$E:$E,1000)

=STDEV(IF(('All Subject Data'!$C$2:$C$5220=6)
*('All Subject Data'!$D$2:$D$5220=4.3)
*('All Subject Data'!$E$2:$E$5220=1000)
*('All Subject Data'!$B$2:$B$5220="Male"),
'All Subject Data'!$J$2:$J$5220))

Note that the test for column B differs: "N" v. "Male". Does that explain the "wildly different result"?

Also, note that the std dev is based on only rows 2:5220, whereas the average is based on the entire column. It is better not to reference entire columns (B:B). But is there more data in the columns that you are including in your "hand" calculation, but excluding in the STDEV formula?

Are any of J2:J5220 empty? If so, any that meet all of the criteria are treated as zero in the STDEV formula, but they are ignored in the AVERAGEIF formula. If that is a possibility, you might try the following modification:

=STDEV(IF(('All Subject Data'!$C$2:$C$5220=6)*('All Subject Data'!$D$2:$D$5220=4.3)
*('All Subject Data'!$E$2:$E$5220=1000)*('All Subject Data'!$B$2:$B$5220="Male")
*('All Subject Data'!$J$2:$J$5220<>""), 'All Subject Data'!$J$2:$J$5220))

Finally, are you aware of the difference between STDEV and STDEVP; and are you using the same one for your "hand" calculations?

Usually, that does not produce a "wildly different result". But that depends on the magnitude of the number of data that meets all criteria.

If you do not get a quick resolution of the problem, I suggest that you attach a (redacted) Excel file that demonstrates the problem.

Originally Posted by excel12121

There are some empty cells but not enough to warrant such a huge difference. With hand calculations, i get like 1.4, with stdev(if....) i get like 6.41

Perhaps. It really depends on the magnitude of the data and the relative number of empty cells. See the example below. Note that the correct STDEV is about 0.89, whereas the incorrect STDEV is about 5.09. The difference is partly due to the change in the average as well as the number of data, which is caused by interpreting empty cells as zero in the latter case.

	A	B
1	data	stats
2	13.10	0.89	B2: =STDEV(A2:A11)
3		11.94	B3: =AVERAGE(A2:A11)
4	11.20
5	11.30	5.09	B5: { =STDEV(IF(A2:A11>=0,A2:A11)) }
6	12.30	9.55	B6: { =AVERAGE(IF(A2:A11>=0,A2:A11)) }
7	11.20
8	11.40
9
10	13.40
11	11.60

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.

Originally Posted by joeu2004

Note that the test for column B differs: "N" v. "Male". Does that explain the "wildly different result"?

Originally Posted by excel12121

No there are three choices, male, female, or just N for a non report.

I understand that. I don't believe you understood my comment.

My point is: your AVERAGEIF formula calculates the average of J cells where B cells are "N", but your STDEV formula implicitly uses the average of J cells where B cells are "Male".

In your hand calculation, did you extract rows where B cells are "N" (like your AVERAGEIF), or did you extract "Male" (like your STDEV)?

If you calculated std dev manually -- sqrt of sum of (x[i] - mean)^2 / (n - 1) -- did you use the average (mean) of J cells where B cells are "N" (like your AVERAGEIF), or did you use the average of "Male" (like your STDEV)?

Originally Posted by excel12121

will try to attach a file

Since you didn't, I can imagine that you are struggling with one of two problems or both.


First, this forum has a file size limitation; 1 MB, I think. If your example file is too big, upload it to a file-sharing website, and post the public/share URL in a response here. Some free file-sharing websites:

Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files

I avoid Google docs.


Second, if redaction is tedious, consider doing the following instead.

1. Open the original file in Excel.

2. In the same Excel instance, create a new workbook.

3. Copy B2:B5220 from the original file, and paste-value into B2:B5220 of the new workbook.

4. Repeat #3 with the other relevant ranges in columns C, D, E and J.

5. Change the tab label in the new workbook to "All Subject Data" without quotes.

6. Close the original workbook, and save the new workbook.

7. Use the new workbook to demonstrate your formulas both with the original data and with your hand calculations.

Be sure that the new workbook demonstrates the problems and your "by hand" work-around. Sometimes, copy-and-paste-value changes things unintentionally.