DIV/0! and business application

[sony654]

if a = 5.00 (TY earnings)
if b = 0.00 (LY earnings)
the percent change is (a-b)/b is really a 100% increase, not DIV/0!
and if a=-5.00, then it's a 100% decrease.


=IF(b29=0,100%,(a29-b29)/b29)
..... what if a29 is negative

Thanks

 

[RWN]

if a = 5.00 (TY earnings)

if b = 0.00 (LY earnings)
the percent change is (a-b)/b is really a 100% increase, not DIV/0!

That would mean that;
a-b = 1.00 * b
5-0 = 1.00 *0
5 = 0

Strange math!

 

[Harlan Grove]

if a = 5.00 (TY earnings)
if b = 0.00 (LY earnings)
the percent change is (a-b)/b is really a 100% increase, not DIV/0!
and if a=-5.00, then it's a 100% decrease.

WRONG!

If this year's earnings are 5.0, they'd represent a 100% increase over last
year's earnings if last year's earnings were 2.5.

And a 400% increase if last year's earnings were 1.0.

And a 999900% increase if last year's earnings were 0.0005.

And so on.

If last year's earnings were zero (or negative), there's no mathematically
meaningful percentage change (whether there's a 'liberal arts' percentage
change is irrelevant). That's why financial statements show something like
NMF (for not meaningful) in such situations.

=IF(b29=0,100%,(a29-b29)/b29)
.... what if a29 is negative

There's a meaningless negative percentage change. Signs matter. A negative
percentage change is a GOOD THING if the starting value is negative, a bad
thing when the starting value is positive. This is the problem with
percentages when used by nonmathematicians. Laymen improperly think of
percentages as additive when, in fact, they're multiplicative.

 

[John Mansfield]

To make sure you've got your signs correct, use Excel's ABS (absolute value
function) in the denominator. If you're dividing by zero, you can add an
error trapping statement into the formula to return "N/M", or Not Meaningful.

For example, if you have the number 5 in cell A1 and 3 in cell B1, add this
formula somewhere in the sheet (say C1) to calculate the percent change:

=IF(ISERROR((A1-B1)/ABS(B1)),"N/M",(A1-B1)/ABS(B1))

Harlan is correct in that
(1) the signs very much matter . . you need to make sure they go the right
direction if a negative number is thrown into the mix.
(2) it's improper to try to interpret an percentage change off of a zero
value.