IF cell is positive help

[nymyth]

I have a little problem and was wondering if someone can help me out.

I am working on an Income Statement where I show:

Actuals Budget CM Act vs CM Bud($) CM Act vs CM Bud (%)

The problem is in the final column for the %. The percentage formula says "If ROUND of column C is 1, then give me a 0%, else divide column C and B (C/B). Well this works out great until I have a positive number in column C and a negative in column B, the percentage will come out to be a negative, but it should be positive. For example:

Actuals | Budget | CM Act vs CM Bud($) | CM Act vs CM Bud (%)
22,920 | 24,485 | (1,565) |-6.4%
(2,869) | (3,270) | 401 |-12.3%

That negative 12.3 should actually be a positive. Any help is appreciated. Thank you.

 

[mole999]

I think ABS() will fix the value

 

[schielrn]

Maybe try the following?

Excel Workbook
	A	B	C	D
2	22920	24485	-1565	-6.4%
3	-2869	-3270	401	12.3%
4	-2869	-2200	-669	-30.4%
Sheet4

Cell Formulas
Range	Formula
C2	=A2-B2
D2	=C2/ABS(B2)

Hope that helps.

 

[nymyth]

I think ABS() will fix the value

Dude, thank you for that...did the trick...how on earth did I forget about ABS()...lol

 

[nymyth]

ended up using this:

=IF(ROUND(J24,1)=0,0%,IF(K24>1,ABS(K24/J24),+K24/J24))