Excel: Calculate Average of Numbers Separated by Commas


You can use the following formula to calculate the average value of numbers in a cell in Excel that are separated by commas:

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

This particular formula calculates the average of the comma-separated values in cell A2.

For example, if cell A2 contains 1,2,3 then this formula would return 2 since this is the average of these values:

Average: (1+2+3) / 3 = 2

The following example shows how to use this formula in practice.

Example: Calculate Average of Numbers Separated by Commas in Excel

Suppose we have the following column of comma-separated values in Excel:

Suppose we would like to calculate the average value in each cell in column A.

If we simply tried to use the AVERAGE() function, we would receive a #DIV/0! error in each cell because this function cannot handle comma-separated values:

Instead, we must type the following formula into cell B2:

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

We can then click and drag this formula down to each remaining cell in column B:

Excel average of numbers separated by commas

Column B now displays the average of each list of comma-separated values in column A.

For example:

  • The average of 2,4,5,5,7,13 is 6.
  • The average of 3,5,6,8 is 5.5.
  • The average of 10,12,14,14,15,19 is 14.

And so on.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Calculate Average If Cell Contains Number in Excel
How to Use AVERAGEIF with Multiple Ranges in Excel
How to Calculate Average Excluding Outliers in Excel

One Reply to “Excel: Calculate Average of Numbers Separated by Commas”

  1. The whole formula for this is cut off the edge of the screen. Could you please send me the full formula?
    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *