MoonPoint Support Logo

 


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



Advanced Search
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")

[/os/windows/office/excel] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo