1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting help required

Discussion in 'Ask an Excel Question' started by ksnexcel, Sep 19, 2017.

  1. ksnexcel

    ksnexcel New Member

    Messages:
    16
    I have attached an excel sheet, where in column A need to be highlighted based on the value in Column B or Column C. I want the conditional formula to be compatible with office 2003 and more generic as possible. Provided details in the attached excel file.

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose and ksnexcel like this.
  3. ksnexcel

    ksnexcel New Member

    Messages:
    16
    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.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    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
  5. ksnexcel

    ksnexcel New Member

    Messages:
    16
    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.
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    Please go through the formulae in the helper columns , and then comment.

    Narayan
  7. ksnexcel

    ksnexcel New Member

    Messages:
    16
    Apologies, will take a look and update you. Thanks
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    I have seen that there is a mistake in the formula in one of the helper columns.

    Please see the attached file for a revised version.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  9. ksnexcel

    ksnexcel New Member

    Messages:
    16
    Thanks for your help. It works fine. I am just trying any possibility of squeezing the column F, so I can put it as a single conditional format.
  10. ksnexcel

    ksnexcel New Member

    Messages:
    16
    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?
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    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
    Thomas Kuriakose likes this.
  12. ksnexcel

    ksnexcel New Member

    Messages:
    16
    Hi,

    Uploaded the file with the mentioned formula in column J and certainly the values in column E and column J doesn't match and seems formula doesn't work. INDIRECT("$B" & ROW() & ":$B$28")

    Attached Files:

  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    I had posted a revision which was not incorporated ; that has been done in the attached file.

    Narayan

    Attached Files:

    ksnexcel and Thomas Kuriakose like this.
  14. ksnexcel

    ksnexcel New Member

    Messages:
    16
    Also my formula may get more generic as it programatically getting generated. So I would like my formula should have dynamic row and column.

    INDIRECT(ADDRESS(ROW(),COLUMN()) & ":" & ADDRESS(ROW(), COLUMN()))
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    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
    ksnexcel and Thomas Kuriakose like this.
  16. neeraj2050

    neeraj2050 Member

    Messages:
    154
    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
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    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
    ksnexcel and neeraj2050 like this.
  18. neeraj2050

    neeraj2050 Member

    Messages:
    154
    @Narayan Sir: Sir, thanks a lot for taking pain to share such details. Would you please elaborate a little about ABS as I have no idea about ABS

    Thanks in advance!!
  19. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    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
    neeraj2050 likes this.
  20. neeraj2050

    neeraj2050 Member

    Messages:
    154
    Thanks a lot Sir!!
  21. ksnexcel

    ksnexcel New Member

    Messages:
    16
    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)
  22. ksnexcel

    ksnexcel New Member

    Messages:
    16
    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.

    Attached Files:

  23. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    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

    Attached Files:

  24. ksnexcel

    ksnexcel New Member

    Messages:
    16
    The range will be between -15 to -1 for negative.
    The range will be between +1 to +15 for positive
  25. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    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

Share This Page