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

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

Thank you