Standard deviation and if function

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 1, 2011

• #2

Veronika_T said:

Hello,
I have a big set of data with various characteristics. I need to calculate average and stdev of a subset belonging to particular group. I used "averageif" function - worked perfectly - and am looking for something like"stdevIF" - can someone help me, please?
Thanks in advance,
V.

Click to expand...

Control+shift+enter, not just enter:

=STDEV(IF(GroupRange=Group,ValueRange))

 

Upvote 0

V

Veronika_T

New Member

Joined

Mar 1, 2011

Messages

3

• Mar 1, 2011

• #3

Wow, it's magic! It worked!
Now, stupid follow-up question: what is this magic combination of "enter+ctrl+shift"?
THANKS!!!!
V.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 1, 2011

• #4

Veronika_T said:

Wow, it's magic! It worked!
Now, stupid follow-up question: what is this magic combination of "enter+ctrl+shift"?
THANKS!!!!
V.

Click to expand...

The combination signals Excel that the formula is intended to process arrays.
See Excel's Help under the "array" keyword.

 

Upvote 0

V

Veronika_T

New Member

Joined

Mar 1, 2011

Messages

3

• Mar 1, 2011

• #5

Thanks again!
V.

 

Upvote 0

B

briordan

New Member

Joined

Oct 18, 2013

Messages

17

• Oct 18, 2013

• #6

But will this work for 2 conditions?

To extend the current example:

=STDEV(IF(GroupRange=Group,ValueRange) might be something like =STDEV(IF(AND(GroupRange1=Group1,GroupRange2=Group2, ValueRange)

All my efforts so far have failed.

Thanks, briordan

 

Upvote 0

C

Canapone

Active Member

Joined

May 10, 2007

Messages

463

• Oct 18, 2013

• #7

Hi,

maybe

=STDEV(IF(GroupRange1=Group1,IF(GroupRange2=Group2,ValueRange)))

to be confirmed with control+shift+enter

Hope it helps

 

Upvote 0

B

briordan

New Member

Joined

Oct 18, 2013

Messages

17

• Oct 18, 2013

• #8

Ciao Canapone, funziona bene, grazie mille!

It works, thank you very much.

Ciao,
briordan

 

Upvote 0

C

Canapone

Active Member

Joined

May 10, 2007

Messages

463

• Oct 18, 2013

• #9

Ciao,

thanks for your kind feedback.

Alla prossima

 

Upvote 0