How to compute standard deviation from tallies

[JenniferMurphy]

Are the formulas in H6 & H7 the best way to calculate the mean and std dev of a possibly large collection of numbers identified by their tallies?

In this example, I have a list of 217 numbers, 100 5s, 75 4s, 30 3s, 2 2s, & 10 1s.

Weighted Ratings.xlsx
	D	E	F	G	H
6	X	N		Mean	4.17
7	5	100		Std Dev	1.01
8	4	75
9	3	30
10	2	2
11	1	10
12	Total	217
Sheet1

Cell Formulas
Range	Formula
H6	H6	=(D7*E7+D8*E8+D9*E9+D10*E10+D11*E11)/Tbl[[#Totals],[N]]
H7	H7	=LET(m,H6, SQRT( ( ((D7-m)^2*E7) + ((D8-m)^2*E8) + ((D9-m)^2*E9) + ((D10-m)^2*E10) + ((D11-m)^2*E11) ) / Tbl[[#Totals],[N]] ) )
E12	E12	=SUBTOTAL(109,[N])

Thanks

 

[Eric W]

Maybe:

Book2
	D	E	F	G	H
6	X	N		Mean	4.1658986
7	5	100		Std Dev	1.0115181
8	4	75
9	3	30
10	2	2
11	1	10
12	Total	217
Sheet3

Cell Formulas
Range	Formula
H6	H6	=SUM(Tbl[X]*Tbl[N])/SUM(Tbl[N])
H7	H7	=SQRT(SUM((Tbl[X]-H6)^2*Tbl[N])/(SUM(Tbl[N])))
E12	E12	=SUBTOTAL(109,[N])

 

[JenniferMurphy]

Maybe:

That works for a table organized that way. I guess I misled you with the table example I posted above. Sorry about that.

Unfortunately, I need to organize mine as shown below. Is there a simpler formula for the mean and std dev in this table? Thanks

Std Dev & Variance.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	SKU	Rating	#Reviews	5%	4%	3%	2%	1%	%Sum	#5	#4	#3	#2	#1	Mean	Std Dev
4	B0822KNQKT	4.7	67,027	83%	10%	3%	1%	2%	99%	55,632	6,703	2,011	670	1,341	4.6800	0.7474
5	B004HZFASG	4.8	27,183	85%	9%	3%	1%	2%	100%	23,106	2,446	815	272	544	4.7400	0.7432
6	B093VNWP94	4.6	8,157	79%	13%	5%	2%	2%	101%	6,444	1,060	408	163	163	4.6800	0.8346
7	B0928PHM2B	4.1	1,924	78%	13%	5%	2%	2%	100%	1,501	250	96	38	38	4.6300	0.8325
8	B08RWMY1MW	4.4	1,447	72%	12%	7%	3%	6%	100%	1,042	174	101	43	87	4.4100	1.1322
9	B081T9X7RZ	4.8	371	84%	11%	2%	1%	1%	99%	312	41	7	4	4	4.7300	0.6272
10	B00FHW7PHW	3.7	149	53%	13%	3%	12%	19%	100%	79	19	4	18	28	3.6900	1.6291
11	B07FMC8N7H	4.5	91	76%	13%	4%	3%	4%	100%	69	12	4	3	4	4.5400	0.9942
12	B002VL628U	3.8	7	51%	26%	0%	0%	23%	100%	4	2	0	0	2	3.8200	1.5961
Amazon 01

Cell Formulas
Range	Formula
K4:K12	K4	=[@[5%]]+[@[4%]]+[@[3%]]+[@[2%]]+[@[1%]]
L4:L12	L4	=[@['#Reviews]]*[@[5%]]
M4:M12	M4	=[@['#Reviews]]*[@[4%]]
N4:N12	N4	=[@['#Reviews]]*[@[3%]]
O4:O12	O4	=[@['#Reviews]]*[@[2%]]
P4:P12	P4	=[@['#Reviews]]*[@[1%]]
Q4:Q12	Q4	=([@['#5]]*5+[@['#4]]*4+[@['#3]]*3+[@['#2]]*2+[@['#1]]*1)/[@['#Reviews]]
R4:R12	R4	=SQRT( ((5-[@Mean])^2*[@['#5]] + (4-[@Mean])^2*[@['#4]] + (3-[@Mean])^2*[@['#3]] + (2-[@Mean])^2*[@['#2]] + (1-[@Mean])^2*[@['#1]]) / [@['#Reviews]] )

 

[Eric W]

This is about as concise as I can get it:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	SKU	Rating	#Reviews	5%	4%	3%	2%	1%	%Sum	#5	#4	#3	#2	#1	Mean	Std Dev
4	B0822KNQKT	4.7	67027	0.83	0.1	0.03	0.01	0.02	0.99	55632.41	6702.7	2010.81	670.27	1340.54	4.68	0.747379
5	B004HZFASG	4.8	27183	0.85	0.09	0.03	0.01	0.02	1	23105.55	2446.47	815.49	271.83	543.66	4.74	0.743236
6	B093VNWP94	4.6	8157	0.79	0.13	0.05	0.02	0.02	1.01	6444.03	1060.41	407.85	163.14	163.14	4.68	0.83464
7	B0928PHM2B	4.1	1924	0.78	0.13	0.05	0.02	0.02	1	1500.72	250.12	96.2	38.48	38.48	4.63	0.832526
8	B08RWMY1MW	4.4	1447	0.72	0.12	0.07	0.03	0.06	1	1041.84	173.64	101.29	43.41	86.82	4.41	1.13221
9	B081T9X7RZ	4.8	371	0.84	0.11	0.02	0.01	0.01	0.99	311.64	40.81	7.42	3.71	3.71	4.73	0.627193
10	B00FHW7PHW	3.7	149	0.53	0.13	0.03	0.12	0.19	1	78.97	19.37	4.47	17.88	28.31	3.69	1.629079
11	B07FMC8N7H	4.5	91	0.76	0.13	0.04	0.03	0.04	1	69.16	11.83	3.64	2.73	3.64	4.54	0.994183
12	B002VL628U	3.8	7	0.51	0.26	0	0	0.23	1	3.57	1.82	0	0	1.61	3.82	1.59612
Sheet1

Cell Formulas
Range	Formula
K4:K12	K4	=SUM([@[5%]]:[@[1%]])
L4:L12	L4	=[@['#Reviews]]*[@[5%]]
M4:M12	M4	=[@['#Reviews]]*[@[4%]]
N4:N12	N4	=[@['#Reviews]]*[@[3%]]
O4:O12	O4	=[@['#Reviews]]*[@[2%]]
P4:P12	P4	=[@['#Reviews]]*[@[1%]]
Q4:Q12	Q4	=SUM([@['#5]]:[@['#1]]*{5,4,3,2,1})/[@['#Reviews]]
R4:R12	R4	=SQRT(SUM(({5,4,3,2,1}-[@Mean])^2*[@['#5]]:[@['#1]])/[@['#Reviews]])

You can also write the sum formula like:

=SUM(Table1[@[5%]:[1%]])

which is a little longer, but shows a slightly different way to select a range on the current row. It might be a bit more intuitive and/or easier to remember, but that's up to you.

 

[JenniferMurphy]

This is about as concise as I can get it:

Very nice. All of your suggestions are better than what I came up with.

Thank you.