Please critique this little UDF to average ratings

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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.

20200323 1313 PW Manager ratings, detailed.jpg


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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,317
Members
415,966
Latest member
ctorohuamanchumo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top