STDEVA Match for large sheet.

[jespo1351]

Hello,

EXCEL 2013
is there a way to do a STDEVA with a Match function without an array? I'm looking for a formula in column C. I have a large set of data (9,000 rows, 1200+ different instances of column a) with an unknown amount of instances in column a. I want to match column A to column be to create a Standard Deviation. So if I copied all the lines down, each line for "a" will show a standard deviation of 17.41551, "b" will show 290.7066, etc...

Or if anyone knows a simpler solution to show the standard deviation for all my instances, I'm all ears.

Thank you so much
Column A Column B
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]12[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]412[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]758[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]52[/TD]
[/TR]
</tbody>[/TABLE]

 

[jespo1351]

Just a thought...Would it be easier to do a separate tab and have it find all the instances first? Then try to match and do standard deviation?

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

2​

[/td][td]a[/td][td]

12​

[/td][td][/td][td][/td][td]a[/td][td]

17.41551​

[/td][/tr]
[tr][td]

3​

[/td][td]a[/td][td]

18​

[/td][td][/td][td][/td][td]b[/td][td]

290.7066​

[/td][/tr]
[tr][td]

4​

[/td][td]a[/td][td]

21​

[/td][td][/td][td][/td][td]c[/td][td]

21.96209​

[/td][/tr]
[tr][td]

5​

[/td][td]a[/td][td]

51​

[/td][td][/td][td][/td][td]d[/td][td]

19.62651​

[/td][/tr]
[tr][td]

6​

[/td][td]a[/td][td]

45​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]b[/td][td]

85​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]b[/td][td]

412​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]b[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]b[/td][td]

758​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]b[/td][td]

54​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]b[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]b[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]c[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]c[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]c[/td][td]

44​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]d[/td][td]

22​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]d[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]d[/td][td]

52​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]d[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]d[/td][td]

52​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 control+shift+enter, not just enter, and copy down:

=STDEVA(IF($A$2:$A$21=$E2,$B$2:$B$21))

STDEVA admits text and logical values in B2:B21, although your sample does not house such values.

 

[jespo1351]

Thank you Aladin. Is there a way to do it without an array?

 

[Aladin Akyurek]

Thank you Aladin. Is there a way to do it without an array?

I don't think there is. Is that a problem?

 

[jespo1351]

Doing an array formula like this for 9000 lines seems to take a long time to calculate. If it's the only resolution then I'll deal. Thanks a bunch!

 

[Aladin Akyurek]

Doing an array formula like this for 9000 lines seems to take a long time to calculate. If it's the only resolution then I'll deal. Thanks a bunch!

Are you trying to run the formula in column C
instead of in a separate area as I have done?

 

[jespo1351]

Pretty much. I don't have a set # of values for column a and I don't know how to populate the list separately to create 1 line for each change. I could have 1200 different items in column A and the list can change every time I run it.

Is there a way to pull each item from column A into a separate area where there is only one line for each? I have done a sort in column A followed by a count with subtotal at the end. Then substituted " total" with "" on the grouped sum line. Copied the list them moved it. Did that even make sense? haha

 

[Aladin Akyurek]

You can run Advanced Filter (with Unique records only checked) on the range column A from the location
where you want the list of unique items. Then run the suggested formula.

 

[jespo1351]

Yes that isn't too bad. That brings my list to less than 1,000 different instances. It still temporarily freezes my computer when calculating with 8 processors but its not as bad. Thanks again Aladin!