# Please critique this little UDF to average ratings

#### JenniferMurphy

##### Well-known Member
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:
VBA 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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### ExcelGzh

##### Board Regular
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

##### Well-known Member
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.

Replies
5
Views
64
Replies
8
Views
203
Replies
0
Views
252
Replies
8
Views
508
Replies
6
Views
507

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back