Fixing #DIV/0! errors

1 / 4

The average order value is sales divided by orders. But Quinn has 0 orders, and dividing by 0 gives #DIV/0!.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDE
1NameSalesOrdersAverage order
2Tariq1827
3Nadia154022
4Daniel87015
5Quinn00
6Jamal72030
7Isla00
8Kofi80523
9
10
11
12
sales, orders
average order
no orders
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How IFERROR and IF works

  • What does #DIV/0! mean?

    You divided by zero, or by an empty cell (Excel treats an empty cell as 0). An average order value with no orders yet is a typical example.

  • How do I show something else instead?

    Wrap the division in IFERROR: =IFERROR(B2/C2,"No orders") shows the answer when there is one, and your message when the division fails.

  • Is IF better?

    =IF(C2=0,"No orders",B2/C2) checks for the zero first. It only catches that one problem, so other mistakes (like text in a number column) still show their error. IFERROR hides every error, which is quicker but can hide a real mistake.

  • Should I show 0 instead?

    Only if 0 is true. An average of 0 looks like real data; a message or a blank ("") makes it clear there is nothing to show yet.

Know someone fighting with a spreadsheet? Send them this page.