Home |
Search |
Today's Posts |
#1
|
|||
|
|||
DIV/0! and business application
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 -- Sony Luvy |
#2
|
|||
|
|||
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! -- Regards; Rob ------------------------------------------------------------------------ |
#3
|
|||
|
|||
"sony654" wrote...
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. |
#4
|
|||
|
|||
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. ---------------------------- Regards, John Mansfield http://www.pdbook.com "sony654" wrote: 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 -- Sony Luvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
Where can I find a 2006 Business Calendar template? | Charts and Charting in Excel | |||
record business mileage | New Users to Excel | |||
How would I change a date cell to decrease it by business days? | Excel Discussion (Misc queries) | |||
Formula to make Excel count business days/skip weekends? | Excel Worksheet Functions |