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.