Ask Your Question
0

Conditional Formatting using values instead of actual cell contents [closed]

asked 2018-08-22 18:51:46 +0100

rebelstar gravatar image

updated 2018-08-22 19:43:23 +0100

I want to use conditional formatting to color the cell backgrounds of 2 cells. Green when the values match and red when they do not match. The values in both cells are derived from formulas, but even when the values match, the conditional formatting displays the backgrounds as red. How can I make conditional formatting utilize the cells' values as opposed to the cells' formulas, which is what I believe they're using when checking for a match? C:\fakepath\Sample.ods

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-08-23 12:40:27.259619

Comments

Please share a minimal sample file to test.

m.a.riosv gravatar imagem.a.riosv ( 2018-08-22 19:01:53 +0100 )edit

I've attached the sample (I think) above. The cells in question are on Sheet 2, cells B1 and C1.

rebelstar gravatar imagerebelstar ( 2018-08-22 19:43:37 +0100 )edit

1 Answer

Sort by » oldest newest most voted
3

answered 2018-08-22 20:38:39 +0100

JohnSUN gravatar image

You don't get the TRUE value that you expect because these values are really different. Increase the number of digits displayed after the decimal point and you will see this with your own eyes.

As a workaround, you can use the ROUND() function, which will allow you to compare coarser values.

ConditionFormat.png

edit flag offensive delete link more

Comments

Brilliant! Thank you. I was not aware of the ROUND function. I appreciate the help.

rebelstar gravatar imagerebelstar ( 2018-08-22 21:02:19 +0100 )edit

For @rebelstar There was sample worksheet by @Lupp attached in this thread How do I test cell for a whole integer, showing how untrustable (by means of precison) decimal calculations could be. Even 24.24 - 24.00 gives you result 0.239999999999998, not 0.24 as you would expect.

SM_Riga gravatar imageSM_Riga ( 2018-08-22 21:10:11 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2018-08-22 18:51:46 +0100

Seen: 35 times

Last updated: Aug 22 '18