JenniferMurphy
Wellknown Member
 Joined
 Jul 23, 2011
 Messages
 1,438
 Office Version

 365
 Platform

 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 applestoapples 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:
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:
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
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:
 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.
 Is my method of testing for missing ratings correct or is there a better way?
 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 110 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.