Ethan Brown said:
I'm not sure however that stdev in an array automatically
ignores the blanks.
I 'spose you could ready the STDEV help page <wink>. To wit:
"If an argument is an array or reference, only numbers in that array or
reference are counted. Empty cells, logical values, text, or error values in
the array or reference are ignored."
However, for a very different reason, I was wrong with my suggestion that
you can avoid the IF condition (C1:C36<>"") in your array formulas, using
your new example.
I will discuss that below. But I am not sure that is related to your
original problem.
If you still need help, please upload an example file that demonstrates your
original problem and post the URL here.
PS: Note that your newsreader or newserver changes the subject line each
time you post. That is confusing. Please try to work around that either by
setting an appropriate option, if one is provided, or by copying the
original subject line of the message you are responding to, adding the "Re:"
prefix if necessary.
Ethan Brown said:
You have sparse values in C2:C36; and the cells without values are empty (no
constant and no formula). Also, all the values in A2:A36 match the values
in E3 and E4, which are the same.
You have the following formulas and approximate results:
F2=0.127789: =STDEV(C2:C36)
F3=0.127789: =IF(OR(COUNTIF(A1:A36,E3)=1,E3=""),"",
STDEV(IF((A1:A36=E3)*(C1:C36<>""),C1:C36)))
F4=0.202208: {=IF(OR(COUNTIF(A1:A36,E4)=1,E4=""),"",
STDEV(IF((A1:A36=E4),C1:C36)))}
0.127789 is the correct result. You can demonstrate that with the formula
=STDEV(C3,C9,C13,C23,C36).
That demonstrates that STDEV does indeed ignore empty cells.
You can demonstrate that it also ignores cells with text, notably the null
string ("") that appears blank, by changing the range in F2 to C1:C36, which
is consistent with the range in F3 and F4. Note that A1 contains text, the
column title.
-----
However, there is still the question: why do F3 and F4 return different
results?
And that begs the question: why does F3 work at all?!
Note that F3 is not array-entered, as it should normally be.
It works only by accident because the formula is in row 3, and A3=E3 and
C3<>"". Consequently, STDEV(IF((A1:A36=E3)*(C1:C36<>""),C1:C36)) is
evaluated as STDEV(C1:C36).
If you copy the F3 formula from the Formula Bar (not the cell) and paste it
into, say, F8, you would see that the formula returns an error when you
press just Enter. It returns the correct result only when you press
ctrl+shift+Enter.
It is difficult to explain why the non-array-entered formula seems to work
(misleadingly) in F3. It is the reason why named references work in normal
formulas. It is treated as what I call an array-indexed formula: the row
number of the formula determines the values used from array references in
the formula.
If you don't understand that, no matter. It is not what you want to do,
presumably. However, it might explain why some array formulas "work" (i.e.
return a value, albeit often bogus) in one context, but return an Excel
error or the wrong value in another context. That is why array formulas are
risky to use, IMHO.
Let's move on to the formula in F4....
F4 is correctly array-entered.
It returns the wrong value because for C1:C36 corresponding to the
non-matching A1:A36, Excel substitutes zero in the IF() array result instead
of FALSE or "empty" (the null string). For example, for row 2, it is
equivalent to =C2. Note that =C2 returns zero when C2 is empty.
Consequently, the array-entered formula in F4 calculates
STDEV(0,0,C3,0,...,0,C9,...) instead of STDEV(C3,C9,...). Of course, the
zero-valued data throws off the std dev computation understandably.
Again, this is a mistake with my off-the-cuff suggestion to simplify the
original formula. Presumably it has nothing to do with your original
problem.
PS: F4 __seems__ to work when not array-entered as an accident of
implementation for the same reason that F3 seems to work. It is a
misleading illusion due to its position relative to the array references.
Always array-enter formulas that include expressions of the form
STDEV(IF((A1:A36=E4),C1:C36)).
