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