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.

**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