Please critique this little UDF to average ratings

[JenniferMurphy]

I am in the process of researching password managers. I found at least 8 websites with reviews and "ratings". The ratings are all on different scales, so it's difficult to make an apples-to-apples comparison. So I wrote this little UDF. It takes each rating and rescales it to [0,1]. The resulting ratings can then be compared or averaged. I would appreciate any feedback plus answers to a few questions at the end.

Here's the code:

Public Function ScaleEm(pValue As Variant, _
                        pMin As Double, pMax As Double, _
                        Optional pMinMax As Boolean = True _
                        ) As Variant
Dim TrueMin As Double
Dim TrueMax As Double

'Check for missing ratings and return null
If IsEmpty(pValue) Or Not IsNumeric(pValue) Then
  ScaleEm = ""
  Exit Function
End If

If pMinMax Then   'If min is min and max is max, preserve order
  TrueMin = pMin
  TrueMax = pMax
Else              'If they are reversed, reverse the order
  TrueMin = pMax
  TrueMax = pMin
End If

ScaleEm = (pValue - TrueMin) / (TrueMax - TrueMin)

End Function

The pMinMax switch tells the UDF if the Min and Max values are backwards as is the case for rankings. 1 = best, 2 = next best.

And here is some sample data. All of the data except for row 21 and columns M & W are from actual review websites. The table on the left is the raw (input) data. The table on the right is the results of calls to my UDF. In a future version, I'll pass the entire table on the left and have the UDF (now Sub) do all of the calculations and fill in column D.

Questions:

1. I wanted to make it return a Double value, but then I didn't know how to handle missing ratings. Not every product is rated by every reviewer. There is no way to return a null double,m so it ends up being a zero, which then screws of the averages. So I changed it to Variant and returned a null value for missing ratings.
2. Is my method of testing for missing ratings correct or is there a better way?
3. I'd be interested in any comments on the algorithm. One problem is highlighted by the Test column. The mythical Test reviewer only reviewed one product, Test. So I gave it a "1" rating on a 1-10 scale. That gave that product a perfect 1.00 overall rating because none of the other products had a rating. I thought about assigning unrated products an average rating or a rating just below the lowest one they did rate, but this seemed arbitrary and may well penalize unfairly products that the reviewer just didn't get to.

Any other comments or suggestions are welcome.

 

[ExcelGzh]

JenniferMurphy

The idea of using a Variant to deal with nulls seems fine to me.

As far as the algorithim goes, it seems to produce a meaningful result but personally I would have used a z-score formula (review score - average score) / standard deviation

And for the Test reviewer, you could put in additional smarts (meaning more helper tables) to exclude reviewers or products that have less than a minimum number of reviews.

 

[JenniferMurphy]

JenniferMurphy

The idea of using a Variant to deal with nulls seems fine to me.

As far as the algorithim goes, it seems to produce a meaningful result but personally I would have used a z-score formula (review score - average score) / standard deviation

And for the Test reviewer, you could put in additional smarts (meaning more helper tables) to exclude reviewers or products that have less than a minimum number of reviews.

Those are two great suggestions. I will look into both of them. Thanks.

 

[JenniferMurphy]

JenniferMurphy

The idea of using a Variant to deal with nulls seems fine to me.

As far as the algorithim goes, it seems to produce a meaningful result but personally I would have used a z-score formula (review score - average score) / standard deviation

And for the Test reviewer, you could put in additional smarts (meaning more helper tables) to exclude reviewers or products that have less than a minimum number of reviews.

Well, I got distracted a few times and just got back to this project.

Your Z score suggestion looks to be excellent. Thanks for that.

I created a sheet to compare Z scores vs "normalized" scores and "raw" rankings, two of the methods I was considering. Here's a mini-sheet. It shows 6 items (products) in rows 9-14 rated by 4 different sources in columns D-G. The first source uses a 5-star system, like Amazon. The second uses an inverted 1-9 system with 1 the highest and 9 the lowest. The third uses a 100-0 system. The last uses a 10 to -10 system. I wanted to test it against any kind of rating system.

The Z scores are in columns P-S. They are summed and ranked in H-I and averaged and ranked in J-K. I use a UDF to calculate the Z scores so it can handle inverted ratings. I'll include that code below.

The "normalized" ratings are in T-W and then summed and ranked in L-M. These also use a UDF for similar reasons. I'll include that code below, too.

The ranked ratings are in X-AA and then summed and ranked in N-O.

Weighted Ratings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
5	Max	5	1	100	10
6	Min	1	9	0	-10
7	Weights	1	1	1	1
8	Item	Rtg 1	Rtg 2	Rtg 3	Rtg 4	Z Sum	ZSum Rank	Z Avg	ZAvg Rank	Norm Sum	Norm Rank	Rank Avg	Rank Rank	Z 1	Z 2	Z 3	Z 4	Norm 1	Norm 2	Norm 3	Norm 4	Rank 1	Rank 2	Rank 3	Rank 4
9	A	4.7	3	93	8	+1.9344	1	+0.4836	1	3.51	1	2.0	1	+0.25	+0.28	+0.53	+0.88	0.93	0.75	0.93	0.90	2.0	2.0	2.0	2.0
10	C	4.6	1	88	4	+0.8201	2	+0.2050	2	3.48	2	3.4	3	-0.25	+1.93	-0.23	-0.63	0.90	1.00	0.88	0.70	4.0	1.0	4.0	4.5
11	E	4.6	4	88	10	+0.6000	3	+0.1500	3	3.41	3	3.4	3	-0.25	-0.55	-0.23	+1.63	0.90	0.63	0.88	1.00	4.0	4.5	4.0	1.0
12	B	5.0	4	88	4	+0.3685	4	+0.0921	4	3.21	5	3.5	5	+1.77	-0.55	-0.23	-0.63	1.00	0.63	0.88	0.70	1.0	4.5	4.0	4.5
13	D	4.6	4	100	4	+0.1541	5	+0.0385	5	3.23	4	3.5	5	-0.25	-0.55	+1.58	-0.63	0.90	0.63	1.00	0.70	4.0	4.5	1.0	4.5
14	F	4.4	4	80	4	-3.8772	6	-0.9693	6	2.98	6	5.3	6	-1.27	-0.55	-1.43	-0.63	0.85	0.63	0.80	0.70	6.0	4.5	6.0	4.5
15	Std Dev	0.20	1.21	6.63	2.66
16	Mean	4.65	3.33	89.50	5.67
Compare Methods

Cell Formulas
Range	Formula
H9:H14	H9	=SUMPRODUCT(TblCompare[@[Z 1]:[Z 4]],Weights)
I9:I14	I9	=RANK.EQ([@[Z Sum]],[Z Sum])
J9:J14	J9	=AVERAGE(TblCompare[@[Z 1]:[Z 4]])
K9:K14	K9	=RANK.EQ([@[Z Avg]],[Z Avg])
L9:L14	L9	=SUMPRODUCT(TblCompare[@[Norm 1]:[Norm 4]],Weights)
M9:M14	M9	=RANK.AVG([@[Norm Sum]],[Norm Sum])
N9:N14	N9	=AVERAGE(TblCompare[@[Rank 1]:[Rank 4]]*Weights)
O9:O14	O9	=RANK.AVG([@[Rank Avg]],[Rank Avg],1)
P9:P13	P9	=ZScore([@[Rtg 1]], Rtg1Mean, TblCompare[[#Totals],[Rtg 1]], Rtg1Min, Rtg1Max)
Q9:Q14	Q9	=ZScore([@[Rtg 2]], Rtg2Mean, TblCompare[[#Totals],[Rtg 2]], Rtg2Min, Rtg2Max)
R9:R14	R9	=ZScore([@[Rtg 3]], Rtg3Mean, TblCompare[[#Totals],[Rtg 3]], Rtg3Min, Rtg3Max)
S9:S14	S9	=ZScore([@[Rtg 4]], Rtg4Mean, TblCompare[[#Totals],[Rtg 4]], Rtg4Min, Rtg4Max)
T9:T14	T9	=Normalize([@[Rtg 1]],Rtg1Min, Rtg1Max)
U9:U14	U9	=Normalize([@[Rtg 2]],Rtg2Min, Rtg2Max)
V9:V14	V9	=Normalize([@[Rtg 3]],Rtg3Min, Rtg3Max)
W9:W14	W9	=Normalize([@[Rtg 4]],Rtg4Min, Rtg4Max)
X9:X14	X9	=RANK.AVG([@[Rtg 1]],[Rtg 1])
Y9:Y14	Y9	=RANK.AVG([@[Rtg 2]],[Rtg 2],1)
Z9:Z14	Z9	=RANK.AVG([@[Rtg 3]],[Rtg 3])
AA9:AA14	AA9	=RANK.AVG([@[Rtg 4]],[Rtg 4])
P14	P14	=ZScore([@[Rtg 1]], D$16, TblCompare[[#Totals],[Rtg 1]], D$6, D$5)
D15	D15	=SUBTOTAL(107,[Rtg 1])
E15	E15	=SUBTOTAL(107,[Rtg 2])
F15	F15	=SUBTOTAL(107,[Rtg 3])
G15	G15	=SUBTOTAL(107,[Rtg 4])
D16	D16	=AVERAGE(TblCompare[Rtg 1])
E16	E16	=AVERAGE(TblCompare[Rtg 2])
F16	F16	=AVERAGE(TblCompare[Rtg 3])
G16	G16	=AVERAGE(TblCompare[Rtg 4])

Named Ranges
Name	Refers To	Cells
'Compare Methods'!Rtg1Max	='Compare Methods'!$D$5	P9:P14, T9:T14
'Compare Methods'!Rtg1Mean	='Compare Methods'!$D$16	P9:P14
'Compare Methods'!Rtg1Min	='Compare Methods'!$D$6	P9:P14, T9:T14
'Compare Methods'!Rtg2Max	='Compare Methods'!$E$5	Q9:Q14, U9:U14
'Compare Methods'!Rtg2Mean	='Compare Methods'!$E$16	Q9:Q14
'Compare Methods'!Rtg2Min	='Compare Methods'!$E$6	Q9:Q14, U9:U14
'Compare Methods'!Rtg3Max	='Compare Methods'!$F$5	R9:R14, V9:V14
'Compare Methods'!Rtg3Mean	='Compare Methods'!$F$16	R9:R14
'Compare Methods'!Rtg3Min	='Compare Methods'!$F$6	R9:R14, V9:V14
'Compare Methods'!Rtg4Max	='Compare Methods'!$G$5	S9:S14, W9:W14
'Compare Methods'!Rtg4Mean	='Compare Methods'!$G$16	S9:S14
'Compare Methods'!Rtg4Min	='Compare Methods'!$G$6	S9:S14, W9:W14
'Compare Methods'!Weights	='Compare Methods'!$D$7:$G$7	H9:H14, L9:L14, N9:N14

Here's the Z score code:

Function ZScore(pValue As Double, pMean As Double, pStdDev As Double _
              , pMin As Double, pMax As Double) As Double

' 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 pMin > pMax Then ZScore = -ZScore  'If range order is reversed, revferse ZScore
End If

End Function

Here's the Normalize code:

                , Optional pWrstNew As Double = 0 _
                , Optional pBestNew As Double = 1 _
                ) As Double

Dim RangeOld As Double          'The range of the old scale
RangeOld = pBestOld - pWrstOld  'Calculate the old range

'Is it normal (Max > Min), inverted (Man < Min), or point (Max = Min)?
Select Case RangeOld
  Case Is > 0             '>0 = Normal range (Best > Worst)
    Select Case pXOld       'Where is pXOld on the old range?
      Case Is >= pBestOld     'If >= old best
        Normalize = pBestNew    'Make it the best
      Case Is <= pWrstOld     'If <= old Worst
        Normalize = pWrstNew    'Make it the Worst, scale it
      Case Else               'If it's within the old range
        Normalize = (pXOld - pWrstOld) / RangeOld  'Scale it
    End Select
  Case Is < 0             '<0 = Inverted range (Best < Worst)
    Select Case pXOld       'Where is pXOld on the old range?
      Case Is >= pWrstOld     'If >= old worst
        Normalize = pWrstNew    'Make it the worst
      Case Is <= pBestOld     'If <= old best
        Normalize = pBestNew    'Make it the best
      Case Else               'If it's within the old range
        Normalize = (pXOld - pWrstOld) / RangeOld  'Scale it
    End Select
  Case Else               '=0 = Point range (Best = Worst)
    Select Case pXOld       'Where is pXOld on the old (point) range?
      Case Is > pBestOld      'If > old point range,
        Normalize = pBestNew    'Make it the best
      Case Is < pWrstOld      'If < old point range
        Normalize = pWrstNew    'Make it the worst
      Case Else               'If = old point range,
        Normalize = (pWrstNew + pBestNew) / 2 'Make it the average (middle) value
    End Select
End Select

End Function

I'd appreciate any comments or suggestions.

 

[ExcelGzh]

Looks good so far. One adjustment I would make is to try to eliminate outliers. For example Rating 2 for Item C has a z-score of +1.93 whereas the other z-scores for Item C are -0.25, -0.23 & -0.63. Including +1.93 in the averages and rankings skews the final result a little bit. The way to screen out unrepresentative results like this is to do a statistical analysis of the z-scores. You can calculate the average and standard deviation of an item's z-scores and then produce another table of z-scores consisting of only z-scores that fall between some range of standard deviations. For example, the average of Item C's z-scores is +0.90 and the standard deviation is -0.25. If you say that only results in the range Average +/- (Standard Deviation x 1.50) are acceptable, that excludes the +1.93 result. You then take the averages of the revised z-scores and rank those. The 1.50 factor is completely arbitrary and can be anything you like. I use this system for a motorsport scoring system I have and I have found that over the long term a factor of 2.0 satisfactorily excludes unreasonable results while retaining realistic results.

You will find that if you use a factor if 1.50 then all the Items except Item F will have one result excluded. If you use a factor of 1.75 then Item A gets to keep all its results. Using either 1.50 or 1.75 changes the z-score ranking a little bit. Instead of 1-A, 2-C, 3-E, 4-B, 5-D, 6-F you get 1-A, 2-E, 3-C, 4-B, 5-D, 6-F. Entirely up to you to decide which is the more realistic/representative outcome.

 

[JenniferMurphy]

Looks good so far. One adjustment I would make is to try to eliminate outliers. For example Rating 2 for Item C has a z-score of +1.93 whereas the other z-scores for Item C are -0.25, -0.23 & -0.63. Including +1.93 in the averages and rankings skews the final result a little bit. The way to screen out unrepresentative results like this is to do a statistical analysis of the z-scores. You can calculate the average and standard deviation of an item's z-scores and then produce another table of z-scores consisting of only z-scores that fall between some range of standard deviations. For example, the average of Item C's z-scores is +0.90 and the standard deviation is -0.25. If you say that only results in the range Average +/- (Standard Deviation x 1.50) are acceptable, that excludes the +1.93 result. You then take the averages of the revised z-scores and rank those. The 1.50 factor is completely arbitrary and can be anything you like. I use this system for a motorsport scoring system I have and I have found that over the long term a factor of 2.0 satisfactorily excludes unreasonable results while retaining realistic results.

You will find that if you use a factor if 1.50 then all the Items except Item F will have one result excluded. If you use a factor of 1.75 then Item A gets to keep all its results. Using either 1.50 or 1.75 changes the z-score ranking a little bit. Instead of 1-A, 2-C, 3-E, 4-B, 5-D, 6-F you get 1-A, 2-E, 3-C, 4-B, 5-D, 6-F. Entirely up to you to decide which is the more realistic/representative outcome.

Interesting point. My initial reaction is that I want to keep all ratings. The ratings in these examples are all made up. I included some more extreme values to see what the algorithm would do with them and to compare the different methods. Now that I've pretty much settled on the z-score method, I'll try some actual values. But thanks for the comment. If I see outliers in the real data, I'll revisit your point.

 

[JenniferMurphy]

As far as the algorithim goes, it seems to produce a meaningful result but personally I would have used a z-score formula (review score - average score) / standard deviation

The Z Score suggestion was very helpful. Thank you for that.

I have been playing around with it and have come up with some additional parameters. I have added an "Order" parameter which tells the code whether higher values are better (such as for ratings, mileage, or capacity) or lower values are (such as for price or error rate). I also added a weighting parameter which I use to give more weight to properties that matter more to me.

Here's a little demo table with 3 properties for each of 7 items. The Weight and Order parameters are across the top. I set the weights all to "1" so I could show that the weighted Z Score sums (Col M) are the same as the unweighted ones (Col L). I also show that the rankings are the same for both (Cols O & P). The Price (Col D) uses a LoHi order. The other 2 use a HiLo order (Cols F & H). In Col J, I applied a factor and a difference to the Amazon Ratings to show that items that are similarly (or identically) dispersed will have similar (or identical) Z Scores.

Weighted Ratings.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Weight		1.00		1.00		1.00	1234.56
3	Order		LoHi		HiLo		HiLo	-5678	HiLo
4	Item	Price	Price ZScore LoHi	Range	Range ZScore	Amazon Rtg	Amazon Rtg ZScore	Amazon Rtg2	Amazon Rtg2 ZScore	ZScore Sum	Wtd ZScore Sum	Sum Δ	ZScore Sum Rank	Wtd ZScore Sum Rank	Rank Δ
5	E	$42.00	-0.6148	180	+1.3257	4.9	+1.1965	+371.34	+1.1965	+1.9073	+1.9073	=0.00	1	1	=0
6	D	$35.00	+0.3161	130	-0.4252	4.8	+0.7312	+247.89	+0.7312	+0.6221	+0.6221	=0.00	2	2	=0
7	G	$49.99	-1.6775	180	+1.3257	4.8	+0.7312	+247.89	+0.7312	+0.3794	+0.3794	=0.00	3	3	=0
8	F	$42.00	-0.6148	150	+0.2751	4.7	+0.2659	+124.43	+0.2659	-0.0738	-0.0738	=0.00	4	4	=0
9	C	$32.75	+0.6154	125	-0.6003	4.5	-0.6647	-122.48	-0.6647	-0.6496	-0.6496	=0.00	5	5	=0
10	B	$29.95	+0.9878	110	-1.1256	4.5	-0.6647	-122.48	-0.6647	-0.8025	-0.8025	=0.00	6	6	=0
11	A	$29.95	+0.9878	120	-0.7754	4.3	-1.5953	-369.39	-1.5953	-1.3829	-1.3829	=0.00	7	7	=0
12	Mean	$37.38	-0.0000	142.1429	+0.0000	4.6429	-0.0000	53.89	=0.0000	-0.0000	-0.0000	=0.00
13	Std Dev	$7.52	1.0000	28.5565	1.0000	0.2149	1.0000	265.33	1.0000	1.0918	1.0918
Simple

Cell Formulas
Range	Formula
I5:I11	I5	=ZScore([@[Amazon Rtg]],TblSimple[[#Totals],[Amazon Rtg]],$H$13,$I$3)
J5:J11	J5	=([@[Amazon Rtg]]*$J$2)+$J$3
K5:K11	K5	=ZScore([@[Amazon Rtg2]],TblSimple[[#Totals],[Amazon Rtg2]],$J$13,$K$3)
L5:L11	L5	=[@[Price ZScore LoHi]]+[@[Range ZScore]]+[@[Amazon Rtg ZScore]]
M5:M11	M5	= [@[Price ZScore LoHi]]*$E$2 + [@[Range ZScore]]*$G$2 + [@[Amazon Rtg ZScore]]*$I$2
N5:N11	N5	=[@[Wtd ZScore Sum]]-[@[ZScore Sum]]
O5:O11	O5	=RANK.EQ([@[ZScore Sum]],[ZScore Sum],0)
P5:P11	P5	=RANK.EQ([@[Wtd ZScore Sum]],[Wtd ZScore Sum],0)
Q5:Q11	Q5	=[@[Wtd ZScore Sum Rank]]-[@[ZScore Sum Rank]]
E5:E11	E5	=ZScore([@Price],TblSimple[[#Totals],[Price]],$D$13,$E$3)
G5:G11	G5	=ZScore([@Range],TblSimple[[#Totals],[Range]],$F$13,$G$3)
D12	D12	=SUBTOTAL(101,[Price])
E12	E12	=SUBTOTAL(101,[Price ZScore LoHi])
F12	F12	=SUBTOTAL(101,[Range])
G12	G12	=SUBTOTAL(101,[Range ZScore])
H12	H12	=SUBTOTAL(101,[Amazon Rtg])
I12	I12	=SUBTOTAL(101,[Amazon Rtg ZScore])
J12	J12	=SUBTOTAL(101,[Amazon Rtg2])
K12	K12	=SUBTOTAL(101,[Amazon Rtg2 ZScore])
L12	L12	=SUBTOTAL(101,[ZScore Sum])
M12	M12	=SUBTOTAL(101,[Wtd ZScore Sum])
N12	N12	=SUBTOTAL(101,[Sum Δ])
D13	D13	=STDEV.S(TblSimple[Price])
E13	E13	=STDEV.S(TblSimple[Price ZScore LoHi])
F13	F13	=STDEV.S(TblSimple[Range])
G13	G13	=STDEV.S(TblSimple[Range ZScore])
H13	H13	=STDEV.S(TblSimple[Amazon Rtg])
I13	I13	=STDEV.S(TblSimple[Amazon Rtg ZScore])
J13	J13	=STDEV.S(TblSimple[Amazon Rtg2])
K13	K13	=STDEV.S(TblSimple[Amazon Rtg2 ZScore])
L13	L13	=STDEV.S(TblSimple[ZScore Sum])
M13	M13	=STDEV.S(TblSimple[Wtd ZScore Sum])

Here's that same table once I applied weight=3 to prices (because I'm a cheapskate) and weight=2 to range (because I don't trust Amazon ratings). I then sorted on the weighted Z Score sum. Items E & D remained in 1st and 2nd place. But Item B jumped 3 positions to 3rd place.

Weighted Ratings.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Weight		3.00		2.00		1.00	1234.56
3	Order		LoHi		HiLo		HiLo	-5678	HiLo
4	Item	Price	Price ZScore LoHi	Range	Range ZScore	Amazon Rtg	Amazon Rtg ZScore	Amazon Rtg2	Amazon Rtg2 ZScore	ZScore Sum	Wtd ZScore Sum	Sum Δ	ZScore Sum Rank	Wtd ZScore Sum Rank	Rank Δ
5	E	$42.00	-0.6148	180	+1.3257	4.9	+1.1965	+371.34	+1.1965	+1.9073	+2.0034	+0.10	1	1	=0
6	D	$35.00	+0.3161	130	-0.4252	4.8	+0.7312	+247.89	+0.7312	+0.6221	+0.8292	+0.21	2	2	=0
7	B	$29.95	+0.9878	110	-1.1256	4.5	-0.6647	-122.48	-0.6647	-0.8025	+0.0475	+0.85	6	3	+3
8	C	$32.75	+0.6154	125	-0.6003	4.5	-0.6647	-122.48	-0.6647	-0.6496	-0.0192	+0.63	5	4	+1
9	A	$29.95	+0.9878	120	-0.7754	4.3	-1.5953	-369.39	-1.5953	-1.3829	-0.1828	+1.20	7	5	+2
10	F	$42.00	-0.6148	150	+0.2751	4.7	+0.2659	+124.43	+0.2659	-0.0738	-1.0283	-0.95	4	6	-2
11	G	$49.99	-1.6775	180	+1.3257	4.8	+0.7312	+247.89	+0.7312	+0.3794	-1.6499	-2.03	3	7	-4
12	Mean	$37.38	-0.0000	142.1429	=0.0000	4.6429	-0.0000	53.89	=0.0000	-0.0000	-0.0000	-0.00
13	Std Dev	$7.52	1.0000	28.5565	1.0000	0.2149	1.0000	265.33	1.0000	1.0918	1.1914
Simple

Cell Formulas
Range	Formula
I5:I11	I5	=ZScore([@[Amazon Rtg]],TblSimple[[#Totals],[Amazon Rtg]],$H$13,$I$3)
J5:J11	J5	=([@[Amazon Rtg]]*$J$2)+$J$3
K5:K11	K5	=ZScore([@[Amazon Rtg2]],TblSimple[[#Totals],[Amazon Rtg2]],$J$13,$K$3)
L5:L11	L5	=[@[Price ZScore LoHi]]+[@[Range ZScore]]+[@[Amazon Rtg ZScore]]
M5:M11	M5	= [@[Price ZScore LoHi]]*$E$2 + [@[Range ZScore]]*$G$2 + [@[Amazon Rtg ZScore]]*$I$2
N5:N11	N5	=[@[Wtd ZScore Sum]]-[@[ZScore Sum]]
O5:O11	O5	=RANK.EQ([@[ZScore Sum]],[ZScore Sum],0)
P5:P11	P5	=RANK.EQ([@[Wtd ZScore Sum]],[Wtd ZScore Sum],0)
Q5:Q11	Q5	=[@[ZScore Sum Rank]]-[@[Wtd ZScore Sum Rank]]
E5:E11	E5	=ZScore([@Price],TblSimple[[#Totals],[Price]],$D$13,$E$3)
G5:G11	G5	=ZScore([@Range],TblSimple[[#Totals],[Range]],$F$13,$G$3)
D12	D12	=SUBTOTAL(101,[Price])
E12	E12	=SUBTOTAL(101,[Price ZScore LoHi])
F12	F12	=SUBTOTAL(101,[Range])
G12	G12	=SUBTOTAL(101,[Range ZScore])
H12	H12	=SUBTOTAL(101,[Amazon Rtg])
I12	I12	=SUBTOTAL(101,[Amazon Rtg ZScore])
J12	J12	=SUBTOTAL(101,[Amazon Rtg2])
K12	K12	=SUBTOTAL(101,[Amazon Rtg2 ZScore])
L12	L12	=SUBTOTAL(101,[ZScore Sum])
M12	M12	=SUBTOTAL(101,[Wtd ZScore Sum])
N12	N12	=SUBTOTAL(101,[Sum Δ])
D13	D13	=STDEV.S(TblSimple[Price])
E13	E13	=STDEV.S(TblSimple[Price ZScore LoHi])
F13	F13	=STDEV.S(TblSimple[Range])
G13	G13	=STDEV.S(TblSimple[Range ZScore])
H13	H13	=STDEV.S(TblSimple[Amazon Rtg])
I13	I13	=STDEV.S(TblSimple[Amazon Rtg ZScore])
J13	J13	=STDEV.S(TblSimple[Amazon Rtg2])
K13	K13	=STDEV.S(TblSimple[Amazon Rtg2 ZScore])
L13	L13	=STDEV.S(TblSimple[ZScore Sum])
M13	M13	=STDEV.S(TblSimple[Wtd ZScore Sum])

This is getting too complicated for Excel. I think I will move it to a database. Then I can add other paramaters such as the max/min you suggested above.

Do you (or anyone else) have any comments or suggestions?

 

[JenniferMurphy]

PS: Here's the code for the ZScore function:

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

 

[JenniferMurphy]

JenniferMurphy

The idea of using a Variant to deal with nulls seems fine to me.

As far as the algorithim goes, it seems to produce a meaningful result but personally I would have used a z-score formula (review score - average score) / standard deviation

And for the Test reviewer, you could put in additional smarts (meaning more helper tables) to exclude reviewers or products that have less than a minimum number of reviews.

I should have marked this as the answer long ago. Sorry about that. Z Scores are even more useful than I first thought. Thanks for that tip.