Conditional Formatting Prob...

simpsojp

New Member
Joined
Apr 14, 2002
Messages
7
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Aladin,

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

Chris
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top