How can I calculate the average of a list of numbers separated by commas in Excel?

By /

To calculate the average of a list of numbers separated by commas in Excel, you can use the AVERAGE function. This function takes the values in a range of cells and calculates the arithmetic mean, or average, of those values. Simply select the range of cells containing the numbers, and then enter the AVERAGE function in a cell where you want the result to appear. This will provide you with the average of the numbers in the selected range, separated by commas.

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:

Excel average of numbers separated by commas

Column B now displays the average of each list of comma-separated values in column A.

  • 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:

x

Related terms:

  1. Excel: Calculate Average of Numbers Separated by Commas
  2. How can I calculate the average of a set of numbers in Excel while excluding the highest and lowest values?
  3. How can I use Google Sheets to calculate the average of a set of numbers while rounding to the nearest whole number?
  4. How do I find the first 3 positive numbers in Excel when I have a column of numbers?
  5. How can I split comma-separated values into rows in Excel?
  6. How to calculate the mean of a list of numbers?
  7. How can I calculate the average of a group of numbers between two dates in Google Sheets?
  8. How do you split comma-separated values into rows in Excel?
  9. How can I add commas between words in Excel? Can you provide an example?
  10. How can I count the number of commas in a cell in Excel?