Standard deviation and if function

V

Veronika_T

New Member
Joined
Mar 1, 2011
Messages
3
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
Wow, it's magic! It worked!
Now, stupid follow-up question: what is this magic combination of "enter+ctrl+shift"?
THANKS!!!!
V.
 
Upvote 0
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
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

Hi,

maybe

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

to be confirmed with control+shift+enter

Hope it helps
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Standard deviation of a subset in VBA
Replies
7
Views
221
SHW2022
S
J
  • Solved
Standard Deviation limited by multiple criteria
Replies
2
Views
909
JeffFinnan
J
J
  • Solved
Calculating the standard deviation of two weighted arrays.
Replies
5
Views
410
StephenCrump
S
P
  • Solved
Conditional correlation of returns
Replies
3
Views
428
DRSteele
DRSteele
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
342
ashleighbrown
A

Forum statistics

Threads
1,215,607
Messages
6,125,818
Members
449,262
Latest member
hideto94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top