'How do I conditional format based on the set of cells above the cell in question?
I am trying to implement a system that tracks progress with building and improving on a habit. I would like a visual representation on how that is going, and am using conditional formatting to achieve this.
As it currently is:
- Pale green
=b2=b1(Same as yesturday) - Green
=b2>b1(Better than yesturday) - Red
=b2<b1(Worse than yesturday)
I would like an Orange colour to show when the number is better than yesturday but not better than the previous best. The problem with my current condition is it looks at the entire range, rather than just what is above it.
=and(B2>B1, (B2<max(b1:B100)))
This needs to become somthing like:
=and(B2>B1, (B2<max(B1:B[CURRENT CELL NUMBER])))
So how do I get this 'current cell number'?
Example of what I'm looking for:
Solution 1:[1]
You just have to anchor the top of the range and the current cell will adjust by relative addressing:
=and(B2>B1, B2<max(B$1:B1))
I have filled columns C to F in to check that the formulas are working correctly. Note that the Orange rule has to fire before the Green rule. I haven't formatted B1 because there is nothing to compare it to.
Solution 2:[2]
top to bottom:
=INDEX(COUNTIFS(B:B, B1, ROW(B:B), "<="&ROW(B1)))=1
=(B1=MAX(B$1:B1))
=(B1>B2)*(B2)
=(B1<>"")
note the offset for red
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tom Sharpe |
| Solution 2 | player0 |