Auto Highlighting and problems with double negatives

  • Thread starter Thread starter garungaha1
  • Start date Start date
G

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
 
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)
 
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
 
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?
 
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
 
Back
Top