Best way to convert Amazon ratings to meaningful Z Scores

[JenniferMurphy]

Most products on Amazon have a rating (0-5) and a count of the number of reviews. A product might get a rating of 4.7 based on 500 reviews or 4.6 based on 5000 reviews. Clearly a rating of 4.7 based on 5000 reviews is worth more that one with a rating of 4.8 based on juist 1 review. But what about a 4.8 rating based on 3000 reviews vs one with a 4.9 rating based on 2500 reviews? How to combine the rating and the number of reviews into a single z score that accurately ranks them?

I created this table as a test.

Cols D-F have the ratings, the z scores, and the ranks. Cols G-I have the number of reviews, those z scores, and those ranks.

Cols J-L have the product of the z scores (z1*z2), those z scores, and those ranks. This turns out to be a poor choice because the product of two negative numbers is a positive number. I couldn't see a way to correct for that.

Cols M-O have the sum of the z scores, those z scores, and those ranks. This looks more reasonable, but I am not confident that it is really valid.

I think the answer might be in a confidence interval. I see that Excel has two -- normal and student T. I am not sure how to use them appropriately. Any suggestions?

Weighted Ratings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Product	Rating	Z1	Rank1	#Reviews	Z2	Rank2	Z1*Z2	Z3	Rank3	Z1+Z2	Z4	Rank4
5	A	5.0	+1.43	1	25	-1.27	8	-1.82	-1.58	8	0.15	+0.12	3
6	B	4.9	+1.02	2	4,000	+0.64	3	0.66	+0.80	3	1.66	+1.32	2
7	C	4.8	+0.61	3	5,000	+1.13	1	0.69	+0.83	2	1.74	+1.38	1
8	D	4.7	+0.20	4	2,000	-0.32	5	-0.07	+0.11	5	-0.12	-0.09	5
9	E	4.6	-0.20	5	500	-1.05	7	0.21	+0.37	4	-1.25	-0.99	7
10	F	4.5	-0.61	6	4,000	+0.64	3	-0.39	-0.21	6	0.03	+0.03	4
11	G	4.4	-1.02	7	800	-0.90	6	0.92	+1.05	1	-1.92	-1.52	8
12	H	4.3	-1.43	8	5,000	+1.13	1	-1.61	-1.37	7	-0.30	-0.24	6
13	Mean	4.65	0.00		2,666	0.00		-0.18	0.00		0.00	0.00
14	Std Dev	0.245	1.00		2,072	1.00		1.04	1.00		1.26	1.00
Amazon

Cell Formulas
Range	Formula
E5:E12	E5	=([@Rating]-TblAmazon[[#Totals],[Rating]])/D$14
F5:F12	F5	=RANK.EQ([@Rating],[Rating])
H5:H12	H5	=([@['#Reviews]]-TblAmazon[[#Totals],['#Reviews]])/G$14
I5:I12	I5	=RANK.EQ([@['#Reviews]],['#Reviews])
J5:J12	J5	=[@Z1]*[@Z2]
K5:K12	K5	=([@[Z1*Z2]]-TblAmazon[[#Totals],[Z1*Z2]])/J$14
L5:L12	L5	=RANK.EQ([@[Z1*Z2]],[Z1*Z2])
M5:M12	M5	=[@Z1]+[@Z2]
N5:N12	N5	=([@[Z1+Z2]]-TblAmazon[[#Totals],[Z1+Z2]])/M$14
O5:O12	O5	=RANK.EQ([@Z4],[ Z4 ])
D13	D13	=SUBTOTAL(101,[Rating])
E13	E13	=SUBTOTAL(101,[Z1])
D14	D14	=STDEV.S(TblAmazon[Rating])
E14	E14	=STDEV.S(TblAmazon[Z1])
G13	G13	=SUBTOTAL(101,['#Reviews])
H13	H13	=SUBTOTAL(101,[Z2])
G14	G14	=STDEV.S(TblAmazon['#Reviews])
H14	H14	=STDEV.S(TblAmazon[Z2])
J13	J13	=SUBTOTAL(101,[Z1*Z2])
K13	K13	=SUBTOTAL(101,[Z3])
J14	J14	=STDEV.S(TblAmazon[Z1*Z2])
K14	K14	=STDEV.S(TblAmazon[ Z3 ])
M13	M13	=SUBTOTAL(101,[Z1+Z2])
N13	N13	=SUBTOTAL(101,[Z4])
M14	M14	=STDEV.S(TblAmazon[Z1+Z2])
N14	N14	=STDEV.S(TblAmazon[ Z4 ])

 

[lrobbo314]

I came across this site which looked promising, but the formula did not work no matter how much I messed with it.

I have no idea if this is even a good way of doing this, but the results seem to pan out.

JM
	A	B	C	D	E	F	G
1	Product	Rating	Votes	X	Y	Adjusted Score	Rank
2	A	5	25	1	0.005	5.025	6
3	B	4.9	4000	0.98	0.8	8.722	2
4	C	4.8	5000	0.96	1	9.408	1
5	D	4.7	2000	0.94	0.4	6.298	5
6	E	4.6	500	0.92	0.1	4.692	7
7	F	4.5	4000	0.9	0.8	7.65	4
8	G	4.4	800	0.88	0.16	4.576	8
9	H	4.3	5000	0.86	1	7.998	3
Sheet3

Cell Formulas
Range	Formula
D2:D9	D2	=B2/MAX($B$2:$B$9)
E2:E9	E2	=C2/MAX($C$2:$C$9)
F2:F9	F2	=SUM(D2:E2)*B2
G2:G9	G2	=RANK.EQ(F2,$F$2:$F$9)

 

[lrobbo314]

This version gives the makes the vote count seem to affect the score more.

JM
	A	B	C	D	E	F	G
1	Product	Rating	Votes	X	Y	Adjusted Score	Rank
2	A	5	25	1	0.005	0.025	8
3	B	4.9	4000	0.98	0.8	3.8416	2
4	C	4.8	5000	0.96	1	4.608	1
5	D	4.7	2000	0.94	0.4	1.7672	5
6	E	4.6	500	0.92	0.1	0.4232	7
7	F	4.5	4000	0.9	0.8	3.24	4
8	G	4.4	800	0.88	0.16	0.61952	6
9	H	4.3	5000	0.86	1	3.698	3
Sheet3

Cell Formulas
Range	Formula
D2:D9	D2	=B2/MAX($B$2:$B$9)
E2:E9	E2	=C2/MAX($C$2:$C$9)
F2:F9	F2	=PRODUCT(B2,D2,E2)
G2:G9	G2	=RANK.EQ(F2,$F$2:$F$9)

 

[JenniferMurphy]

Here is my implementation of this approach. Both methods do seem to adjust the ratings in the right direction. But I don't see how you got those formulas from that website. Their formulas seem a lot more complicated.

Weighted Ratings.xlsx
	R	S	T	U	V	W	X	Y
4	Rating	#Reviews	R Adj	# Adj	Rtg1	Rank1	Rtg2	Rank2
5	4.9	978	1.0000	0.0279	5.04	5	0.137	5
6	4.8	35,059	0.9796	1.0000	9.50	1	4.702	1
7	4.7	16,546	0.9592	0.4719	6.73	2	2.128	3
8	4.7	220	0.9592	0.0063	4.54	6	0.028	7
9	4.7	113	0.9592	0.0032	4.52	7	0.015	8
10	4.7	46	0.9592	0.0013	4.51	8	0.006	9
11	4.6	17,354	0.9388	0.4950	6.60	3	2.138	2
12	4.6	8,133	0.9388	0.2320	5.39	4	1.002	4
13	4.6	663	0.9388	0.0189	4.41	9	0.082	6
Amazon

Cell Formulas
Range	Formula
T5:T13	T5	=[@Rating]/MAX([Rating])
U5:U13	U5	=[@['#Reviews]]/MAX(['#Reviews])
V5:V13	V5	=[@Rating]*([@[R Adj]]+[@['# Adj]])
W5:W13	W5	=RANK.EQ([@Rtg1],[Rtg1])
X5:X13	X5	=[@Rating]*[@[R Adj]]*[@['# Adj]]
Y5:Y13	Y5	=RANK.EQ([@Rtg2],[Rtg2])

I'll study that website when I get a minute.

If I had the standard deviation of the ratings for each product, I think I could use a confidence interval to adjust the ratings. I can get the number of reviews in each rating (1-5). Is there a formula I can put in AL5 that will calculate the standard deviation of a set of 861 5s, 68 4s, 39 3s, 10 2s, & 10 1s?

Weighted Ratings.xlsx
	S	T	U							AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
4	Product	Rating	#Reviews							%5s	%4s	%3s	%2s	%1s	#5s	#4s	#3s	#2s	#1s	Std Dev
5	A	4.9	978							88%	7%	4%	1%	1%	861	68	39	10	10
6	B	4.8	35,059							84%	10%	4%	1%	1%	29,450	3,506	1,402	351	351
7	C	4.7	16,546							82%	12%	4%	1%	1%	13,568	1,986	662	165	165
8	D	4.7	220							82%	9%	4%	2%	2%	180	20	9	4	4
9	E	4.7	113							78%	14%	6%	0%	2%	88	16	7	-	2
10	F	4.7	46							81%	10%	9%	0%	0%	37	5	4	-	-
11	G	4.6	17,354							76%	13%	6%	2%	3%	13,189	2,256	1,041	347	521
12	H	4.6	8,133							79%	13%	5%	2%	2%	6,425	1,057	407	163	163
13	I	4.6	663							79%	12%	6%	1%	3%	524	80	40	7	20
Amazon

Cell Formulas
Range	Formula
AG5:AG13	AG5	=[@['#Reviews]]*[@[%5s]]
AH5:AH13	AH5	=[@['#Reviews]]*[@[%4s]]
AI5:AI13	AI5	=[@['#Reviews]]*[@[%3s]]
AJ5:AJ13	AJ5	=[@['#Reviews]]*[@[%2s]]
AK5:AK13	AK5	=[@['#Reviews]]*[@[%1s]]

 

[lrobbo314]

I didn't get my formulas from the website. I came up with that on my own. The formulas from the website weren't helpful for me, but I thought it would be worth including in case you could get it to work.

It's been way too long since college and statistics, standard deviations, etc. have long since left my head. There are standard deviation formulas in Excel, but I don't know how to implement it.

 

[JenniferMurphy]

I didn't get my formulas from the website. I came up with that on my own.

What led you come up with those formulas? What is the basis of dividing my the maximum value? Is that some kind of normalization?

I noticed that both of your solutions have the Rating in there twice. Once as itself and again as a normalized version of itself. I wonder if this gives too much weight to the rating.

The formulas from the website weren't helpful for me, but I thought it would be worth including in case you could get it to work.

Ok, thanks

It's been way too long since college and statistics, standard deviations, etc. have long since left my head. There are standard deviation formulas in Excel, but I don't know how to implement it.

I have been told that when I was born, I knew nothing, and by now I have forgotten most of that.

 

[JenniferMurphy]

I am looking for a way to compare ratings like the ones below, by taking into account the number of reviews. The more reviews, the more confidence in the rating. I have more confidence in the first 5.0 rating below with 24,000 reviews than in the other two 5.0 ratings with 4,000 and 200 reviews. But how much more? More importantly, how do the second and third 5.0 ratings compare with the 4.9 rating with 24,000 reviews?

I have done some testing. I tested both of your methods plus a couple based on that idea. I found a way to use confidence intervals that I think is better than any of the others.

Weighted Ratings.xlsx
	B	C	D
4	B	C	D
5	Product	Rating	#Reviews
6	A	5.0	24,000
7	B	5.0	4,000
8	C	5.0	200
9	D	4.9	24,000
10	E	4.9	4,000
11	F	4.9	200
12	G	4.8	24,000
13	H	4.8	4,000
14	I	4.8	200
15	J	4.7	24,000
16	K	4.7	4,000
17	L	4.7	200
18	M	4.6	24,000
19	N	4.6	4,000
20	O	4.6	200
21	P	4.5	24,000
22	Q	4.5	4,000
23	R	4.5	200
24	S	4.4	24,000
25	T	4.4	200
26	U	4.4	4,000
27	V	3.7	100,000
28	Mean	4.7	13,518
29	Std Dev	0.3	21,957
Amazon-02

Cell Formulas
Range	Formula
B4:D4	B4	=col()
C28	C28	=SUBTOTAL(101,[Rating])
D28	D28	=SUBTOTAL(101,['#Reviews])
C29	C29	=STDEV.S(TblAmz02[Rating])
D29	D29	=STDEV.S(TblAmz02['#Reviews])

Here's that data with rankings based on several methods. I just hope it fits on the screen. Columns E & F are your "adjusted" values. I added a weighting factor to the number of reviews so it wouldn't dominate. In this run, I weighted the number of reviews at 0.5 (half weight). Columns G-J are your two methods and two of mine based on those. mine have the rating value in there only once. The rankings based on those values are in columns U-X.

Columns K-M make use of Z Scores. The rankings based on that are in column Y.

The most interesting data is in columns N-S. Column N calculates the confidence interval based on the confidence level (alpha) in N3 and the standard deviation in O3. I did some standard deviation calculations on actual Amazon ratings. Most of them were around 0.75 to 1.00. In column O, I calculate an adjusted rating by subtracting the confidence interval from the actual rating. This yields the minimum value that we have 95% confidence in. Column P shows how much the actual rating was adjusted downward. Column Q converts it to a Z Score, which shows how much the values differ. Column R scales the Z Score so the top score is 100. This makes is clearer how much each diffrers from its neighbors. Column S gives the new rankings. This table is sorted on column S.

The remaining columns have the rankings based on the other methods. I think it is pretty clear that column Q is by far the best. One of the key reasons for this is that a products ranking can only be reduced, whereas the other methods can increase it. The one that most clearly illustrated this is Product V with a rating of 3.7 on 100,000 reviews. Column
S is the only one that has it still down at #22. Several of the others have it at #1, which is grossly in error.

Weighted Ratings.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
2					F #A								Alpha	Std Dev
3					0.5								0.05	0.80
4	Product	Rating	#Reviews	Rtg Adj	#Rev Adj*F	R*(RA+#AF)	R*RA*#AF	RA*#AF	RA+ #AF	Z Rtg	Z #Rev	ZRtg+ Z#Rev	Conf	Rtg Min	Δ	Z RtgMin	Z 100	Rank RtgMin	Rank Rtg	Rank R*(RA+#AF)	Rank R*RA*#AF	Rank RA*#AF	Rank RA+#AF	Rank ZRtg+Z#Rev
5	A	5.0	24,000	1.000	0.120	5.60	0.60	0.12	1.12	+1.18	+0.48	+1.66	+0.01	+4.99	-0.010	+1.32	+100	1	1	1	2	2	2	1
6	B	5.0	4,000	1.000	0.020	5.10	0.10	0.02	1.02	+1.18	-0.43	+0.75	+0.02	+4.98	-0.025	+1.27	+96	2	1	4	9	9	7	4
7	D	4.9	24,000	0.980	0.120	5.39	0.58	0.12	1.10	+0.84	+0.48	+1.32	+0.01	+4.89	-0.010	+0.98	+74	3	4	2	3	3	3	2
8	C	5.0	200	1.000	0.001	5.01	0.01	0.00	1.00	+1.18	-0.61	+0.58	+0.11	+4.89	-0.111	+0.97	+74	4	1	5	16	16	9	7
9	E	4.9	4,000	0.980	0.020	4.90	0.10	0.02	1.00	+0.84	-0.43	+0.41	+0.02	+4.88	-0.025	+0.92	+70	5	4	7	10	10	10	8
10	G	4.8	24,000	0.960	0.120	5.18	0.55	0.12	1.08	+0.50	+0.48	+0.97	+0.01	+4.79	-0.010	+0.63	+48	6	7	3	4	4	4	3
11	F	4.9	200	0.980	0.001	4.81	0.00	0.00	0.98	+0.84	-0.61	+0.23	+0.11	+4.79	-0.111	+0.63	+47	7	4	8	17	17	12	10
12	H	4.8	4,000	0.960	0.020	4.70	0.09	0.02	0.98	+0.50	-0.43	+0.06	+0.02	+4.78	-0.025	+0.58	+44	8	7	10	11	11	13	11
13	J	4.7	24,000	0.940	0.120	4.98	0.53	0.11	1.06	+0.16	+0.48	+0.63	+0.01	+4.69	-0.010	+0.28	+21	9	10	6	5	5	5	6
14	I	4.8	200	0.960	0.001	4.61	0.00	0.00	0.96	+0.50	-0.61	-0.11	+0.11	+4.69	-0.111	+0.28	+21	10	7	11	18	18	14	13
15	K	4.7	4,000	0.940	0.020	4.51	0.09	0.02	0.96	+0.16	-0.43	-0.28	+0.02	+4.68	-0.025	+0.23	+18	11	10	14	12	12	15	14
16	M	4.6	24,000	0.920	0.120	4.78	0.51	0.11	1.04	-0.19	+0.48	+0.29	+0.01	+4.59	-0.010	-0.06	-5	12	13	9	6	6	6	9
17	L	4.7	200	0.940	0.001	4.42	0.00	0.00	0.94	+0.16	-0.61	-0.45	+0.11	+4.59	-0.111	-0.07	-5	13	10	15	19	19	16	16
18	N	4.6	4,000	0.920	0.020	4.32	0.08	0.02	0.94	-0.19	-0.43	-0.62	+0.02	+4.58	-0.025	-0.11	-9	14	13	17	13	13	17	17
19	P	4.5	24,000	0.900	0.120	4.59	0.49	0.11	1.02	-0.53	+0.48	-0.05	+0.01	+4.49	-0.010	-0.41	-31	15	16	12	7	7	7	12
20	O	4.6	200	0.920	0.001	4.24	0.00	0.00	0.92	-0.19	-0.61	-0.79	+0.11	+4.49	-0.111	-0.41	-31	16	13	18	20	20	18	18
21	Q	4.5	4,000	0.900	0.020	4.14	0.08	0.02	0.92	-0.53	-0.43	-0.96	+0.02	+4.48	-0.025	-0.46	-35	17	16	19	14	14	19	19
22	S	4.4	24,000	0.880	0.120	4.40	0.46	0.11	1.00	-0.87	+0.48	-0.39	+0.01	+4.39	-0.010	-0.75	-57	18	19	16	8	8	10	15
23	R	4.5	200	0.900	0.001	4.05	0.00	0.00	0.90	-0.53	-0.61	-1.14	+0.11	+4.39	-0.111	-0.76	-57	19	16	20	21	21	20	20
24	U	4.4	4,000	0.880	0.020	3.96	0.08	0.02	0.90	-0.87	-0.43	-1.30	+0.02	+4.38	-0.025	-0.81	-61	20	19	21	15	15	21	21
25	T	4.4	200	0.880	0.001	3.88	0.00	0.00	0.88	-0.87	-0.61	-1.48	+0.11	+4.29	-0.111	-1.10	-83	21	19	22	22	22	22	22
26	V	3.7	100,000	0.740	0.500	4.59	1.37	0.37	1.24	-3.27	+3.94	+0.67	+0.00	+3.70	-0.005	-3.16	-239	22	22	13	1	1	1	5
27	Mean	4.7	13,518	0.931	0.068	4.64	0.26	0.06	1.00	0.00	0.00	-0.00		+4.61		-0.00	-0.00
28	Std Dev	0.3	21,957	0.058	0.110	0.46	0.34	0.09	0.09	1.00	1.00	0.85		0.29		1.00	75.68
Amazon-02

Cell Formulas
Range	Formula
E5:E26	E5	=[@Rating]/MAX([Rating])
F5:F26	F5	=([@['#Reviews]]/MAX(['#Reviews])) * FactorNumAdj
G5:G26	G5	=[@Rating]*([@[Rtg Adj]]+[@['#Rev Adj*F]])
H5:H26	H5	=[@Rating]*[@[Rtg Adj]]*[@['#Rev Adj*F]]
I5:I26	I5	=[@[Rtg Adj]]*[@['#Rev Adj*F]]
J5:J26	J5	=[@[Rtg Adj]]+[@['#Rev Adj*F]]
K5:K26	K5	=([@Rating]-TblAmz02[[#Totals],[Rating]])/StdDev_Rtg
L5:L26	L5	=([@['#Reviews]]-TblAmz02[[#Totals],['#Reviews]])/StdDev_NumRev
M5:M26	M5	=[@[Z Rtg]]+[@[Z '#Rev]]
N5:N26	N5	=CONFIDENCE.NORM(Alpha,StdDev,[@['#Reviews]])
P5:P26	P5	=[@[Rtg Min]]-[@Rating]
Q5:Q26	Q5	=([@[Rtg Min]]-TblAmz02[[#Totals],[Rtg Min]])/StdDev_RtgMin
R5:R26	R5	=[@[Z RtgMin]]/MAX([Z RtgMin])*100
S5:S26	S5	=RANK.EQ([@[Rtg Min]],[Rtg Min])
T5:T26	T5	=RANK.EQ([@Rating],[Rating])
U5:U26	U5	=RANK.EQ([@[R*(RA+'#AF)]],[R*(RA+'#AF)])
V5:V26	V5	=RANK.EQ([@[R*RA*'#AF]],[R*RA*'#AF])
W5:W26	W5	=RANK.EQ([@[RA*'#AF]],[RA*'#AF])
X5:X26	X5	=RANK.EQ([@[RA+ '#AF]],[RA+ '#AF])
Y5:Y26	Y5	=RANK.EQ([@[ZRtg+ Z'#Rev]],[ZRtg+ Z'#Rev])
C27	C27	=SUBTOTAL(101,[Rating])
D27	D27	=SUBTOTAL(101,['#Reviews])
E27	E27	=SUBTOTAL(101,[Rtg Adj])
F27	F27	=SUBTOTAL(101,['#Rev Adj*F])
G27	G27	=SUBTOTAL(101,[R*(RA+'#AF)])
H27	H27	=SUBTOTAL(101,[R*RA*'#AF])
I27	I27	=SUBTOTAL(101,[RA*'#AF])
J27	J27	=SUBTOTAL(101,[RA+ '#AF])
K27	K27	=SUBTOTAL(101,[Z Rtg])
L27	L27	=SUBTOTAL(101,[Z '#Rev])
M27	M27	=SUBTOTAL(101,[ZRtg+ Z'#Rev])
C28	C28	=STDEV.S(TblAmz02[Rating])
D28	D28	=STDEV.S(TblAmz02['#Reviews])
E28	E28	=STDEV.S(TblAmz02[Rtg Adj])
F28	F28	=STDEV.S(TblAmz02['#Rev Adj*F])
G28	G28	=STDEV.S(TblAmz02[R*(RA+'#AF)])
H28	H28	=STDEV.S(TblAmz02[R*RA*'#AF])
I28	I28	=STDEV.S(TblAmz02[RA*'#AF])
J28	J28	=STDEV.S(TblAmz02[RA+ '#AF])
K28	K28	=STDEV.S(TblAmz02[Z Rtg])
L28	L28	=STDEV.S(TblAmz02[Z '#Rev])
M28	M28	=STDEV.S(TblAmz02[ZRtg+ Z'#Rev])
Q27	Q27	=SUBTOTAL(101,[Z RtgMin])
R27	R27	=SUBTOTAL(101,[Z 100])
Q28	Q28	=STDEV.S(TblAmz02[Z RtgMin])
R28	R28	=STDEV.S(TblAmz02[Z 100])
O5:O26	O5	=[@Rating]-[@Conf]
O27	O27	=SUBTOTAL(101,[Rtg Min])
O28	O28	=STDEV.S(TblAmz02[Rtg Min])

Named Ranges
Name	Refers To	Cells
'Amazon-02'!Alpha	='Amazon-02'!$N$3	N5:N26
'Amazon-02'!FactorNumAdj	='Amazon-02'!$F$3	F5:F26
'Amazon-02'!StdDev	='Amazon-02'!$O$3	N5:N26
'Amazon-02'!StdDev_NumRev	='Amazon-02'!$D$28	L5:L26
'Amazon-02'!StdDev_Rtg	='Amazon-02'!$C$28	K5:K26
'Amazon-02'!StdDev_RtgMin	='Amazon-02'!$O$28	Q5:Q26

Let me know if you have any comments of suggestions.

 

[JenniferMurphy]

I think I have some code that will effectively adjust (discount) Amazon style ratings based on the number of reviews. The AmazonRtg code calcualtes the lower end of the 95% confidence interval.

This minisheet has 28 multivitamin products on Amazon. I tried to select products with different ratings and different numbers of reviews. I then computed the z scores and finally adjusted those so that the top z score is 100. I think that makes it easier to judge the magnitude of the difference between products. Notice that several products with 5.0 and 4.9 ratings, but few reviews, are fairlt far down in the adjusted ratings.

New Microsoft Excel Worksheet.xlsx
	C	D	E	F	G	H	I	J
4	SKU	Price	Quantity	$$/each	Amazon Rating	Discounted Rating	Z Score	Z Max =100
5	B00L3XNK32	$26.49	200	$0.13	4.8/35557	4.79	0.95333	100
6	B0028AD6WO	$27.99	200	$0.14	4.8/22827	4.79	0.93796	98
7	B002H0KZ9M	$11.99	150	$0.08	4.8/21186	4.78	0.93503	98
8	B08Y1NZ7VR	$10.22	125	$0.08	4.8/10435	4.78	0.90094	95
9	B08Y1KFYF8	$10.76	100	$0.11	4.8/9073	4.78	0.89266	94
10	B09KCCVL9V	$18.69	200	$0.09	4.8/7281	4.77	0.87841	92
11	B00HLK0KAW	$29.99	300	$0.10	4.8/6892	4.77	0.87460	92
12	B002GU79G0	$24.12	150	$0.16	4.8/4379	4.77	0.83880	88
13	B00BKNSV2A	$72.50	120	$0.60	4.8/1744	4.75	0.73565	77
14	B071ZJHLJR	$17.00	150	$0.11	4.8/940	4.73	0.63440	67
15	B007L0DPE0	$11.30	150	$0.08	4.7/70796	4.69	0.44132	46
16	B00K5NEPHG	$53.62	120	$0.45	4.7/6445	4.67	0.33976	36
17	B010FAUO8C	$44.49	200	$0.22	4.8/293	4.67	0.33310	35
18	B0771XZF8F	$25.65	250	$0.10	4.7/4946	4.67	0.31918	33
19	B01LZN2QD1	$33.25	200	$0.17	4.9/86	4.66	0.28617	30
20	B07BWCTBBQ	$20.49	150	$0.14	4.7/2452	4.66	0.24940	26
21	B00CDX2QWW	$15.39	90	$0.17	4.9/62	4.62	0.06236	7
22	B007KL6SP8	$39.60	60	$0.66	4.7/716	4.62	0.04883	5
23	B01A8ZCLOE	$38.99	60	$0.65	4.9/55	4.60	-0.02918	-3
24	B0007YCC7S	$24.98	150	$0.17	4.6/18870	4.58	-0.12983	-14
25	B09VH42YTL	$40.00	60	$0.67	5.0/22	4.53	-0.41413	-43
26	B0B11R9NWQ	$42.30	180	$0.24	4.8/57	4.51	-0.53135	-56
27	B00ESWBE1U	$37.62	60	$0.63	4.6/499	4.50	-0.56755	-60
28	B082P9FRLS	$269.85	540	$0.50	4.5/487	4.40	-1.10399	-116
29	B0BW2VX2M3	$19.20	60	$0.32	4.4/22319	4.39	-1.18307	-124
30	B000OQA3SE	$47.00	120	$0.39	4.5/103	4.28	-1.72540	-181
31	B07KQWXRGM	$80.96	180	$0.45	4.2/12202	4.18	-2.27069	-238
32	B0BX7JBZG7	$19.99	60	$0.33	4.5/30	4.10	-2.70671	-284
33	Total				Mean	4.61	0.00000	0
34					Std Dev	0.19	1.00000	104.8959
35					Max	4.79	0.95333	100
Sheet1

Cell Formulas
Range	Formula
H5:H32	H5	=AmazonRtg([@[Amazon Rating]])
I5:I32	I5	=ZScore([@[Discounted Rating]],TblVitamins[[#Totals],[Discounted Rating]],AdjRtgStdDev,"HiLo")
J5:J32	J5	=[@[Z Score]]/ZScoreMax*100
H33	H33	=SUBTOTAL(101,[Discounted Rating])
I33	I33	=SUBTOTAL(101,[Z Score])
J33	J33	=SUBTOTAL(101,[Z Max =100])
H34	H34	=STDEV.S(TblVitamins[Discounted Rating])
I34	I34	=STDEV.S(TblVitamins[Z Score])
J34	J34	=STDEV.S(TblVitamins[Z Max =100])
H35	H35	=MAX(TblVitamins[Discounted Rating])
I35	I35	=MAX(TblVitamins[Z Score])
J35	J35	=MAX(TblVitamins[Z Max =100])
F5:F32	F5	=[@Price]/[@Quantity]

Named Ranges
Name	Refers To	Cells
AdjRtgMean	=TblVitamins[[#Totals],[Discounted Rating]]	I5:I32
AdjRtgStdDev	=Sheet1!$H$34	I5:I32
ZScoreMax	=Sheet1!$I$35	J5:J32

Here's the code that adjusts the ratings. I have not added error checking.

'================================================================================================
'             Amazon Rating Adjustment Function

' Adjust (devalue) the rating based on the number of reviews.
' Return the low end of the confidence interval range.

'   pRtgRevs = r.r/nnn
'       r.r = Amazon rating
'       nnn = Number of reviews

' For the power function relating confidence interval to number of reviews
'   confidence interval = AmzCoef * NumReviews ^ AmzExp
' The adjusted rating = rating - confidence interval

'   To Do
' Add error checking

'   Change Log
' 05/19/23  Created
'================================================================================================
Function AmazonRtg(pRtgRevs As String) As Double

Const AmzCoef As Double = 2.20296467  'The coefficient of the power function
Const AmzExp As Double = -0.5         'The exponent of the power function
Dim arrParms As Variant               'The result of the split of rtg/#revs

arrParms = Split(pRtgRevs, "/")       'Split the rating and the #reviews
'if ubound
AmazonRtg = arrParms(0) - AmzCoef * arrParms(1) ^ AmzExp

End Function

And here the Z Score code:

'================================================================================================
'                   Calculate the Z Score

'   pVal     The value to compute the Z Score for
'   pMean    The mean of the sample
'   pStdDev  The standard deviation of the sample
'   pOrder   "HiLo" = normal order (high values as better), such as for ratings
'            "LoHi" = inverted order (low values are better), such as for prices

'    Change Log
' 09/20/22  Created for my weighted rating system
' 10/09/22  Handle data errors
'================================================================================================
Function ZScore(ByVal pValue As Variant, ByVal pMean As Double _
              , ByVal pStdDev As Double, ByVal pOrder As String) As Double
Application.Volatile

'Check that pOrder is valid
If Not (UCase(pOrder) = "HILO" Or UCase(pOrder) = "LOHI") Then
  ZScore = CVErr(xlErrValue): Exit Function: End If

' If the value is not a number, return zero,
' which is the same as setting it to the average of the other values.
If Not WorksheetFunction.IsNumber(pValue) Then  'If not a number,
  ZScore = 0: Exit Function: End If               'Return zero
' If std dev is zero, return 0; else calculate Z Score
If pStdDev = 0 Then                   'If std dev = 0,
  ZScore = 0                            'Return 0
Else                                  'Else
  ZScore = (pValue - pMean) / pStdDev   'Calculate the ZScore
  If UCase(pOrder) = "LOHI" Then      'If range order is reversed,
    ZScore = -ZScore: End If            'Reverse the ZScores
End If

End Function