Calculating Standard Deviations of Percentile Groups

[NMTaxGuy]

I am trying to calculate the standard deviation of rows of data that are in percentiles. I have not made any special subgroups. Rather, I have used COUNTIF and AVERAGEIF by establishing the range and using a cell value as the match criteria.

This phrase worked once: IF(E6:E20022=G7, STDEV.S(D6:D20022), but I get false thereafter, even if the cell reference is updated (i.e. G8, then g9, etc.)

How do I make this work. My data sets can be hundreds of thousands of rows. I want to subdivide them into equal groups (percentiles) and calculate the count of the group, the group mean, and the group standard deviation. Got the first 2; standard deviation is a pain.

 

[ajamess]

I believe you can do this with array formulas, but in big sample sizes it would always crash my computer so I would do this if you can sort your Percentile Groups in E (so all of a group are next to each other) :

it seems like in Column E you already have your Percentile Group:

=stdev.s(offset($d$1,match(g7,e:e,0)-1,0,countif(e:e,g7),1))


Having to sort might mess up your G7, G8 etc groups. So I would probably put this summarization on another "dashboard" or "summary" type tab. Averageifs/countifs should work normally as before.

 

[ajamess]

If you would like to try array formulas (no need to sort) see:

https://www.mrexcel.com/forum/excel-questions/502098-calculate-stdev-array-formula.html


it would be
=STDEV(IF(E$6:E$20022=G7, D$6:D$20022))

 

[NMTaxGuy]

Column D is the values for the std dev; column E identifies the percentile. Column G is what I am using to "match" the groups, ie. if the G cell is 100, then only use the values corresponding to a percentile of 100.

This is the first of 16 partitions I have to do; the largest will have 340K rows...this one only 20K. I will play with the above; let me know if this additional information helps.

Thanks!

 

[NMTaxGuy]

something wrong in my G or E column, because am getting Div/0 error.

 

[NMTaxGuy]

that worked for the first cell, but not for the subsequent rows.....keep getting a div/o error....but both reference columns E and G are numbers....no rounding issues that I can find....hmmmmm

 

[ajamess]

that worked for the first cell, but not for the subsequent rows.....keep getting a div/o error....but both reference columns E and G are numbers....no rounding issues that I can find....hmmmmm

Which method worked for the first cell but not subsequent rows?

 

[NMTaxGuy]

I adjusted the formula you provided and made it an array...and voila!! {=STDEV.S(IF($E$6:$E$20022=G6,$D$6:$D$20022))}

Once I did that, the div/0 error went away and the standard deviations were calculated.

TYVM for your help.

 

[ajamess]

ah I apologize, since I mentioned array formula, i didn't think to add that you need to ctrl+shift+enter. my fault!

But glad you got it working, good luck!