Conditional Formatting Prob...

[simpsojp]

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?

[Aladin Akyurek]

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

[simpsojp]

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!

[Aladin Akyurek]

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

[simpsojp]

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.

[Aladin Akyurek]

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

[simpsojp]

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

[Aladin Akyurek]

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

[simpsojp]

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

[Chris Davison]

Aladin,

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

Chris