Excel's IF function can be used to prevent divide by zero errors, which show up as #DIV/0! values in cells, in calculations. The format for the IF function is:
IF(logical_test, [value_if_true], [value_if_false])
E.g., suppose column B holds values for items ordered on particular days and column C holds values for items shipped on those days, as shown below:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Items Ordered | Items Shipped | % Shipped |
| 2 | 9/1/13 | 0 | 0 | |
| 3 | 9/2/13 | 2 | 1 | |
| 4 | 9/3/13 | 0 | 1 | |
| 5 | 9/4/13 | 3 | 0 |
If column C is simply divided by column B, e.g. =C2/B2, to get the percentage of orders shipped, the results would be as follows:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Items Ordered | Items Shipped | % Shipped |
| 2 | 9/1/13 | 0 | 0 | #DIV/0! |
| 3 | 9/2/13 | 2 | 1 | 50.00% |
| 4 | 9/3/13 | 0 | 1 | 0.00% |
| 5 | 9/4/13 | 3 | 0 | #DIV/0! |
A simple IF test can be used so that if a cell in column B is zero, then a zero value is put in the "% shipped column".
=IF(B2>0, C2/B2, 0)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Items Ordered | Items Shipped | % Shipped |
| 2 | 9/1/13 | 0 | 0 | 0.00% |
| 3 | 9/2/13 | 2 | 1 | 50.00% |
| 4 | 9/3/13 | 0 | 1 | 0.00% |
| 5 | 9/4/13 | 3 | 0 | 0.00% |
Or if no items were ordered on a particular day, "N/A", for "Not Applicable" could be used instead of zero, if preferred.
=IF(B2>0, C2/B2, "N/A")
