How to color-format only MIN & MAX values in a range using CF.

[James Perry]

I have a range of sales values for a month. Using the MAX and MIN functions i can easily find out the MAX & MIN values.
What formula must i use in Conditional Formatting to color-format the MAX and MIN values using the 2-color scale option.
Thank you, friends.

 

[Sathish KV]

Hi,

Change according to your columns and rows

=$A1=MAX($A$1:$A$100)
=$A1=MIN($A$1:$A$100)

 

[Khalid NGO]

Hi James,

You can use a helper cell to get a Max / Min value.
Then try CF = to the helper cell. (see the attached sample file)

Although its not what you were asking.

 

[James Perry]

Many thanks Sathish and Khalid for ur solutions. Also thank u 4 the file, Khalid. That was interesting.
Here's a solution i thought i'd share.......
The simplest solution i got so far is to use the top/bottom Rules.
Top 10 items which you change to 1, and Bottom 10 items which you change to 1.
Hope u like it.

 

[Khalid NGO]

Very Good James...
I liked it.