Hiya,
Apologies for the poor heading!!
I need to use some CF. I want to put a bottm border on a range if a cell in the range is not empty.
I don't use cf all that much so am looking at web sites to try and get my head around it.
On page.. https://techcommunity.microsoft.com/t5/ ... -p/1005636
There is a formula.. =($D1<OFFSET($D1,-1,1))*(ROW()>5)
Is anyone willing to try and talk me through that please?
TIA
Lisa
Conditional formatting
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional formatting
Keep in mind that a conditional formatting rule is applied when the condition is TRUE or any number other than 0.
The formula =($D1<OFFSET($D1,-1,1))*(ROW()>5) combines two conditions. Each of them evaluates to TRUE or FALSE.
If you multiply them, Excel treats TRUE as 1 and FALSE as 0. The only situation in which the product is non-zero is if both conditions are TRUE:
TRUE*TRUE = 1
TRUE*FALSE = 0
FALSE*TRUE = 0
FALSE*FALSE = 0
The first condition is $D1<OFFSET($D1,-1,1). Note that there is a $ before the column letter D, so that is absolute (fixed). But there is no $ before the row number 1, so that is relative (it will change to 2, 3, 4 etc. in the rows below).
OFFSET($D1,-1,1) is the cell one row above and one column to the right of $D1, i.e. the cell in column E in the row above. In row 1, that evaluates to an error, so the condition is not TRUE, but in the rows below, the D2 will be compared to E1, D3 will be compared to E2 etc.
The second condition is ROW()>5. This is FALSE for rows 1 to 5, and TRUE for row 6 and below.
If we combine this, the condition will always be false in rows 1 to 5.
In row 6 and below, the condition will be satisfied if the cell in column D is less than the cell in column E one row up.
Does that help?
The formula =($D1<OFFSET($D1,-1,1))*(ROW()>5) combines two conditions. Each of them evaluates to TRUE or FALSE.
If you multiply them, Excel treats TRUE as 1 and FALSE as 0. The only situation in which the product is non-zero is if both conditions are TRUE:
TRUE*TRUE = 1
TRUE*FALSE = 0
FALSE*TRUE = 0
FALSE*FALSE = 0
The first condition is $D1<OFFSET($D1,-1,1). Note that there is a $ before the column letter D, so that is absolute (fixed). But there is no $ before the row number 1, so that is relative (it will change to 2, 3, 4 etc. in the rows below).
OFFSET($D1,-1,1) is the cell one row above and one column to the right of $D1, i.e. the cell in column E in the row above. In row 1, that evaluates to an error, so the condition is not TRUE, but in the rows below, the D2 will be compared to E1, D3 will be compared to E2 etc.
The second condition is ROW()>5. This is FALSE for rows 1 to 5, and TRUE for row 6 and below.
If we combine this, the condition will always be false in rows 1 to 5.
In row 6 and below, the condition will be satisfied if the cell in column D is less than the cell in column E one row up.
Does that help?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: Conditional formatting
Oh yes it helps tremendously!!
Thank you Hans!!!
Lisa
Thank you Hans!!!
Lisa