Conditional Formatting and Negative Numbers!

[excellearner18]

Hi!

I have what I think should be very simple, but is not working for me right now!

I am working on a scorecard sheet. So, we have a number of metrics, and for each we have Target and Goal for every month. Then, for each metric, there is a calculation of the percentage-where are they in relation to the goal? This is a scorecard, so 90% and above is Green, Between 50% and 89% is yellow, and 49% and below is red. All very simple, and we have used conditional formatting to ensure that the cell containing the percentage sum is coloured appropriately automatically.

However, for some of the measures the target, and thus the actual (sometimes), is a negative number, and the conditional formatting doesn't seem to work with negative percentages, as we want it to work with both positive and negative. To set the conditional formatting, we wrote the target percentages in other cells and referenced them in the conditional formatting box ("equal to L13" for example,). For those with negative options, we have set two rules for each (one linked to a cell showing the "RED" positive value, and one linked to the cell showing the "RED" negative value etc, for green and yellow). I have tried "stop if true" for the positive values, but still, whenever the percentage is negative, it only shows up red. Can anyone help please?

Thanks so much!

 

[63falcondude]

Have you tried incorporating the ABS function into your CF formula? For example, =ABS(A2)>=.9 would return true for all values greater than or equal to .9 and all values less than or equal to -.9

 

[63falcondude]

Excel 2010
	A	B	C
1	Data
2	0.5	-0.6	0.49
3	-0.9	0.98	-0.48
4
5	Green Formula
6	FALSE	FALSE	FALSE
7	TRUE	TRUE	FALSE
8
9	Yellow Formula
10	TRUE	TRUE	FALSE
11	FALSE	FALSE	FALSE
12
13	Red Formula
14	FALSE	FALSE	TRUE
15	FALSE	FALSE	TRUE
Sheet8

Green Formula =ABS(A2)>=0.9
Yellow Formula =AND(ABS(A2)>=0.5,ABS(A2)<0.9)
Red Formula =ABS(A2)<0.5

 

[excellearner18]

Thanks so much-so I have tried this, though am still failing at excel!!

For each item, the percentage changes-I used the 0.5 etc as an example for my description, so for each metric, we have been basing the conditional formatting on if X= (contents of cell showing percentage that makes it red)-then colour it red, or if x=between (cell containing yellow value) and (cell containing red value), then colour it yellow etc etc

I tried to add in ABS before these, and it didn't change the response-everything is red still-I am tearing my hair out, I am sure there is a simple solution?! Or that, you just gave me a great one and i am too silly to realise it?!

Thanks so much!

 

[63falcondude]

Can you copy and paste a few rows and columns of your data or create a small example that I can go off of?