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

a	12
a	18
a	21
a	51
a	45
b	85
b	412
b	4
b	758
b	54
b	1
b	2
c	7
c	5
c	44
d	22
d	14
d	52
d	14
d	52

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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]

Row\Col	A​	B​	C​	D​	E​	F​
2​	a	12​			a	17.41551​
3​	a	18​			b	290.7066​
4​	a	21​			c	21.96209​
5​	a	51​			d	19.62651​
6​	a	45​
7​	b	85​
8​	b	412​
9​	b	4​
10​	b	758​
11​	b	54​
12​	b	1​
13​	b	2​
14​	c	7​
15​	c	5​
16​	c	44​
17​	d	22​
18​	d	14​
19​	d	52​
20​	d	14​
21​	d	52​

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!