Excel Conditional Formatting Unexpected Result

0
votes

The conditional format rule is set as an equation doesn't behave as I expect (highlight only values that are =1 in Column M corresponding to the current row). Instead they are seemingly randomly colored.

My code is as follows: IF($M5=1,TRUE,FALSE) where true results in the color green, false default formatting. This formatting is copied along a column, e.g. I also expect the value M$5 to correspond with the row in the cell with formatting in column M.

I observed that the color changes sometimes when Mrow=1, sometimes not.It appears uncorrelated with the row value. See below.

Item M-column Color

#1. 1 none

#2 0.48 green

#3. 0.0. green

#4 1. green

Any suggestions to understand what to try next to fix the probelm?

Thanks

1
excelexcel-formulaconditional-formatting
Try =LEFT(B1,1)*1=1. – OverflowStacker
@OverflowStacker That works! – seth shill

1 Answers

0
votes

Your formula is offsetting the row by 5, either use =$M1=1 or use the conditional formatting dropdown options to select highlight cells -> Equal to...

Based on the data provided it looks like your raw data has a 1 in rows 8,9,10, which is why it formatted the cells five rows above as green.

Also, you don't need to wrap your logical test in an IF() statement, it will return TRUE or FALSE either way.