Issue with Highlighting Columns that have different values

MelissaYE

I have a sheet where I am tracking values from one month to the next (values imported using DataShuttle). I want to highlight the columns where the values are different, using a helper column:

=IF(Bananas@row = [Previous Bananas]@row, 1, 0)

However, I am having the formulas return 0s when the formulas are NOT DIFFERENT. The numbers look the same in the columns, so I'm assuming it's some sort of issue with rounding and the source files. Not sure how to resolve this.

Cathy Salscheider

In this case Value1 and Value2 are my cells. Enter 9 for Value1 and 200 for Value2. Create a new column for the formula called PercentageTest. Use this formula in PercentageTest cell… and make it a column formula.

=([Value1]@row / [Value2]@row) * 100

Using conditional formatting, for PrecentageTest column, set any value < 10 to green and any value > 10 to red.

In the example, the value in PercentageTest is 4.5 and the cell will be turned green. Does this work?

Cathy Salscheider

In this case Value1 and Value2 are my cells. Enter 9 for Value1 and 200 for Value2. Create a new column for the formula called PercentageTest. Use this formula in PercentageTest cell… and make it a column formula.

=([Value1]@row / [Value2]@row) * 100

Using conditional formatting, for PrecentageTest column, set any value < 10 to green and any value > 10 to red.

In the example, the value in PercentageTest is 4.5 and the cell will be turned green. Does this work?

MelissaYE

Yes, this is really helpful! I just changed the "Banana Differential Column" to

=(Bananas@row / [Previous Bananas]@row * 100)

and updated the conditional format to highlight when the Banana Differential is <90.

HOWEVER, I'm still seeing some issues where the Banana and Previous Banana values are low (0, 1, 2, 3)—both these values appear to be the same, but are being highlighted.

Cathy Salscheider

Could there be some type of rounding going on that is not visible, so that the true values in rows 1 and 2 are not the same.

Also, check your function on Previous Bananas column. Could that be contributing?