0

Consider a column of a spread sheet, e.g. column A in the picture.

How can I get Libre Office to calculate the standard deviation of this column, but without considering values below a certain threshold (A14 in the picture)?

In the picture my desired expression, would only consider the values of cells A1, A4, A7 to A9, A10 and A11 in the standard deviation as if they were only ones in the column (sub-sample).

LibreOffice conditional deciation

As you can see I tried

=STDEV(IF(A1:A12>A14,A1:A12))

...and its array-form to no avail. I'm surprised non of these work as it goes against other claims in the OpenOffice forum and against my intuition of LibreOffice.

Edit:
The problems seems to be more of a bug in my version. Even in a fresh sheet it behaves unpredictable. Sometimes I have to remove the last round closing bracket ), than enter it manually again and then press Strg+Shift+Enter for array mode. Sometimes it doesn't work at all no matter what I do. Its incredible.

con-f-use
  • 3,772
  • 5
  • 39
  • 60
  • 1
    This formula works for me as array formula - result is `0.1020620726`. Did you enter the curly braces manually? I wonder why the opening bracket is placed **after** the leading equation sign. I think `FORMULA()` should return `{=STDEV(IF(A1:A12>A14,A1:A12))}` instead (opening bracket `{` before `=`)... (tested with LO Calc 4.4.2.2) – tohuwawohu Apr 27 '15 at 11:47
  • I'm very puzzled. Now the formula sheet is behaving completely erratic. The problem seems to be something else bug related. – con-f-use Apr 27 '15 at 12:57
  • 2
    Hmm - i've just tested it with LO Calc 4.2.7.2 on Linux Mint 17 (same build ID as yours) - works perfectly, too... BTW - forget to mention - **don't** insert the curly braces manually - Calc will add them automatically when entering as array formula (CTRL+SHIFT+ENTER). – tohuwawohu Apr 27 '15 at 13:31
  • @tohuwawohu Why not to convert your comments into an answer? – XavierStuvw Dec 13 '19 at 14:55

0 Answers0