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:


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:


We can then click and drag this formula down to each remaining cell in column B:

Excel average of numbers separated by commas

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

Leave a Reply

Your email address will not be published. Required fields are marked *