Formula for % change in a value. False negatives

[Maverick21]

Hey guys, I'm new here and have come up with another excel problem / brain fart. Well I had the formula worked out I thought. Only problem is some of the old values are negative, therefore returning false negative %'s.

current formula: (Current Net Income - Old Net Income) / Old Net income = % change (example attached)

Problem is, when old is negative and new is not it gives a false negative % change. Maybe an if() statement for negative values to =1? I would still like the end result to be as accurate as possible.

Thanks for any help available

[TMS]

Is it not:

=(D10-B10)/ABS(B10) = 200%

On the basis that -500 to 0 would be 100% and 0 to 500 would be another 100%


Regards, TMS

[Fidd$]

Hi Maverick,

Not quite sure how you would have a negative value for your old net income? You also have a #div/0 error because you can't divide by 0 or a blank. The negative percentage value will not be false as that's what you are asking from the formula. eg. you have -500 as old income, 500 as new income. Based on your formula then, there is indeed a 200% DROP in income, hence a NEGATIVE percentage in the form of "-200%".

[Maverick21]

Is it not:

=(D10-B10)/ABS(B10) = 200%

On the basis that -500 to 0 would be 100% and 0 to 500 would be another 100%


Regards, TMS

With that formula there is an inverse relationship to the old value and the %change value when the old value is negative. It does get rid of the negative end value though but not quite what I need. I need to go back to my calc class .....

[Maverick21]

Hi Maverick,

Not quite sure how you would have a negative value for your old net income? You also have a #div/0 error because you can't divide by 0 or a blank. The negative percentage value will not be false as that's what you are asking from the formula. eg. you have -500 as old income, 500 as new income. Based on your formula then, there is indeed a 200% DROP in income, hence a NEGATIVE percentage in the form of "-200%".

I guess I should have said Net Loss. But given the actual spreadsheet I'm dealing with has a column for "Net Income" there are negatives where the assets reported a loss. This is not important to the issue at hand though as I'm really just worried about the formula, not semantics.

Yes I realize that the formula is doing what I told it to. I'm saying the formula I'm using is the wrong one. Not that the formula isn't calculating correctly. These were all example of things that I'm running into that I cannot report in the final spreadsheet I must submit.

[Maverick21]

Well I think I got part of it. Need to tinker with it a bit though

=(IF(B10<0.0001,D10-B10,D10)-(IF(B10<0.0001,1,B10)))/(IF(B10<0.0001,1,B10))

Thanks for the help guys and helping me figure out where I was going wrong. I'm glad there are forums dedicated to helping schmoes like me out with these issues.