I need to calculate the average and standard deviation of certain rows in G16:G4675, but only those rows corresponding to rows in I16:I4675 that do not contain the text “-” (a single hyphen, the output of a formula).
I finally figured out how to get the average: =AVERAGEIF(I16:I4675,"<>-",G16:G4675). But there does not appear to be a STDEVIF function.
Can anyone suggest a workaround?
Hello,
you can perform via array function:
AVERAGEIF: {=AVERAGE(IF(I16:I4675<>"-",G16:G4675))}
STDEVIF: {=STDEV(IF(I16:I4675<>"-",G16:G4675))}
To get the array version of the formula finish the edit of the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER.
See the following simple example (Value in B5 omitted due to - in A5; “yes” is just for visibility)
Note If I understand that right, most, if not all, of these xxxIF functions are built in for convenience and maybe performance reasons only.
Tested using LibreOffice:
Version: 6.2.5.2, Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159, CPU threads: 8; OS:
Linux 4.12; UI render: default; VCL: kde5; Locale: en-US (en_US.UTF-8); UI-Language: en-US,
Calc: threaded
If the answer is correct or helped you to fix your problem, please click the check mark (
) next to the answer.
Thank you! That’s just what I needed.
I’m not familiar with array functions, though, and in case anyone else looking at this is in the same boat: I had to enter the function without the curly brackets or initial equals sign, then press control-shift-enter.
Please consider to mark the answer as correct and regarding control-shift-enter - that’s already in my answer
To get the array version of the formula finish the edit of the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER.
Thank you. Works well on:
Version: 24.2.7.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
CPU threads: 8; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.4
Calc: threaded
Would still be nice to have a STDEVIF and related conditional STDEVs
LOL. More that 500 spreadsheet functions already there in Calc. Why not triple all of them with respective IF/IFS variants? And of course, there must be a magic DOWHATIIMAGINE function, which would just replace everything else.
A new function is only a good candidate, when it is reasonably difficult to implement it using built-in functions, or when its use is overwhelmingly common.