AND is not working with conditional formatting

Hello All,

I am trying to do some conditional formatting based on multiple conditions. I need to quickly see if students are on level based on their grade level and placement score. However their "placement" is also conditional to their grade level. i.e. if a 1st and 2nd grader get the same score on the placement test they will be placed at different levels. The formula I have been using is =IF(AND($E3=1,$H3>=$N$4),"1Green"). Where Column E is their grade level, Column H is their score on the test, and N4 is the minimum cut off for placement in their current grade (I chose not to put an actual number for for N4 because the cut off scores may change in the future) I know this formula works in the actual sheet because I have tested it on cells matching the criteria (it returns 1Green for true and false for false). However, when I put it in conditional formatting it doesn't work. I have tried to modify it in many different ways to make it work, but I am just not savvy enough. Can someone help me, please?

I have attached a worksheet. It looks a bit weird because I have removed the identifying information for the students, but kept the formulas intact. The table on the right is the range of cut off scores (grade"x" is their grade level when taking the test and the Level"x" is their placement based on their score) There are some blanks where students have not taken the test, I would like those to stay blank. As you will see I have separate formulas for 1st through 12th grade and whether they are on grade or above (green), one level below(yellow) or two or more levels below(red). Eventually I will need all of these to go into conditional formatting, but if I can figure out why it won't work on the previously specified formula, I can apply it to the rest of them myself. I am sure it's something simple I'm not seeing. Thanks in advance for any help!

With conditional formatting the IF is implied - you need to rework the formula as a logical expression which will return TRUE (to trigger the change in format) or FALSE. So, you will select the cells that you want the format to apply to (or you could just select one cell and then use the Format Painter to apply that format to other cells afterwards), and then click on Conditional Formatting | New Rule | Use a Formula... from the bottom of the list, and then use this formula in the dialogue box:

=AND($E3=1,$H3>=$N$4)

Click on the Format button | Fill tab and choose Green, then OK your way out.

Hope this helps.

Pete

Perfect! Thank you!