How to find an average

A total tells you how much happened altogether. An average tells you what a typical one looks like. A shift supervisor might ask “How many customers do we usually get per day?” A teacher wants the class’s typical test score, and a sales manager wants the average order size. For all of these, you use AVERAGE.

The example

A shop counts its customers each day for one week, Monday to Saturday. The manager wants the daily average in B8 to plan staffing.

Step by step

1. Click the answer cell, B8. The result appears where the formula is typed.
2. Type =AVERAGE(.
3. Enter the range of numbers, B2:B7, by typing it or dragging over those cells. Leave out the heading in B1 and the answer cell B8.
4. Close the parenthesis and press Enter. The formula =AVERAGE(B2:B7) shows 48.

Blank cells and zeros are not the same

This is the point that trips up most beginners. Suppose Saturday’s count has not been entered yet:

• If B7 is empty, AVERAGE ignores it. It divides the five counts (243) by 5 and shows 48.6.
• If B7 contains 0, AVERAGE treats it as a real day with no customers. It divides 243 by 6 and shows 40.5.

Both answers are “correct.” They answer different questions. Leave a cell blank when the data is missing. Type 0 only when zero really happened, for example a day the shop was open but nobody came in.

Common mistakes

• Including the total row. If a column already has a total at the bottom, a range that covers it will pull the average far too high. Stop the range on the last real data row.
• Using SUM when the question says “typical” or “per day.” “How many altogether?” calls for SUM. “How many on a normal day?” calls for AVERAGE.
• Seeing too many decimals. An average of 47.3333333 is correct but hard to read. Wrap it in ROUND: =ROUND(AVERAGE(B2:B7), 1) keeps one decimal place. Use 0 for a whole number or 2 for money.
• Getting #DIV/0!. This error means the range has no numbers at all, so there is nothing to divide by. Check that the range points at the right column and that the numbers are not stored as text.

Questions first-time users ask

What is the difference between average, mean, and median?

“Average” and “mean” are the same thing in a spreadsheet: add, then divide. The median is the middle value when the numbers are lined up in order, and you get it with =MEDIAN(B2:B7). The median is useful when one number is unusually large. If five orders are around $40 and one is $900, the average jumps to well over $100, but the median stays close to $40.

Can I average only some of the rows?

Yes, with AVERAGEIF. If column A holds a region and column B holds sales, =AVERAGEIF(A2:A20, "North", B2:B20) averages only the North rows.

Does AVERAGE update when I add a new day?

Only if the new row falls inside the range. If you add Sunday in row 8 and move the average down, change the range to B2:B8.

Does it work in Excel and Google Sheets?

Yes. AVERAGE, MEDIAN, ROUND, and AVERAGEIF are written the same way in Excel, Google Sheets, and LibreOffice Calc.

Practice it

Related guides: totaling sales with SUM · counting order statuses · formulas used at work · all guides