Stdev

[EJohnson99202]

Hello, I am trying to pull a Standard Deviation based on a specific group and can not seem to get the formula quite right.

I have a list of account numbers in column A, count of days between bills in column B and wanting to put the standard deviation in column C.

[TABLE="width: 233"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Acct #[/TD]
[TD]Days/Bills[/TD]
[TD]StdDev[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]28[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]35[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]14[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]11[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

How can I get the Standard Deviation to only account for the rows where the account numbers match, so my results are "4" for Rows 1 - 3 and "2" for Rows 4 - 6 and not show "11" for the whole column B?

PS: I'm working on a sheet with over 24K rows and over 2K different account numbers.

Thank you in advance!

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
I used STDEV for a sample, you may need to change to population.

Excel Workbook
	A	B	C
1	Acct #	Days/Bills	StdDev
2	123	31	3.512
3	123	28
4	123	35
5	456	10	2.082
6	456	14
7	456	11
Sheet

 

[EJohnson99202]

Spreadsheet Formulas

Cell Formula C2 {=IF(COUNTIF($A$2:$A2,$A2)>1,"",STDEV.S(IF($A$2:$A$7=$A2,$B$2:$B$7)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

That worked perfect! Thank you so much!!

 

[shg]

Pivot Table:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Acct #​

[/td][td="bgcolor:#F3F3F3"]

Days/Bills​

[/td][td="bgcolor:#F3F3F3"]

StdDev​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#DCE6F1"]Row Labels[/td][td="bgcolor:#DCE6F1"]StdDev of Days/Bills[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

123​

[/td][td]

31​

[/td][td][/td][td][/td][td]123[/td][td]

3.512​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

123​

[/td][td]

28​

[/td][td][/td][td][/td][td]456[/td][td]

2.082​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

123​

[/td][td]

35​

[/td][td][/td][td][/td][td="bgcolor:#DCE6F1"]Grand Total[/td][td="bgcolor:#DCE6F1"]

11.077​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

456​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

456​

[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

456​

[/td][td]

11​

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

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.
SHG use of a pivot table will also get what you want.