# Conditional Formatting with arrows

#### Kele

##### Board Regular
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))))

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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))

Last edited:
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

Replies
3
Views
331
Replies
1
Views
196
Replies
2
Views
83
Replies
4
Views
454
Replies
5
Views
1K

### Forum statistics

1,207,194
Messages
6,077,006
Members
446,250
Latest member
Dontcomehereoften

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back