Function Problems

How to Sum Ten Columns and Calculate an Average in Excel

Guest Writer Oct 10, 2026 869 views

Question details

The user needs an Excel formula to calculate an aggregate score by adding values from ten columns and dividing the total by ten.

How to Sum Ten Columns and Calculate an Average in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating a final aggregate score by combining data values spread across ten adjacent columns.
Observed behavior: The user is looking for the exact syntax and optimal functions (like SUM or AVERAGE) to aggregate ten column values and find their mathematical average.

Before you start

Verify that the ten columns you intend to average contain numerical data, and ensure there are no hidden columns within your selected range that might inadvertently alter the final calculation.

Solution 1Recommended

Calculate the Average Using the AVERAGE Function

This is the most efficient and recommended method because it automatically handles the range, sums the values, and divides by the correct count.

The AVERAGE function simplifies the process by performing both the addition and division in a single step. It is highly recommended because it automatically excludes blank cells or text values from the count.

Select the result cell

Click on the cell where you want the calculated average to appear.

Enter the AVERAGE formula

Type =AVERAGE(A1:J1) in the formula bar, replacing A1:J1 with the actual range covering your ten columns.

Apply the calculation

Press Enter. The cell will now display the average score of the selected ten columns.

Calculate the Average Using the AVERAGE Function

Handling Empty Cells: If any of the ten columns are blank, the AVERAGE function will automatically ignore them and divide the total by the number of cells that actually contain numbers.

Solution 2

Calculate the Average Using the SUM Function

Use this method if you need to strictly force a division by 10, regardless of whether some cells in the range are blank.

Advanced Spreadsheet Tool

Calculate Sums and Averages Seamlessly in WPS Office

WPS Spreadsheet provides an intuitive interface and built-in AutoSum features to easily calculate averages across multiple columns. It perfectly supports all standard Excel functions for smooth data analysis.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the ten columns you wish to analyze.
2. Highlight the destination cell: Click the empty cell at the end of the row where the aggregate score should be calculated.
3. Use the AutoAverage feature: Navigate to the Formulas tab on the ribbon, click the arrow next to AutoSum, and select 'Average'. WPS will automatically detect the ten adjacent columns.
4. Confirm the result: Press Enter to execute the formula and instantly view your aggregated average score.

Fully compatible with Microsoft Excel formulas like SUM and AVERAGE.One-click AutoSum and AutoAverage buttons for instant calculations.Smart formula suggestions to prevent syntax errors.Free, lightweight, and easy-to-use interface for complex datasets.

microsoft office alternative - wps office

Frequently Asked Questions

What happens if one of the ten columns has a blank cell?

If you use the =AVERAGE(range) formula, Excel ignores the blank cell and divides the total by 9 instead of 10. If you use =SUM(range)/10, Excel still divides the total sum by 10, treating the blank space effectively as a zero.

How do I calculate the average of ten non-adjacent columns?

To average columns that are not next to each other, you can separate individual cell references with commas inside the formula, like this: =AVERAGE(A1, C1, E1, G1, I1, K1, M1, O1, Q1, S1).

How can I apply this average formula to hundreds of rows at once?

Once you have typed the formula into the first row and pressed Enter, click on that result cell. Hover your mouse over the small square at the bottom-right corner of the cell until it turns into a plus sign (+), then click and drag down to fill the formula across all other rows.

Excel & Data Problems