Positive/Negative Number Calculation

[AlmostAGeek]

I am having trouble coming up with a formula that doesn't need to be changed each month depending on the positive/negative answer in a row. Below are total lines from an Income Statement. Column B is budget total - Column C is current year actual - Column D is dollars variance (C-B) - Column E is the variance percentage.

If you have spent more than budgeted Column D and E should be be negative answers. If you have spent less - postive answers in D and E. Look what happens though when you have two negative number - row 15 and 17

The formula in Column E is as follows:

=-IF(OR(B12=0,C12=0,D12=0),"",(C12/B12-1))

As you can see row A12 gives a #VALUE! error - it would work fine if I took out the - (negative sign) before the IF statement. If I do that as standard practice, no negative sign in column E, the rest of the numbers are presenting themselves incorrectly. The signs are reversed.

A11          B            C             D              E
A12           0.00      28,664.77     -28,664.77     #value!
A13       1012.56        2152.12       -1,139.56    -112.54%
A14       9594.58        6321.78       3,272.80       34.11%
A15       -578.56       -1975.35        1,396.79    -241.43%
A16      -3818.56      -1478.96       -2,339.60       61.27%
A17      -1872.65      -4375.19        2,502.54     -133.64%
A18      28864.77          0.00       28,864.77      #value!
A19          0.00          0.00           0.00        #value! 

Can anyone shed any light on this problem for me. Any insight would be much appreciated.

D

[Richard Schollar]

Hi

Don't you just need this then instead:

=IF(OR(B12=0,C12=0,D12=0),"",-(C12/B12-1))

Richard

[oldchippy]

Hi AlmostAGeek,

You say Column D is dollars variance (C-B) but your data show it as B-C?

[AlmostAGeek]

Thank you Richard your suggestion. That does take care of the value error, much appreciated.

OldChippy,

THIS IS HOW I WAS GOING TO ANSWER YOU....GLAD I DIDN'T, I'D HAVE MADE A FOOL OF MYSELF:
"Yes, that is correct - you subtract budget dollars (B) from current dollars (C) to come up with the variance amount in column D. So the formula should be =C-B. To get the sign right I did =-C+B. This then made column E going the wrong way to I added a - to it too. I should probably correct myself - this is more a budget comparison than an income statement report."

Boy, just goes to show you that if you think about something too long you make REALLY stupid mistakes.

I just corrected it to show =B-C for column D and corrected the formula in column E per Richards's suggestion and almost looks right!

The only problem now is when column B and/or C is a negative number. The answer is a positive-should be negative.
See example below:
........B.................C..................D................E
...-578.56.......-1,975.35.......1,396.79.......-241.43%
In this we lost more than we thought we would (C) - should be negative D & E.

25,736.00....-101,147.55....126,883.55.......493.02%

The longer I look at this the more confused I become. Is it time to give up??

D