Excel IF formula, criteria negative percentage values

[kevvyb]

Hi

I am using this formula on a 'helper column' (G) to try to produce 3 values, G, R and A on which I want to apply conditional formatting traffic light icons.

All would be well if one of the criteia in my formula was not a negative percentage value: =IF(H32>=15%,"G",IF(H32<=15%,"A",IF(H32<-15%),"R","")))
It works apart from the last nested IF statement which does not seem to be recognised.

The cells with the values it is using are generated from this formula to produce percentages: =IFERROR((E32-C32)/C32,"0.00%")*-1
So it too has a negative but I have tried all sorts of permuations in case that was it.

Is what I am trying to do impossible or is there another way to do it?

Than ks in advance for any help.

 

[Fluff]

How about

=IF(H32>=15%,"G",IF(H32<-15%,"R","A"))

 

[Joe4]

The order of your IF clauses is not correct. Since -15% is also less than 15%, it will hit the second one and never get to the third.

There are a number of ways of structuring these.
1. If only 3 conditions, check the two ends, and then everything left in the middle would be your third (like the formula Fluff posted).
2. Go in ascending or descending order (i.e. If more than your biggest value, if more than your second largest value, etc. or If less than your smallest value, if less than your second smallest value, etc).
3. Use and "AND" clause to define and check the limits of your middle intervals, i.e. If more than your biggest value, if more than your second biggest value AND less than your biggest value, etc).

If you define the conditions in ascending/descending order, then you do not need to mess around with AND clauses, because Excel will stop evaluating the formula once the first true condition is met.