Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used Products

September
Sun Mon Tue Wed Thu Fri Sat
21

2013
Months
Sep

Sat, Sep 21, 2013 12:03 pm

Testing a value to prevent a divide by zero error in Excel

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:

ABCD
1 DateItems OrderedItems Shipped % Shipped
2 9/1/1300
3 9/2/1321
4 9/3/1301
5 9/4/1330

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:

ABCD
1 DateItems OrderedItems Shipped % Shipped
2 9/1/1300 #DIV/0!
3 9/2/1321 50.00%
4 9/3/1301 0.00%
5 9/4/1330 #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) ```

ABCD
1 DateItems OrderedItems Shipped % Shipped
2 9/1/1300 0.00%
3 9/2/1321 50.00%
4 9/3/1301 0.00%
5 9/4/1330 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") ```

Shop Amazon Local - Subscribe to Deals in Your Neighborhood