Solved

# Conditional Format - Compare two cells for 3 results

Posted on 2014-04-15
317 Views
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.

0
Question by:PostQ

LVL 25

Assisted Solution

Shaun Kline earned 150 total points
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

LVL 14

Accepted Solution

sentner earned 350 total points
You just need to use conditional formatting with formulas, with 2 rules.  See attachment.
condformat.xlsx
0

LVL 2

Author Closing Comment

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

### Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.