Conditional format only the lowest in a named range

[Wilco()]

I have a named range (let's call that NamedRange1), all containing numbers. I want to highlight only the lowest number in that range.

I know I can use =min(NamedRange1) to display the lowest number in a cell. But I want specifically to hightlight the cell containing the lowest number.

How can I use NamedRange in a formula and conditional formatting?

 

[NARAYANK991]

Hi ,

Select the range , and use the following formula :

=INDEX(NamedRange1,ROW(A1))=MIN(NamedRange1)

Narayan

 

[Anant Chirmade]

Hi Wilco,

2 methods of doing this :

1) Select range >> goto conditional formatting >> Top-Bottom Rules >> Click on Bottom 10 Items, then in the dialog box, change 10 to 1 and click OK.

2)When you will use named formula in conditional formatting, it will highlight
all the cells.
So, you can use a helper cell, suppose in a cell "C1" write the formula
=min(namedrange1)
select the range and in conditional formatting enter "=$C$1"

Good Day

Anant

 

[Anant Chirmade]

Hi ,

Select the range , and use the following formula :

=INDEX(NamedRange1,ROW(A1))=MIN(NamedRange1)

Narayan

Ohh Yess, our last love "Index" formula.. Thanks Narayan..
How couldn't I thought of that !!

 

[Wilco()]

Hi ,

Select the range , and use the following formula :

=INDEX(NamedRange1,ROW(A1))=MIN(NamedRange1)

Narayan

Note sure how to use this. I have tried to replace A1 with my cells. But it did not work.

The cells are not adjecent. But like this:

- |--A--| B |--C--| D |--E-- | F
1|
2|Value|---|Value|---|Value|---
3|

In the other cells are other information. But I want to highlight the lowest number in range A2, C2 and E2.

 

[NARAYANK991]

Hi ,

As long as your NamedRange1 is created using the selected cells , you can use the formula :

=Top left corner cell address of NamedRange1 = MIN(NamedRange1)

For example , suppose your NamedRange1 refers to the cells A11 , J17 , H22 , K5 ; with the cursor in A11 , enter the following rule :

=A5=MIN(NamedRange1)

since A5 is the top left corner cell address of the range starting at row 5 and column A.

Once you have created the rule , modify it by going into edit mode , and in the Applies To box enter =NamedRange1 , so that the rule now applies to all the cells in the range.

Narayan