Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Conditional Formatting Prob...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am having a problem with the conditional formatting function... I have hundreds of lists, all of varying lengths. I want to highlight the first occurence of the maximum value in everylist. Conditional formatting seemed like the easiest method to do this. However, excel has a different opinion. I am using this formula:

    =AND(MAX($B$39:$B$65536)=$B40,MAX($B$39:$B39)<$B40)

    It works fine and highlights the correct number each time. However when I move an arrow up next to the highlighted text the highlighting disappears and the conditional formatting changes to:

    =AND(MAX($B$39:$B$65536)=$B40,MAX($B$39:$B$65536)<$B40)

    It changes to every cell that it was applied too. This new formula doesn't work. I am using Excel 97. Can someone shed some light?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 08:00, simpsojp wrote:
    I am having a problem with the conditional formatting function... I have hundreds of lists, all of varying lengths. I want to highlight the first occurence of the maximum value in everylist. Conditional formatting seemed like the easiest method to do this. However, excel has a different opinion. I am using this formula:

    =AND(MAX($B$39:$B$65536)=$B40,MAX($B$39:$B39)<$B40)

    It works fine and highlights the correct number each time. However when I move an arrow up next to the highlighted text the highlighting disappears and the conditional formatting changes to:

    =AND(MAX($B$39:$B$65536)=$B40,MAX($B$39:$B$65536)<$B40)

    It changes to every cell that it was applied too. This new formula doesn't work. I am using Excel 97. Can someone shed some light?
    This is a beautiful question.

    Use the following formula instead:

    =AND(B39=MATCH(MAX($B$39:$B$65536),$B$39:$B$65536,0),COUNTIF($B$39:B39,B39)=1)

    I assumed, judging by your own formula, your range to start at B39. If not, adjust to suit. By the way, do you really need to go as far as row 65536?

    Aladin

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't use MATCH because my list is not in ascending order. There is no way I can sort the numbers either because they are for a load-displacement graph. I have 7 columns and the second column contains the 'load' values. That data is what I want to determine the highlighting. I tried this formula with the COUNTIF function:

    =AND($B40=MAX($B$39:$B$65536),COUNTIF($B$39:$B40,$B40)=1)

    When I move the arrow, excel changes it to:

    =AND($B40=MAX($B$39:$B$65536),COUNTIF($B$39:$B$1,$B40)=1)

    I'm starting to think it may be a bug in excel. Thanks for your help... I have no idea what is going on!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 08:51, simpsojp wrote:
    I can't use MATCH because my list is not in ascending order. There is no way I can sort the numbers either because they are for a load-displacement graph. I have 7 columns and the second column contains the 'load' values. That data is what I want to determine the highlighting. I tried this formula with the COUNTIF function:

    =AND($B40=MAX($B$39:$B$65536),COUNTIF($B$39:$B40,$B40)=1)

    When I move the arrow, excel changes it to:

    =AND($B40=MAX($B$39:$B$65536),COUNTIF($B$39:$B$1,$B40)=1)

    I'm starting to think it may be a bug in excel. Thanks for your help... I have no idea what is going on!
    You can use MATCH as I suggested:

    MATCH(MAX($B$39:$B$65536),$B$39:$B$65536,0)

    doesn't require sorting. 0 as its third arg just means FALSE.

    Aladin


  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right... Sorry. I was reading the help file where it used 1 as the match_type.

    The match function finds the location of the first maximum number. In this certain case it returns the value 27. However number in that cell is 3.87. Therefore the formula evaluates to false and is not correctly highlighted.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 09:47, simpsojp wrote:
    Right... Sorry. I was reading the help file where it used 1 as the match_type.

    The match function finds the location of the first maximum number. In this certain case it returns the value 27. However number in that cell is 3.87. Therefore the formula evaluates to false and is not correctly highlighted.
    Mea Culpa. Try:

    =AND(ROW(B39)=MATCH(MAX($B$39:$B$65536),$B$39:$B$65536,0),COUNTIF($B$39:B39,B39)=1)

    If OK, would you comment on whether you need to go as far as row 65536?

    Aladin

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for your help...

    No, I don't. I just changed 65536 to 1000 and it worked. Can you tell me why excel has issues when I set the number to 65536?

    Thanks again... I thought my hair was gonna fall out

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 10:04, simpsojp wrote:
    Thank you for your help...

    No, I don't. I just changed 65536 to 1000 and it worked. Can you tell me why excel has issues when I set the number to 65536?

    Thanks again... I thought my hair was gonna fall out
    I'm sorry it's still not good enough. It should be:

    =AND(B39=MAX($B$39:$B$65536),ROW(B39)=MATCH(MAX($B$39:$B$65536),$B$1:$B$65536,0),COUNTIF($B$39:B39,B39)=1)

    The changes are hilited.

    If the range in B is not fixed, (that is, changing frequently), we could create a dynamic name range and use it in the above formula.

    Aladin

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey...

    I used:

    =COUNT($B$39:$B40)=MATCH(MAX($B$39:$B$1000),$B$39:$B$1000,0)

    This worked fine and I don't think I will need to go beyond 1000.

    Thanks again Aladin,

    Pete

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    this looks sort of similar to my "wrapping" observations in a named formula posting a few weeks ago...

    Chris

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •