Conditional Formatting, using an formula to format based on different cell value range

Question

I'm trying to use a conditional format formula but I cannot figure out how to write it correctly. Attached is an image of the data set that I'm working with. Dataset image

Ideally, temperature values in column B will highlight red if they are out of their + or - 0.5 degree range based on which tank they are in. Previously I selected the individual cells that are associated with a specific tank to format based on the range (i.e. cell value not between =$F$4-0.5 and $F$4+0.5 then format red) but it is a pain to individually select each cell when the instrument changes tanks weekly.

Is there any way to tell it to format B6 based on D6 such that it uses the conditional format of "cell value not between =$F$4-0.5 and $F$4+0.5 then format red"?

So you want to use either F4 or E4 depending on the value in column E? — jblood94
– jblood94, Commented May 24, 2018 at 19:25
I'd like to use either F4 or E4 based on the value in column D (the tank). — Fish.smith
– Fish.smith, Commented Jun 7, 2018 at 13:41

jblood94 · Accepted Answer · 2018-06-07 17:40:40Z

1

Use this formula for the format rule:

=ABS(B6-IF(E6=$E$3,$E$4,$F$4))>0.5

It will fill down.

EDIT. Explanation: The IF statement will return the temperature in E4:F4 corresponding to the Tank DO in column E. We then check if the absolute value of the difference of that and the DO from column C is within the 0.5° range that you specified. So for B6, the formula will calculate like this:

=ABS(B6-IF(E6=$E$3,$E$4,$F$4))>0.5
=ABS(B6-IF(8.44=8.48,$E$4,$F$4))>0.5
=ABS(B6-IF(FALSE,$E$4,$F$4))>0.5
=ABS(B6-$F$4)>0.5
=ABS(23.81-23.30)>0.5
=ABS(0.51)>0.5
=0.51>0.5
=TRUE --> the conditional formatting will be applied

edited Jun 7, 2018 at 17:40

answered May 24, 2018 at 19:22

jblood94

17.5k2 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ashleedawg · Accepted Answer · 2018-05-26 07:03:00Z

-1

EDIT: Corrected wrong cell reference.

^{Thanks to superstar @PNUT for pointing out my error.}

You could use:

=ABS(B6-$F$4)<=0.5

Whichever one you use (they'll give the same result), make that the conditional formatting for cell B6 and then copy & paste formatting to the rest of the cells in the column.

Note that the placement of the $ (absolute cell reference indicators).

The ABS function returns the absolute value of a number ("the distance from zero", or more simply, "makes a negative number positive") so there is no need for "between", but if you did need to use multiple criteria (as if to mimic "between") you could use the AND function, something like:

=AND(B6-$F$4>=-0.5,B6-$F$4<=0.5)

A good way to test "more complex" conditional formatting criteria is to enter them in a cell. Any formula that returns only TRUE or FALSE will work in conditional formatting. (TRUE = apply formatting)

A simpler demonstration of how formulas with multiple = (equality signs) work is enter these formulas into cells:

=1=1   (this will return TRUE)
=1=2   (this will return FALSE)

...and thus are the equivalent of:

=IF(1=1,TRUE,FALSE)
=IF(1=2,TRUE,FALSE)

More Information:

Office.com : Use formulas with conditional formatting
Office.com : AND Function (Excel)
Office.com : ABS Function (Excel)
TechRepublic : 10 cool ways to use Excel's conditional formatting feature
Office.com : Quick start: Apply conditional formatting (Tutorial)

edited May 26, 2018 at 7:03

answered May 24, 2018 at 19:22

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

1 Comment

ashleedawg Over a year ago

@pnuts corrected cell reference. re: TRUE/FALSE what is an example of a formula that would not work?

Collectives™ on Stack Overflow

Conditional Formatting, using an formula to format based on different cell value range

2 Answers 2

Comments

More Information:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

More Information:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related