1

I am trying to understand the logic behind how conditional formatting with formulas works.

I just created a formula (by trial and error) that highlights rows whose cell in column B contains a whole number. But I don't really understand how it works.

For this example, the formula is applied to A2 through D60 (omitting a header row):

=INT($B2)=$B2

How does this work exactly? And why, for instance, do I specifically need to use absolute cell references ($) for it to work?

0

1 Answer 1

1

Part 1 - The Formula
Let's say you have the following two cells (B2 and B3, respecitvely):

3
4.3

INT() will round a number down to the nearest integer.

When you run =INT(B2)=B2, it evaluates the first part, INT(B2) which will return simply 3. Then it checks is 3=3? Yes it does, so it returns TRUE for the whole formula.

When you run =INT(B3)=B3, it evaluates INT(4.3) which returns the next whole number when rounding down, in this case 4.

Then it checks 4=4.3 which returns FALSE for the overall formula.

Part 2 - The Referencing
When you have the formula applied to the range A2:D60 (I'm assuming this is the "Applies To" range), you will be running the formula on ALL cells within that range.

If you removed the absolute references in the formula, the formula would evaluate on every cell in your range, regardless of column. in C23 the formula would be =INT(C23)=C23. See below - the formula actually being run in that cell is =INT(C2)=C2. Since 6.5 is not an integer, it returns FALSE and therefore no formatting is applied.

Since you want to check only column B for the integer, you need the absolute reference. That means on cell C23, the formula being evaluated is =INT($B23)=$B23.

The "Applies To" range will then apply whatever formatting you have - so if B23 is 10, then A23:D23 should have your formatting.

1
  • 1
    Thanks for the explanation. The last part still confuses me a bit -- if I have an absolute reference, then for instance in row 23, are you saying every single cell in that row is running the same formula? =INT($B23)=$B23. Also, why wouldn't I just reference $B the column? i.e. Why is it important to put in the first row number ($B2), if that is already being specified by the "Applies To" range?
    – gills
    Nov 24, 2017 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.