Hello everyone,
I can't figure out how to accomplish following task:
I would like to use conditional formatting to color certain cells based on how big is the difference when comparing two cells above each other.
Looking at the example below, Row 2 is the starting value and Row 3 is one that I would like to highlight certain cells based on a difference in comparison to Row 2 ,only comparing cells that are above each other in each column. Continuing down, Row 4 would be compared to Row 3 and so on as I'm adding more rows when new period begins.
Scenario1:
I would highlight only negative trend, which would be value going up in Column A, from example below it would be cell A3 being higher than A2, so A3 should be light shade of red as the difference is not big, cell A4 would remain without highlight as we want this number to go down.
Column B we want this number to be more negative, in example below B3 is less negative in comparison to B2, therefore it should be light red, B4 in comparison to B3 is even more significant, therefore it should be darker shade of red.
Column C and D is the same, just another period.
Scenario2:
Same as the above example with addition to add green highlight also to positive trend, meaning lower positive number when comparing cells in Column A and higher negative number in Column B.
This would be the result for Scenario2:
Thanks a lot for your input!
I can't figure out how to accomplish following task:
I would like to use conditional formatting to color certain cells based on how big is the difference when comparing two cells above each other.
Looking at the example below, Row 2 is the starting value and Row 3 is one that I would like to highlight certain cells based on a difference in comparison to Row 2 ,only comparing cells that are above each other in each column. Continuing down, Row 4 would be compared to Row 3 and so on as I'm adding more rows when new period begins.
Scenario1:
I would highlight only negative trend, which would be value going up in Column A, from example below it would be cell A3 being higher than A2, so A3 should be light shade of red as the difference is not big, cell A4 would remain without highlight as we want this number to go down.
Column B we want this number to be more negative, in example below B3 is less negative in comparison to B2, therefore it should be light red, B4 in comparison to B3 is even more significant, therefore it should be darker shade of red.
Column C and D is the same, just another period.
Scenario2:
Same as the above example with addition to add green highlight also to positive trend, meaning lower positive number when comparing cells in Column A and higher negative number in Column B.
| testcase_.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | 4.9. - 1.10. | 2.10. - 29.10. | 30.10. - 26.11. | 27.11. - 24.12. | ||||||
| 2 | 1,936,474 | -3,720,704 | 1,250,217 | -3,007,572 | 1,046,469 | -2,703,316 | 631,320 | -1,551,938 | ||
| 3 | 2,004,340 | -3,487,924 | 1,467,568 | -3,065,293 | 858,564 | -2,738,814 | 752,123 | -1,541,196 | ||
| 4 | 1,830,979 | -2,941,575 | 1,404,738 | -2,436,402 | 898,630 | -2,951,639 | 724,023 | -1,349,222 | ||
List1 | ||||||||||
This would be the result for Scenario2:
| testcase_.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | 4.9. - 1.10. | 2.10. - 29.10. | 30.10. - 26.11. | 27.11. - 24.12. | ||||||
| 2 | 1,936,474 | -3,720,704 | 1,250,217 | -3,007,572 | 1,046,469 | -2,703,316 | 631,320 | -1,551,938 | ||
| 3 | 2,004,340 | -3,487,924 | 1,467,568 | -3,065,293 | 858,564 | -2,738,814 | 752,123 | -1,541,196 | ||
| 4 | 1,830,979 | -2,941,575 | 1,404,738 | -2,436,402 | 898,630 | -2,951,639 | 724,023 | -1,349,222 | ||
List1 | ||||||||||
Thanks a lot for your input!
