You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Conditional formatting help required
- Thread starter ksnexcel
- Start date
NARAYANK991
Excel Ninja
Hi ,
See the attached file.
Narayan
Hi, Thanks. I am looking for a more generic solution. Thats why I put +10 in the file I attached. Then the formula becomes two bigger, if I to use OR. I am still trying based on array formula.
NARAYANK991
Excel Ninja
Hi ,
I have not understood why the formula will become bigger if you introduce more elements in the two columns.
Can you upload a workbook which has many more rows of data , say a 1000 ?
Narayan
I have not understood why the formula will become bigger if you introduce more elements in the two columns.
Can you upload a workbook which has many more rows of data , say a 1000 ?
Narayan
NARAYANK991
Excel Ninja
Hi ,No the value can be not just between -5 to +5. It can be -10 to +10. So the formula would become too big. And in fact, later if we want to extend up +/-25, then it is a problem.
Please go through the formulae in the helper columns , and then comment.
Narayan
NARAYANK991
Excel Ninja
Hi,
Thanks for your help. I am able to use it. I have one more question to it.
The Formula in E3 column (in the attachment)
Since I am generating the sheet programatically, I may not have the luxury to drag the formula in such a way the formula gets copied with the range like $B4:$B$28, $B5:$B$28,$B6:$B$28 and so on $B28:$B$28.
How do I use indirect to get it correctly?
Is the above formula correct? Also I my formula can change the column as well, how can i express it generically?
Thanks for your help. I am able to use it. I have one more question to it.
The Formula in E3 column (in the attachment)
=IF(ISERROR(MATCH(TRUE, $B3:$B$28 < 0, 0)), 0, IF((ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0)))+ 1) >= (MATCH(TRUE, $B3:$B$28 < 0, 0)), ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0))), 0))
Since I am generating the sheet programatically, I may not have the luxury to drag the formula in such a way the formula gets copied with the range like $B4:$B$28, $B5:$B$28,$B6:$B$28 and so on $B28:$B$28.
How do I use indirect to get it correctly?
MATCH(TRUE, INDIRECT("$B" & ROW() & ":$B$28") < 0, 0)
Is the above formula correct? Also I my formula can change the column as well, how can i express it generically?
NARAYANK991
Excel Ninja
Hi ,
You will have to use the following formula :
=MATCH(TRUE, INDEX(INDIRECT("$B" & ROW() & ":$B$28"),0) < 0, 0)
This is an array formula , and will have to be entered using CTRL SHIFT ENTER.
Narayan
You will have to use the following formula :
=MATCH(TRUE, INDEX(INDIRECT("$B" & ROW() & ":$B$28"),0) < 0, 0)
This is an array formula , and will have to be entered using CTRL SHIFT ENTER.
Narayan
NARAYANK991
Excel Ninja
NARAYANK991
Excel Ninja
Hi ,
The formula will then become :
=IF(ISERROR(MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)), 0, IF((ABS(INDEX(INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0), MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)))+ 1) >= (MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)), ABS(INDEX(INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0), MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0))), 0))
The range has its bottom cell fixed , which is why I have used 28 for the row number. Its column is also fixed , which is why I have used $B:$B.
Narayan
The formula will then become :
=IF(ISERROR(MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)), 0, IF((ABS(INDEX(INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0), MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)))+ 1) >= (MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0)), ABS(INDEX(INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0), MATCH(TRUE, INDEX(INDIRECT(ADDRESS(ROW(), COLUMN($B:$B), 3) & ":" & ADDRESS(28, COLUMN($B:$B), 1)),0) < 0, 0))), 0))
The range has its bottom cell fixed , which is why I have used 28 for the row number. Its column is also fixed , which is why I have used $B:$B.
Narayan
neeraj2050
Member
Hi,
Would you be kind enough to brief about the following formula highlighted with Pink colour
=IF(ISERROR(MATCH(TRUE, $B3:$B$28 < 0, 0)), 0, IF((ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0)))+ 1) >= (MATCH(TRUE, $B3:$B$28 < 0, 0)), ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0))), 0))
Thanks in advance!!
Regards
Neeraj Kumar Agarwal
Would you be kind enough to brief about the following formula highlighted with Pink colour
=IF(ISERROR(MATCH(TRUE, $B3:$B$28 < 0, 0)), 0, IF((ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0)))+ 1) >= (MATCH(TRUE, $B3:$B$28 < 0, 0)), ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0))), 0))
Thanks in advance!!
Regards
Neeraj Kumar Agarwal
NARAYANK991
Excel Ninja
Hi ,
The portions you have highlighted are all having different functions ; what exactly is the reason you have highlighted only these keywords ?
ISERROR is a function which returns a TRUE or FALSE value , based on whether the parameter passed to it is an error value or not.
Since the parameter passed to it is the result of the MATCH function , if the MATCH function finds a match , the result is a number ; if it does not find a match , the result is a #N/A error value.
Thus , the ISERROR function will return a TRUE value if the MATCH function does not find a match.
What is the MATCH function looking for ?
It is looking for a value in the range $B3:$B$28 which is less than zero.
You will understand the entire formula if I explain the logic behind it.
What are we trying to achieve ?
We wish to conditionally format certain cells in column A , based on whether certain cells in column B have any negative values.
Let us take an example ; suppose cell B7 has the value -2 ; we wish to conditionally format cells A7 , A6 and A5.
Suppose cell B13 has the value -5 ; we wish to conditionally format cells A13 , A12 , A11 , A10 , A9 and A8.
Thus , if both B7 and B13 have their negative values of -2 and -5 , all the cells from A5 through A13 will be conditionally formatted.
Our data range starts from row 3.
The formula in cell E3 (a helper column) is :
=IF(ISERROR(MATCH(TRUE, $B3:$B$28 < 0, 0)), 0, IF((ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0)))+ 1) >= (MATCH(TRUE, $B3:$B$28 < 0, 0)), ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0))), 0))
Since we have a negative value of -2 in B7 , the highlighted portions in the formula in E3 will return 5.
Since there is a match , we can safely ignore the ISERROR part of the formula , and reduce it to :
=IF((ABS(INDEX($B3:$B$28, 5))+ 1) >= 5, ABS(INDEX($B3:$B$28, 5)), 0)
where I have replaced the MATCH portion of the formula by the result it returns.
Since the part :
INDEX($B3:$B$28, 5)
is referring to the negative number -2 , we can reduce the formula further to :
=IF((ABS(-2) + 1) >= 5, ABS(-2), 0)
where I have replaced the INDEX portion of the reduced formula by the result it returns.
This then reduces to :
=IF((2 + 1) >= 5, 2 , 0)
If we now move down to E4 , the MATCH function will return 4 , and therefore the reduced formula will become :
=IF((2 + 1) >= 4, 2 , 0)
If we now move down to E5 , the MATCH function will return 3 , and therefore the reduced formula will become :
=IF((2 + 1) >= 3, 2 , 0)
At this stage , the IF condition is satisfied , and hence E5 will have the value 2.
E6 will also have the value 2 , and so will E7.
When we are in row 8 , we have gone beyond the first negative value of -2 , and the MATCH function will now return a result corresponding to the next negative value of -5 ; since we are in row 8 , the result will be 6.
The IF function , in its reduced form , will now be :
=IF((5 + 1) >= 6, 5 , 0)
Thus all the cells from E8 through E13 will be populated with the value 5.
Narayan
The portions you have highlighted are all having different functions ; what exactly is the reason you have highlighted only these keywords ?
ISERROR is a function which returns a TRUE or FALSE value , based on whether the parameter passed to it is an error value or not.
Since the parameter passed to it is the result of the MATCH function , if the MATCH function finds a match , the result is a number ; if it does not find a match , the result is a #N/A error value.
Thus , the ISERROR function will return a TRUE value if the MATCH function does not find a match.
What is the MATCH function looking for ?
It is looking for a value in the range $B3:$B$28 which is less than zero.
You will understand the entire formula if I explain the logic behind it.
What are we trying to achieve ?
We wish to conditionally format certain cells in column A , based on whether certain cells in column B have any negative values.
Let us take an example ; suppose cell B7 has the value -2 ; we wish to conditionally format cells A7 , A6 and A5.
Suppose cell B13 has the value -5 ; we wish to conditionally format cells A13 , A12 , A11 , A10 , A9 and A8.
Thus , if both B7 and B13 have their negative values of -2 and -5 , all the cells from A5 through A13 will be conditionally formatted.
Our data range starts from row 3.
The formula in cell E3 (a helper column) is :
=IF(ISERROR(MATCH(TRUE, $B3:$B$28 < 0, 0)), 0, IF((ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0)))+ 1) >= (MATCH(TRUE, $B3:$B$28 < 0, 0)), ABS(INDEX($B3:$B$28, MATCH(TRUE, $B3:$B$28 < 0, 0))), 0))
Since we have a negative value of -2 in B7 , the highlighted portions in the formula in E3 will return 5.
Since there is a match , we can safely ignore the ISERROR part of the formula , and reduce it to :
=IF((ABS(INDEX($B3:$B$28, 5))+ 1) >= 5, ABS(INDEX($B3:$B$28, 5)), 0)
where I have replaced the MATCH portion of the formula by the result it returns.
Since the part :
INDEX($B3:$B$28, 5)
is referring to the negative number -2 , we can reduce the formula further to :
=IF((ABS(-2) + 1) >= 5, ABS(-2), 0)
where I have replaced the INDEX portion of the reduced formula by the result it returns.
This then reduces to :
=IF((2 + 1) >= 5, 2 , 0)
If we now move down to E4 , the MATCH function will return 4 , and therefore the reduced formula will become :
=IF((2 + 1) >= 4, 2 , 0)
If we now move down to E5 , the MATCH function will return 3 , and therefore the reduced formula will become :
=IF((2 + 1) >= 3, 2 , 0)
At this stage , the IF condition is satisfied , and hence E5 will have the value 2.
E6 will also have the value 2 , and so will E7.
When we are in row 8 , we have gone beyond the first negative value of -2 , and the MATCH function will now return a result corresponding to the next negative value of -5 ; since we are in row 8 , the result will be 6.
The IF function , in its reduced form , will now be :
=IF((5 + 1) >= 6, 5 , 0)
Thus all the cells from E8 through E13 will be populated with the value 5.
Narayan
neeraj2050
Member
NARAYANK991
Excel Ninja
Hi ,
The ABS function is simple ; it returns the absolute value of its parameter , which means that if it is passed a positive number , it returns that number as it is , and if it is passed a negative number , it returns its positive value.
Thus =ABS(7) will return 7 ; so will ABS(-7).
=ABS(-31.73) will return 31.73
Narayan
The ABS function is simple ; it returns the absolute value of its parameter , which means that if it is passed a positive number , it returns that number as it is , and if it is passed a negative number , it returns its positive value.
Thus =ABS(7) will return 7 ; so will ABS(-7).
=ABS(-31.73) will return 31.73
Narayan
neeraj2050
Member
Thanks a lot Sir!!
Thanks Narayan for your help. And there was one suggestion or kind of nice trick suggested was
INDIRECT("$B" & ROW() & ":$B$28") can be changed to
INDIRECT("$B" & SUM(ROW()) & ":$B$28")
The sum() converts the result of ROW() from array to number and will be used with other parts of formula making valid reference for indirect.
But not sure how reliable this behavior is across various office versions (Excel 2003 to 2010 or above)
INDIRECT("$B" & ROW() & ":$B$28") can be changed to
INDIRECT("$B" & SUM(ROW()) & ":$B$28")
The sum() converts the result of ROW() from array to number and will be used with other parts of formula making valid reference for indirect.
But not sure how reliable this behavior is across various office versions (Excel 2003 to 2010 or above)
NARAYANK991
Excel Ninja
Hi ,The formula has one issue. If I put 10 in C3 it paints from C3 to C13 correctly. If I put 2 in C10, C13 is not highlighted.
I have highlighted in red color in the attached file. The same happens for backward index as well.
For the positive formatting , a slight change in the formula is adequate to correct for the problem.
For the negative formatting , I am not able to see any solution. Because we are looking at numbers as they come , when we look at the -1 , we have no idea that a -4 is ahead. Even if we can take care of this second level , what is to say that we will not come across a -10 somewhere further down ?
If you can confirm how many levels of nesting there will be , we can possibly introduce those many helper columns and arrive at a solution.
Narayan
Attachments
NARAYANK991
Excel Ninja
Hi ,The range will be between -15 to -1 for negative.
The range will be between +1 to +15 for positive
What I meant was that in the example you gave earlier , the -4 needed to overrride the -1 ; thus there were two levels of nesting.
Now , if within the extents of the -4 , there were a greater negative number , say -8 two rows down , then the -8 would override the -4 and the -1 , and this would then be three levels of nesting.
Narayan