Auto Highlighting and problems with double negatives

[garungaha1]

Hey everyone,

I am having some problems with double negatives is excel that causes m
conditional formatting to be wrong. The formatting works for all of m
needs including +/-, +/+, and -/+, but when I have -/-, then it come
out positive but being in the financial world 2 negatives is not such
good thing and even worse when the percentage comes outs +%. So m
question is is there a way to change the cell formula so that it come
out Negative (without throwing in a - and the formula being wrong i
all 3 other instances)? Or the other thing is I have conditiona
formatting for one cell, is there a formula to say if cell B6 i
highlighted due to conditional formatting can C6 be highlited the sam
way? Thanks

 

[Frank Kabel]

Hi
you may post your existing formula

 

[garungaha1]

Well here is the situation and formula: The main goal is to get F6 re
due to conditional formatting. C6 is a negative number, D6 is
negative number. E6=C6-D6 (which in turn makes it highlighted re
because conditional formatting is E6>0 then highlight red). F6 has th
same conditional formatting as E6. The formula for F6 is F6=E6/D6. Th
problem is 2 negatives makes it positive which in turn is green. I nee
it to be negative and red(with the same conditional formatting)

 

[Frank Kabel]

Hi
and why not use the formula
E6=C6+D6
?

 

[garungaha1]

Because its the difference between the 2 that I need. C6 is the actua
data for the month and D6 is the planned data for the month so I need
difference, to see how far above or below we planned. So E is th
difference between the two and F is the difference divided by th
planned to know on a % basis how far it varied

 

[Frank Kabel]

Hi
but then your formula is correct. e.g.
C6: -5
D6: -10

C6-D6 = +5
and that seems to be correct for me as your revenues were +5 better
than expected. The only problem for you may be that you can't calculate
a percentage for this kind of increase (there's no percentage value
which would reflect this kind of increase). Or what percentage would be
your expected result for the above example?

 

[garungaha1]

Here is the actual data, maybe this makes it easier.

Actual (C6)= -7918
Plan (D6)= -1422
Variance (E6=C6-D6)= -6496 (Conditionally formatted to be highlighte
red (bad) when less than 0 or green (good) greater than 0)
Variance % (F6=E6/D6)= +456.8%(conditionally formatted the same as E6)

so the problem lies that the cell is highlighing F6 to be green or
good and positive 456.8% variance, but in actuality, its really no
good at all that the actual is less than the planned, but because o
double negatives it appears to be good