Formula requested to determine an average of multiple values entered in a single cell

[TheEngineer]

I need a formula that will calculate the average from a list of numbers in a single cell. These numbers are entered as individual percent values, and separated by commas. An example would be:

A1 = [55%, 60%, 7%, 92%]

For the above example list, the formula I require would spit out 54% (rounded).

I am running Excel 03.

I found a similar problem in a previous thread, but I couldn't modify the solution formula for 2 digit numbers (from 1 digit).

Thanks for sharing your knowledge,
JN

 

[JoeMo]

I need a formula that will calculate the average from a list of numbers in a single cell. These numbers are entered as individual percent values, and separated by commas. An example would be:

A1 = [55%, 60%, 7%, 92%]

For the above example list, the formula I require would spit out 54% (rounded).

I am running Excel 03.

I found a similar problem in a previous thread, but I couldn't modify the solution formula for 2 digit numbers (from 1 digit).

Thanks for sharing your knowledge,
JN

Here's a UDF if you don't mind enabling macros.

Function AvgValuesInCell(R As Range)
Dim vA As Variant, S As Double

If R.Cells.Count = 1 Then
    vA = Split(R.Value, ", ")
    For i = LBound(vA) To UBound(vA)
        S = S + Val(vA(i))
    Next i
    AvgValuesInCell = Format((S / 100 / (UBound(vA) - LBound(vA) + 1)), "0%")
End If
End Function

After you install it, use it like this in say cell B1:

=AvgValuesInCell(A1)

 

[Vidar]

Hi

My take on this with formula:
=AVERAGE(LEFT(A1,FIND("%",A1,1)-1)+0,MID(A1,FIND("%",A1,1)+3,(FIND("zzz",SUBSTITUTE(A1,"%","zzz",2),1))-(FIND("%",A1,1)+3)),MID(A1,FIND("zzz",SUBSTITUTE(A1,",","zzz",2),1)+2,(FIND("zzz",SUBSTITUTE(A1,"%","zzz",3),1))-(FIND("zzz",SUBSTITUTE(A1,",","zzz",2),1)+2)+0),MID(A1,FIND("zzz",SUBSTITUTE(A1,",","zzz",3),1)+2,(FIND("zzz",SUBSTITUTE(A1,"%","zzz",4),1))-(FIND("zzz",SUBSTITUTE(A1,",","zzz",3),1)+2)+0))
Cell A1 contains the values.

Vidar

 

[Aladin Akyurek]

I need a formula that will calculate the average from a list of numbers in a single cell. These numbers are entered as individual percent values, and separated by commas. An example would be:

A1 = [55%, 60%, 7%, 92%]

For the above example list, the formula I require would spit out 54% (rounded).

I am running Excel 03.

I found a similar problem in a previous thread, but I couldn't modify the solution formula for 2 digit numbers (from 1 digit).

Thanks for sharing your knowledge,
JN

A generic set up...

Add the following code for EVAL by Charles Williams to your workbook, using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

If A1 can house:

55%, 60%, 7%, 92%

Run in B1...

=AVERAGE(eval("{"&SUBSTITUTE(A1,"%","")&"}"))/100

If you use decimals (representing percentages) in A1, that is:

0.55,0.6,0.07,0.92

Run in B1 instead...

=AVERAGE(eval("{"&A2&"}"))

Note that comma is used as list separator.

 

[T. Valko]

I need a formula that will calculate the average from a list of numbers in a single cell. These numbers are entered as individual percent values, and separated by commas. An example would be:

A1 = [55%, 60%, 7%, 92%]

For the above example list, the formula I require would spit out 54% (rounded).

I am running Excel 03.

I found a similar problem in a previous thread, but I couldn't modify the solution formula for 2 digit numbers (from 1 digit).

Thanks for sharing your knowledge,
JN

Does the cell contain the brackets [ ] ?

Will there always be only 4 numbers?

Will the maximum number be 100%?

It would be a good idea to post SEVERAL representative samples of the data.