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.