Ask Your Question
0

Equivalent of STDEVIF function?

asked 2019-07-17 19:53:48 +0200

brulingo gravatar image

updated 2019-07-17 19:54:44 +0200

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-07-18 00:27:13 +0200

Opaque gravatar image

updated 2019-07-18 01:42:26 +0200

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 (✔) next to the answer.

edit flag offensive delete link more

Comments

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.

brulingo gravatar imagebrulingo ( 2019-07-18 14:49:43 +0200 )edit

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.

Opaque gravatar imageOpaque ( 2019-07-18 15:29:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-17 19:53:48 +0200

Seen: 27 times

Last updated: Jul 18