How to calculate average ratings of variable lists

[JenniferMurphy]

I need to keep track of a series of ratings of several products. Each product will have one or more ratings on either a 0-10 or 0-100 scale. The number of ratings for each product will vary from 1 to some unknown number (probably less that 100).

Here's some sample data.

The only thing I could come up with is a little UDF to do the average. Here's that code:

Function ListCalc(pList)

Dim ListArray() As String
ListArray = Split(pList, " ")

Dim ListSum As Double
ListSum = 0

Dim ListCount As Long
ListCount = UBound(ListArray)

Dim i As Long
For i = 0 To ListCount
    ListSum = ListSum + CDbl(ListArray(i))
Next i

ListCalc = ListSum / (ListCount + 1)

End Function

Is there a better way?

Thanks

 

[StephenCrump]

A formula way:

	A	B
1	30.0000	30 40 20
2	52.3750	50 56 47 43 59 48 62 54
Sheet1

Cell Formulas
Range	Formula
A1:A2	A1	=AVERAGE(MyNumbers)

MyNumbers: =EVALUATE("{"&SUBSTITUTE(TRIM(!B1)," ",",")&"}")
(For cell A1, i.e. relative reference is to cell on the right - adjust as required)

 

[JenniferMurphy]

A formula way:

	A	B
1	30.0000	30 40 20
2	52.3750	50 56 47 43 59 48 62 54
Sheet1

Cell Formulas
Range	Formula
A1:A2	A1	=AVERAGE(MyNumbers)

MyNumbers: =EVALUATE("{"&SUBSTITUTE(TRIM(!B1)," ",",")&"}")
(For cell A1, i.e. relative reference is to cell on the right - adjust as required)

I get a "That function isn't valid" error message.

 

[JenniferMurphy]

After doing a little research, it appears that the Evaluate function is an old function that it still more or less supported, but must be used "in the confines of a named range".

 

[StephenCrump]

After doing a little research, it appears that the Evaluate function is an old function that it still more or less supported, but must be used "in the confines of a named range".

Correct. So with the cursor in A1, select Name Manager/New ...
and define MyNumbers: =EVALUATE("{"&SUBSTITUTE(TRIM(!B1)," ",",")&"}")

 

[JenniferMurphy]

Interesting. This is weird even for Excel. I think I'll stick with my UDF, At least I more or less understand what it is doing.

Thanks