Need Positive Number When Calculating from Two Cells...


Posted by Brian Sanburn on October 10, 2001 10:14 AM

Hello,

I'm a definite Excel novice so hopefully I can explain my situation clearly. I have started producing a spreadsheet which shows sales figures from both this year (F3) and last year (E3). The figure I have (either positive or negative) that represents the dollar difference between the two years' sales numbers (H3) is divided into the total sales number from the earlier year (E3) to give me a percentage difference from last year to this year (I3)--that is H3/E3 (dollar difference/last year sales). In this particular instance the product showed a 19% sales decline from last year to this year. So it appears I've set up the formula correctly. The problem comes when there are no sales of the product from last year. So, as an example, I have a new product that had sales of $10,000 this year but $0.00 last year. The dollar difference is represented correctly ($10,000 over last year) but the formula for a percentage displays "#DIV/0!" Am I doing something incorrectly and is there a way to set up a formula that will correctly show the %10,000 increase in sales from last year to this? Thank you for whatever help someone can offer.

Brian Sanburn

Posted by Aladin Akyurek on October 10, 2001 10:29 AM

In I3 enter: =IF(E3,H3/E3,1). [NT]

Posted by Brian Sanburn on October 10, 2001 11:41 AM

Thanks to Aladin for providing just what I needed....

Where in Excel can I go to find an explanation for this formula and variants to it?

Thanks, again.

Brian

Posted by Aladin Akyurek on October 10, 2001 12:36 PM

Re: Thanks to Aladin for providing just what I needed....

Brian --

=IF(E3,H3/E3,1)

IF has 3 args, the first one is a condition that must evaluate to a logical value, that is, to either TRUE or FALSE. The above formula tests whether E3 contains a number greater than zero. When E3 in fact contains a number grater than zero, we get TRUE. In case E3 contains nothing (that is, it's blank/empty) or a literal zero, we get FALSE.

H3/E3 is evaluated thus when E3>0. [ The 2nd argument of IF ].
The last argument (that is,1 in the above formula) is evaluated (in this case, simply returned) when E3 empty or E3=0.

You might want to look up IF in Excel's Help for additional info.

Aladin



Posted by Brian Sanburn on October 10, 2001 12:59 PM

Re: Thanks to Aladin for providing just what I needed....

Very cool, Aladin.

Thanks again.

Brian