Conditional Formatting some cells contain =IFERROR(VLOOKUP.....

[sshea]

I have a 2010 spreadsheet that uses VLOOKUP to pull $ amounts in multiple catagories. Each catagory is a column. Each row is the store being charged. The end of each row is the total cost charged to the store and the end of each column is an average cost of that catagory.


I have used the =IFERROR(VLOOKUP(.......), "") so that rows are added properly w/o returning #N/A in case a cell doesn't have info to pull for that compination of store and catagory.


My problem now is that I want to use conditional formating in the columns to highlight the cells that are above average. I can't use the automatic "highlight cells >" rule because my average formula at the bottom only includes cells >1. But when i make my own conditional format >C57 it also highlights my "blank" cells because they have text striings in them.


Any ideas on how to do all of the above but have the "blank" cells not change color??

 

[Luke M]

Make the CF formula an AND formula, like:

=AND(ISNUMBER(C2),C2>$C$57)

 

[sshea]

Wow, not sure how this works but it does! thanks!

 

[Luke M]

You're welcome.


How it works:

The AND function checks all arguements, and returns TRUE only if all of the individual arguments are true. So, the first arguement is:

ISNUMBER(C2)

This will help us sort out the "" cells. If they are "", this arguement evaluates to FALSE, and will in turn cause the AND function to evaluate to FALSE.

The 2nd argument is:

C2>$C$57

This just checks to see if the cell is greater than the average, stored in cell C57. This obviously highlights the cells of interest, but as you already saw, would normally also flag the "" cells.


Again, only when both conditions are TRUE will the AND function evaluate to TRUE.