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:

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

• 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.

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