Conditional Formatting

[Tam12]

Hi,

I have set up a conditional formatting to do a check, if the values match to display a tick, if not to display a cross.

As you can see the values match, however it is displaying a cross.

The formula is:

=IF(K4=K5,CHAR(252),CHAR(251))

Thanks!

 

[Joe4]

Welcome to the Board!

I suspect a rounding issue. Try using:

ROUND(K4,2)=ROUND(K5,2)

instead to weed out that issue.

 

[awoohaw]

I have 365 and I can't seem to select the Webdings font in the conditional formatting set up dialog to change the font.

If you are really using Conditional Formatting, your result needs to evaluate to TRUE or FALSE, so having CHAR(252) or CHAR(251) in a CF formula will not give you the desired results. (This is my guess why @Joe4 did not give you an IF() function with the rounding suggestion.). I'm not sure if a text value of Char(252) or Char(251) resolves to TRUE or FALSE, but you'll always get the same one of the two in any CF evaluation.

 

[Tam12]

Welcome to the Board!

I suspect a rounding issue. Try using:

ROUND(K4,2)=ROUND(K5,2)

instead to weed out that issue.

Hi,

Thanks for your welcome and your reply!

Unfortunately, this did not work. And just as an fyi, the numbers it is checking against are formulated. I checked all the formulas and the figures it is pulling from do not have multiple decimals. Just the -3415.54 displayed...

 

[Tam12]

I have 365 and I can't seem to select the Webdings font in the conditional formatting set up dialog to change the font.

If you are really using Conditional Formatting, your result needs to evaluate to TRUE or FALSE, so having CHAR(252) or CHAR(251) in a CF formula will not give you the desired results. (This is my guess why @Joe4 did not give you an IF() function with the rounding suggestion.). I'm not sure if a text value of Char(252) or Char(251) resolves to TRUE or FALSE, but you'll always get the same one of the two in any CF evaluation.

The font I am using is 'Wingdings'

I have used conditional formatting without having TRUE or FALSE before and achieved the desired results...

 

[Joe4]

=IF(K4=K5,CHAR(252),CHAR(251))

Is a valid spreadsheet formula, but NOT a Conditional Formatting formula.
As awoohaw said, Conditional Formatting formulas must return TRUE or FALSE.
So, if you wanted to return two different format options, depending on the result, you would need two different rules formulas:

Rule 1 (equal):

=ROUND(K4,2)=ROUND(K5,2)

and then choose your desired formatting option

Rule 2 (not equal):

=ROUND(K4,2)<>ROUND(K5,2)

and then choose your desired formatting option

Also note:

And just as an fyi, the numbers it is checking against are formulated. I checked all the formulas and the figures it is pulling from do not have multiple decimals. Just the -3415.54 displayed...

That does not matter, you could STILL have issues, as explained here: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps
Adding the ROUND function ensures that this won't be an issue (and even if it is not needed in this case, it doesn't hurt anything).

 

[awoohaw]

Just the -3415.54 displayed...

Displayed does not mean what the value that is stored in the cell is.
Change the cell formatting you have in the 2 cells to general with all the decimal spaces you can, and you may eventually see a difference.

 

[Tam12]

=IF(K4=K5,CHAR(252),CHAR(251))

Is a valid spreadsheet formula, but NOT a Conditional Formatting formula.
As awoohaw said, Conditional Formatting formulas must return TRUE or FALSE.
So, if you wanted to return two different format options, depending on the result, you would need two different rules formulas:

Rule 1 (equal):

=ROUND(K4,2)=ROUND(K5,2)

and then choose your desired formatting option

Rule 2 (not equal):

=ROUND(K4,2)<>ROUND(K5,2)

and then choose your desired formatting option

Also note:

That does not matter, you could STILL have issues, as explained here: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps
Adding the ROUND function ensures that this won't be an issue (and even if it is not needed in this case, it doesn't hurt anything).

Makes sense,

All working now thank you!

 

[Tam12]

Displayed does not mean what the value that is stored in the cell is.
Change the cell formatting you have in the 2 cells to general with all the decimal spaces you can, and you may eventually see a difference.

Understood,

Thanks for your help!

 

[Joe4]

You are welcome.
Glad we were able to help!