Conditional Formatting with arrows

[Kele]

I have a spreadsheet that has people who are classed as Platinum (P), Gold(G), Silver(S), and Bronze(B) and I want to use conditional formatting arrows to show when people are getting to the top or bottom criteria of their groups I have a former that works but I can get this to work in conditional formatting can any one help?

My formulas are;

=IF(N2="P",IF(J2>200000,1,0),IF(N2="G",IF(J2>18000,1,0),IF(N2="S",IF(J2>1800,1,0),IF(N2="B",IF(J2>80,1,0),0))))

=IF(N2="P",IF(J2>23000,1,0),IF(N2="G",IF(J2>2000,1,0),IF(N2="S",IF(J2>100,1,0),IF(N2="B",IF(J2>50,1,0),0))))

 

[nbrcrunch]

Kele, instead of IF statements, Conditional formatting logic is written in True/False syntax.

The stmt must resolve to "TRUE" for the condition to light up the cell or change the color of a font, or whatever else you choose.

Taking just the first part of your formula:
=IF(N2="P",IF(J2>200000,1,0),IF(N2="G",IF(J2>18000,1,0),IF(N2="S",IF(J2>1800,1,0),IF(N2="B",IF(J2>80,1,0),0))))

partially translates to...
=and(N2="P",J2>200000)

In the above case, both conditions (N2="P" AND J2 > 200000) must be met for the condition to be true.

You can do more complicated AND/OR combinations. Say for example you need to set a condition in cases where Mares eat oats and Little Lambs eat oats or ivy. In Conditional Formatting terms:

=AND(Mares Eat Oats, OR(Lambs eat oats, Lambs eat Ivy))

 

[Kele]

thanks for this i posted it then had to go away on business so only just got to read it i will give the "AND" statement i go