Issue with Highlighting Columns that have different values
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.
Best Answer
-
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?
Answers
-
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?
-
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.
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!