Equivalent of STDEVIF function?

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)

image description

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 (:heavy_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.