AVERAGE of comma separated numbers in a single cell

I have a number of cells that contain comma separated lists of numbers in them.
The lists are of varying lengths, and theoretically some of the numbers could be double-digits.

For example:

A1 = 1,1,1,2,1,5,3
B1 = 2,10,4
etc.

I would like to find the average of each cell, as well as the overall average of all cells.

I realise I could use text-to-columns, but the data needs to remain as it is.

Thank you.

Please Login or Register  to view this content.

=Ave_cell($a1)

=Sum_cell($a1)



Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]

Thanks John.

The Ave_cell results don't appear to be correct - they show as 2.333 and 8 respectively, but should be 2 and 5.333 ?
I'm guessing it's because the divisor is including the comma count?

Also, can these be adapted to accept a range as well as a single cell?

Or try this ...

=IFERROR(AVERAGE(--MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1))),
ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1),LEN(A1))),A1)

Enter with Ctrl+Shift+Enter.

.... should be

in AVE_CELL function

Ave_Cell = Ave_Cell / (UBound(LArray) + 1)

Please give example of a range requirement.

Originally Posted by JohnTopley

Please give example of a range requirement.

Using the same example file with similar comma separated lists in A1:A10 ... =Ave_Cell(A1:A10) etc.

Please Login or Register  to view this content.

=ave_range(A1:a10)

Originally Posted by JohnTopley

=ave_range(A1:a10)

Perfect. Thank you.