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
