The AVERAGE function is the fastest way to find a mean

The simplest way to calculate an average in Excel is the AVERAGE function. Type =AVERAGE(, select the cells you want to average, close the parenthesis, and press Enter. Excel adds up all the numbers and divides by how many numbers there are. For example, =AVERAGE(A1:A10) averages the ten cells from A1 to A10.

You can also type the cell references manually instead of selecting them. If your numbers are in cells B2, B5, and B8 with gaps between them, type =AVERAGE(B2,B5,B8) and press Enter. Excel treats each cell you list separately, so you do not have to select a continuous range.

The AVERAGE function ignores empty cells and text. If you have ten cells selected but two are blank, Excel averages only the eight cells with numbers in them. This makes it useful when your data has gaps.

Key Takeaways

  • Type =AVERAGE(A1:A10) to average a range of cells, or =AVERAGE(A1,A3,A5) to average specific cells that are not next to each other.
  • The AVERAGE function ignores empty cells and text, so it works even if your data has gaps or labels mixed in.
  • AVERAGEIF lets you average only cells that meet a condition, such as numbers greater than 50 or cells in a certain category.
  • You can also calculate an average manually by typing =SUM(A1:A10)/COUNT(A1:A10) if you need to see how the calculation works.

Using AVERAGEIF to average only cells that meet a condition

Sometimes you need to average only the numbers that match a certain rule. The AVERAGEIF function does this. The syntax is =AVERAGEIF(range, criteria, average_range). The first part is the range you want to check, the second is the condition, and the third is the range to average.

For example, if column A holds product names and column B holds sales numbers, and you want to average sales only for the product "Widget", type =AVERAGEIF(A:A,"Widget",B:B). Excel looks at every cell in column A, finds the ones that say "Widget", and averages the matching numbers in column B.

You can also use comparison operators. Type =AVERAGEIF(B:B,">50") to average only numbers in column B that are greater than 50. The quotes around the condition are required when you use operators like >, <, >=, <=, or <> (not equal to).

Calculating an average manually with SUM and COUNT

If you want to see how an average is calculated step by step, you can build it yourself using SUM and COUNT. Type =SUM(A1:A10)/COUNT(A1:A10). This adds all the numbers in A1 through A10 and divides by how many numbers there are, which is exactly what AVERAGE does.

This approach is useful when you are teaching someone how averages work or when you need to show the math in separate columns. You might put the sum in one cell, the count in another, and the division in a third so someone reading your spreadsheet can follow your work.

The COUNT function counts only cells with numbers in them, so like AVERAGE, it ignores empty cells and text. If you use COUNT on a range with five numbers and five blank cells, it returns 5.

Averaging only numbers, not text or blanks

Excel has a few functions that handle different types of data. AVERAGE ignores text and empty cells automatically. If you want to be more specific, AVERAGEA counts text as zero, which usually gives you a different result.

For most work, AVERAGE is what you want. It skips over any cell that does not contain a number, so your calculation stays clean. If your data has labels, notes, or empty rows, AVERAGE handles them without extra steps.

If you have a range where some cells contain formulas that return text (like an error message), AVERAGE will skip those cells too. This makes it forgiving when your data is messy or incomplete.

Averaging cells across multiple sheets

You can average numbers that are on different sheets in the same workbook. Type =AVERAGE(Sheet1!A1:A10,Sheet2!A1:A10) to average the same range on two different sheets. The exclamation mark tells Excel to look on a specific sheet.

If your sheets have similar names like "January", "February", and "March", you can use a shorthand. Type =AVERAGE(January:March!A1:A10) to average the same range across all sheets from January through March in order. This works only if the sheets are next to each other and named in a way that makes sense as a sequence.

For more complex situations where sheets are scattered or named randomly, list each one separately with commas, like =AVERAGE(Sales!B5,Forecast!B5,Archive!B5).

Weighted averages when numbers have different importance

A regular average treats every number the same. A weighted average gives more importance to some numbers than others. For example, if a test is worth 40% of a grade and homework is worth 60%, you would use a weighted average.

Calculate a weighted average by multiplying each number by its weight, adding those products together, and dividing by the sum of the weights. In Excel, type =SUMPRODUCT(values, weights)/SUM(weights). If test scores are in A1:A5 and their weights are in B1:B5, type =SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5).

SUMPRODUCT multiplies each test score by its weight, adds all those products, and then you divide by the total weight. This gives you a single number that reflects how important each piece of data is.

Frequently Asked Questions

What is the difference between AVERAGE and AVERAGEA?

AVERAGE ignores text and empty cells. AVERAGEA counts text as zero and empty cells as zero, so it usually gives a lower result. For most work, use AVERAGE. Use AVERAGEA only if you specifically need text treated as zero.

Can I average cells if some contain formulas?

Yes. AVERAGE works on the result of a formula, not the formula itself. If a cell contains =5+5, AVERAGE sees it as 10 and includes it in the calculation. If a formula returns an error like #DIV/0!, AVERAGE skips that cell.

How do I average only the top five numbers in a range?

Use =AVERAGE(LARGE(A1:A100,ROW(1:5))) and press Ctrl+Shift+Enter to make it an array formula. LARGE pulls out the largest numbers one at a time, and AVERAGE finds their mean. This is more complex than basic AVERAGE, so use it only when you need this specific behavior.

What happens if I average cells with different number formats?

Excel averages the actual numbers, not the format. If one cell shows 50% and another shows 0.5, they are the same number and average the same way. The format (percentage, decimal, currency) does not change how the average is calculated.