Hello,
please see attached document, go to tab “roh12”, compare K12 and K18. Both cells show the same formula pointing to the same range of cells. In K12 I see a 0 as result and in K18 I see a value. Doing the same in Excel in K18 I get a different result. Anyone an idea what am I missing here?
Thanks!
Zellbio_bearbeitet.ods (207.1 KB)
It is completely unclear, what confuses you. And also - what could possibly be different in Excel (no it can’t; there you will see exactly the same).
Your K12 and K18 have formula =STDEV.P(IF(F2:F807=20;G2:G807)). It works by intersection: from K12, it checks F12, and if it’s 20, it takes G2:G807 and passes it to STDEV.P. Same way for K18: it checks F18, and if it’s 20, it takes G2:G807 and passes it to STDEV.P. The “by implied intersection” behavior is standard for all spreadsheet software, and also standardized in ODF.
I see that your result in K18 is incorrect.
I filtered column F after 20, then copied the values in column G and pasted them elsewhere, and applied STDEV.P to the new values. The result is:
If you enter your formula as a matrix formula (Ctrl+Shift+Enter), the result is correct (K12).
Zellbio_bearbeitet.ods (198,2 KB)
Is it really correct? The deviation will take into account lots of zeroes - i.e., the “unmatching” rows will still contribute to the result - is that really wanted?
without explizit matrix-context:
=STDEV.P(FILTER(G$2:G$807;F$2:F$807=20))
guess…no!?
I am somewhat confused,
=STDEV.P(IF(F2:F807=20;G2:G807))
means for me collect all rows where in F2 to F807 you find a value of 20 and ignore/discard all other rows from the matching rows calculate the standard deviation from the values in column G. Am I here on the wrong track?
Yes. I already provided the explanation. And even the solution by @PKG (using the array formula) doesn’t exactly do what you expect: the “non-matching” rows will still be there in the array passed to the STDEV.P - what’s strange is, that IF gives an empty string in these places, not the expected 0 (and that might be a bug, actually). Use the correct solution by @karolus - it really only takes matching rows, and nothing else.
The IF() function doesn’t “collect” anything. It returns one of two alternatives given as expressions (subformulas).
If the second alternative (ELSE-case) is missing, it returns FALSE() if the condition comes out false.(IMO it should return an error in this case to avoid confusion. However, you can’t get good software if you need to be compatible with badly designed software.)
To “collect regarding conditions” we have the FILTER() function in recent LibO Calc.
The only clearly correct formula suggested above is the one by @karolus.
It avoids possible confusion by an IF() part.
Some of the results using the IF() look buggy to me because they don’t include logical FALSE() as specified for STDEVP() (also written as STDEV.P() here). A “wrongly correct” result is also a bug.
Specification: (odf 1.3)
6.18.74 STDEVP
Summary: Calculates the standard deviation using the population of a random variable, including values of type Text and Logical.