Excel AVERAGE: 3 cell types it skips in sales

Key takeaways

• Keep notes and TRUE or FALSE flags outside the amount column.
• Check amounts that look numeric but may be stored as text.
• Agree on when to record a zero and when to leave a cell blank.
• Trace errors before using the average.

Why this matters at the till

If you run a shop or work a till, you have probably used Excel to find out what a typical sale is worth. You select a column of sale amounts, type =AVERAGE(, and read the answer. The result looks official, and it is quick. It is also only as good as the cells Excel reads, and not every cell in a sales column is read the way you might expect.

This post explains each of the three skipped types, shows how a small change moves the answer, and gives you a short routine for checking your own column before you rely on the number. None of this needs advanced Excel. It needs a clear idea of what the function is doing and a habit of looking at the cells it used.

What AVERAGE is told to do

Microsoft's reference page for the AVERAGE function describes it as taking "1 to 30 numeric arguments for which you want the average." Microsoft Learn: WorksheetFunction.Average method Those arguments can be numbers that you type straight into the formula, or they can be names, arrays, or references that contain numbers.

When you write the formula over your amount column, you are passing a reference. That is a block of cells, not a list of typed numbers, and it is the normal case for a sales sheet. This is also where the skipping happens. Excel looks at each cell in the block and decides whether that cell counts toward the average.

The page gives the rule directly: "If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included." Microsoft Learn: WorksheetFunction.Average method

Put plainly, Excel does not average the cells that are not numbers, and it does not treat a blank as zero. It leaves those cells out of the total and out of the count it divides by. That second part matters more than it first appears. Leaving a cell out changes the divisor, so the average moves even though the total you can see on screen might look unchanged.

If you pull some out because they are unreadable, the average of the ones you kept is fair for those alone, but it is not an average of the whole pile. AVERAGE does the same thing silently. It works with the cells it accepts and says nothing about the ones it dropped.

The first cell type: text

Text is any cell whose contents Excel reads as words or characters rather than as a number. On a sales sheet, text usually turns up in one of three ways.

The first is a note typed into the amount column. Staff write things like "void", "comp", "n/a", "see manager", or "card declined" in the cell where the amount should go. Those words are text, so they are ignored. The note might be useful information, but it is not a sale amount, and AVERAGE treats it accordingly.

The second is a figure that was pasted in from somewhere else and arrived as characters. A copy from a web page, a report from another program, or an export from a system that does not format numbers the way Excel does can all bring amounts across as text. The cell may show a figure that looks exactly like a number and still be skipped.

The third is a number entered with something Excel does not recognise as part of a number. You may not notice, because the cell looks right.

Each of these is ignored by AVERAGE. The formula still runs and still returns a result, so nothing looks broken. The average is simply based on fewer sales than you think. That is the danger of a skipped cell: the sheet does not complain.

The second cell type: logical values

A logical value is TRUE or FALSE. Staff sometimes use them as quick flags. These are useful markers, and Excel is happy to store them.

If those flags sit in the same column as your amounts, AVERAGE ignores them. They do not count as sales and they do not affect the divisor.

This is handy when you want a flag column beside your amounts. It becomes a problem when flags end up in the amount column by accident, for example after a sort, a paste, or a row inserted in the wrong place. Then a row that looks like a sale is missing from the average, and you may not see why.

The fix is to keep the flags in their own column and to check the amount column on its own before you average it. Scan it for anything that is not a figure, then type the formula.

The third cell type: empty cells

An empty cell is one with nothing in it at all. These are skipped as well. A blank row between two days of sales, a line left empty because the till was closed, or a cell cleared while correcting an entry will all drop out of the average.

Skipping blanks is usually what you want. A day with no trading should not pull the average down. Treating an empty row as a zero sale would make the average less useful, not more.

The trouble comes when a blank stands in for a real sale that nobody wrote down. The formula cannot know that the sale happened. It calculates the average on the sales that were recorded and quietly leaves out the one that was not. If you are using the average to decide prices or how much stock to order, a missing sale can shift that decision. For the stock decision, also review your shop replenishment checks.

So a blank is safe only when it really means "nothing happened here." If a blank could mean "we sold something and did not write it down," the column needs a different process. That is a process problem more than an Excel problem, and it is worth fixing at the till.

Zero is not the same as blank

A zero is treated as a real amount of nothing. It counts in the divisor. Microsoft Learn: WorksheetFunction.Average method

This is where averages go wrong on sales sheets. Suppose a customer returns an item, or a sale is voided after the amount was typed in. A clerk who wants the day to balance types zero into the amount column. The row now counts as a sale of nothing. The average falls, and nothing in the formula tells you why.

Compare that with leaving the cell blank. The same void, entered as a blank, would be skipped entirely, and the average would be based only on sales that brought money in.

Neither choice is always right. A zero can be the honest record of a transaction that brought in nothing, such as a comped item that still went through the till. A blank can be the honest record of a line that should not be in the sheet at all. What matters is that you know which one you chose and that everyone who uses the sheet makes the same choice.

Illustrative example: a zero becomes a blank

Consider an illustrative sales column containing 20, 20 and 0.

Now imagine the same comped item is entered as a blank. The same morning gives two different answers, and the only change was one cell.

Next, suppose someone typed "n/a" into a row below the sales, inside the range the formula covers. That cell is text, so it is ignored. Neither average changes because of the note, which is exactly the behaviour the Microsoft page describes.

Errors are different from skipped cells

Errors are not ignored. Microsoft Learn: WorksheetFunction.Average method In practice, a cell showing an error code such as #N/A can stop AVERAGE from giving you a number at all.

That can feel annoying, but it is a useful outcome. The sheet is telling you something is wrong instead of hiding it. If AVERAGE returns an error, find the cell that caused it first, fix or remove it, and the formula will work again. Wrapping the formula in a function that hides errors removes the warning but not the problem.

Numbers that look right but are text

This is one of the easiest traps to miss. A till export, a copy from a web page, or a file from another system can bring in amounts that look like numbers but are stored as text. The cell shows the amount and looks just like its neighbours, yet AVERAGE skips it.

Check suspect amounts against the original sales record.

A cell's appearance alone does not establish whether it contains a number.

Compare the recorded sales with the numeric amounts in your range. Look for entries that need checking rather than assuming every visible amount was included.

Once you know there is a gap, sort or filter the column to show anything that is not a number, and go through those rows. Often the text cell is a note that belongs in another column. Sometimes it is a figure that needs retyping or converting into a number.

A quick check before you trust the number

Before you use an average for pricing, a stock order, a report, or a conversation with a bank or accountant, run a short routine on the column. It takes a few minutes and catches most of the problems in this post.

First, count the sales you expect. Know roughly how many transactions went through the column in that period, from the till roll, the card terminal, or your own notes. Compare that with the count of numbers in the column. If they do not match, find out why before going further.

Second, review non-empty cells that contain notes or flags. Check whether any should instead hold a recorded sale amount, and correct them against the original record.

Third, look for zeros. Filter or sort the column and see how many there are. Each zero should have a reason, such as a comped item or a recorded return. A zero you cannot explain may be an entry someone typed to make a day balance, and it will pull the average down.

Fourth, look for errors. Any #N/A in the range should be traced to its source before you read the average.

Last, write the expected count and the actual count next to the average in your sheet, along with how many comped lines were recorded as zero. This note will save a lot of questions later. If the counts match and the zeros are explained, you can trust the number. If not, it is not ready to use. For a related closing check, see cash drawer reconciliation.

Keeping the column clean at the till

The best fix for skipped cells is to prevent them. A few agreed rules, shared by everyone who touches the sheet, will do more than any clever formula.

Enter amounts as numbers only. Keep notes, reasons, and comments in a separate column, so the amount column holds nothing but figures. If a note matters, it can still sit on the same row, just not in the cell the formula reads.

Decide how voids and comps are recorded, and write the decision down where staff can see it. You might leave the amount blank and add a note in the next column, or record a zero with a note. Either can work. What matters is that everyone picks the same one every time, so the average means the same thing from one week to the next.

Keep TRUE and FALSE flags, and yes or no markers, in their own column. Mixing them into the amount column is a common way for a row to fall out of the average unnoticed.

Be careful when you paste. When you bring figures in from a till report or another program, paste into a column formatted for numbers, then run the counting check before you rely on the result. A paste that looks perfect can still leave text behind.

Do not paste values over the amount column without checking the result afterwards. A single overwritten cell can change the average, and it may not be obvious which cell changed.

Check one sales column today

Before you trust an average, count the numbers, compare that with the count of non-empty cells, and look for zeros and errors. Agree on how voids and comps are recorded, and keep the amount column for amounts only. These steps catch many of the mistakes the three skipped cell types cause, and they give you an average you can explain to anyone who asks how it was calculated.

Related guides: Average ticket: 3 figures to check against costs · Tax-included prices: 3 steps to back out sales tax · Card and cash sales: 3 amount limits for your till.

Questions

Where should a note about a sale go?

Put the note in a separate column on the same row. That keeps the explanation beside the amount without replacing it.

What should I check after correcting an amount?

Check the average again after correcting the amount. Compare the result with your expected sales count before using it for a decision.

Why is my average lower than I expected?

Zeros in the column count in the divisor and pull the average down, so filter the column and check each zero.

Does a blank cell count as a sale?

No. A blank is skipped entirely, so it leaves both the total and the divisor unchanged.