# 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: 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.