[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Conditional Format - Compare two cells for 3 results

I have two columns in an excel sheet that show printer counts as recorded by our staff (count) and the second shows the count(s) from the invoice (invcount).

printer serial   |       count |   invcount

I only log something in the invcount if the invoice does not match our count from the panel.
I would like a formula that compares count to invcount and colors the cells depending on the comparison  (keeping the number in tact)
orange = over (greater than than the count column)
blue = under (less than the count column)
no fill = if empty (or zero difference - not logged)

I have headings at the top of the cells. F1-F3,G1-G3
I have tried using conditional formatting with icons but have an issue with no less than selection.  I am also finding it difficult to compare two entire rows without the SUM of each row deciding the value.
It would be nice to compare left to right down the entire columns and not add formatting to each cell that I logged.

Thanks in advance
0
PostQ
Asked:
PostQ
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
Assuming your Count column is B, your Invoice column is C and your data starts at row 2, you can use these two conditional formulas:
For Blue Fill
=AND($B2<>"",$C2<>"", $B2>$C2)

For Orange Fill
=AND($B2<>"",$C2<>"", $B2<$C2)

Set the Applies to for both formulas to include columns A to C: $A:$C
0
 
sentnerCommented:
You just need to use conditional formatting with formulas, with 2 rules.  See attachment.
condformat.xlsx
0
 
PostQAuthor Commented:
I like the simplicity of the conditional formatting.  I will be working with excluding the printer name from being colored.  Just limiting it to the invcount cell.
invcount cell will take the color if above or below the value in the count cell.
Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now