Need help with Conditional formatting

anilkrag1984

Hello Experts,

Need your help with one issue which I am trying to resolve, I have a data in which I applied the Conditional formatting based on the helper column as below -

1) If Current Coverage CN (M) = Previous Coverage CN (M) it will result N

2) If Current Coverage CN (M) > Previous Coverage CN (M) it will result T

3) If Current Coverage CN (M) < Previous Coverage CN (M) it will result F

The colours for the above conditions I have set in the conditional formatting is Red for F, Green for T and Yellow for N.

But here the strange thing is conditional formatting is working for some cells and for some cells it's not working for example -

Row no. 5 Previous Coverage CN(M) = Current Coverage CN(M) but it is showing green colour ideally it should show yellow.

Same way Row No.1 Current Coverage CC% = Previous Coverage CC% which is 29% but it is showing Green colour in Current Coverage CC % whereas below cell in the same column that is row no.2 is equal to Previous Coverage CC% that is 42% and it showing correct result as yellow.

Please refer the attachment.

The formula for helper I have used as below.

=IF([Current Coverage CN(M)]@row = [Previous Coverage CN (M)]@row, "N", IF([Current Coverage CN(M)]@row > [Previous Coverage CN (M)]@row, "T", "F")).

Can anyone suggest what is the issue here?

Issue 1.png

anilkrag1984

Adding to the above issue, just tested the helper condition wherein I applied the formula as below to match Current Coverage CC% to Previous Coverage CC% which 29% and this is resulting F instead of N which means 29% in both the cell doesn't match.

=IF([Current Coverage CC%]@row = [Previous Coverage CC%]@row, "N", "F")

Please note the value updated in the sheet are pulled from other sheet through sumifs.

Attached the screenshot for your reference.

Please note - The cell below 29% in Column Current Coverage CC% is working fine but there is some issue with cell value 29%, Also please Current Coverage CC(M) Cell no.4 is also not matching with Previous Coverage CC(M) cell no. which is $15.

Attached the screenshot for your reference.

Issue2.PNG

Issue 1.png

Paul Newcome

Try adjusting the number of decimals shown. It may be that one is $23.01 and another is $23. Without decimals it would look like they are equal, but the data stored on the backend is showing that they are not.

anilkrag1984

Hi Paul, Thank you for your response checked the data you are correct data stored at the backend is 28.8 and 28.3. Tried adjusting the decimals 0.00<- but not working can you please advise?

Paul Newcome

You would need to incorporate a ROUND/ROUNDDOWN/ROUNDUP (your choice) function in your formula.

=IF(ROUNDDOWN([Column Name]@row) = ROUNDDOWN([Column Name]@row), "Yellow", IF(...........................