Showing the top result from a range of cells

[Colin Hayes]

Hi

In column F6 - F17 I have a range of formulas giving a range of numeric
results.

Against these I'm trying in column G to have an indication of which of
the cells in F has the highest result , and which the lowest.

Like this

F G

3 Lowest

7

5

11 Highest

9


As the results in F change , so would the noting of highest and lowest.

Grateful for any help with this.

 

[GS]

Colin Hayes explained on 2/25/2011 :

Hi

In column F6 - F17 I have a range of formulas giving a range of numeric
results.

Against these I'm trying in column G to have an indication of which of the
cells in F has the highest result , and which the lowest.

Like this

F G

3 Lowest

7

5

11 Highest

9


As the results in F change , so would the noting of highest and lowest.

Grateful for any help with this.

Try this formula in ColG...

=IF(MIN($F:$F)=$F1,"Lowest",IF(MAX($F:$F)=$F1,"Highest",""))

 

[GS]

GS wrote on 2/25/2011 :

Colin Hayes explained on 2/25/2011 :

Try this formula in ColG...

=IF(MIN($F:$F)=$F1,"Lowest",IF(MAX($F:$F)=$F1,"Highest",""))

Actually, select G6:G17 and revise the formula as follows:

Enter...
=IF(MIN($F:$F)=$F6,"Lowest",IF(MAX($F:$F)=$F6,"Highest",""))
Key Ctrl+Enter

You should be able to copy this beyond G6:G17

 

[Colin Hayes]

Actually, select G6:G17 and revise the formula as follows:

Enter...
=IF(MIN($F:$F)=$F6,"Lowest",IF(MAX($F:$F)=$F6,"Highest",""))
Key Ctrl+Enter

You should be able to copy this beyond G6:G17

Hi Garry

- OK thanks for that. It's working fine.

I do have some cells in the range which are 0.00 or empty , and need
these to be ignored by the formula until they have some content to
gauge.

As a small refinement , Is it possible to exclude cells with a zero or
no content?


Many thanks.

 

[GS]

Colin Hayes expressed precisely :

Hi Garry

- OK thanks for that. It's working fine.

I do have some cells in the range which are 0.00 or empty , and need these to
be ignored by the formula until they have some content to gauge.

As a small refinement , Is it possible to exclude cells with a zero or no
content?


Many thanks.

The formula ignores empty cells. I suggest you clear cells that are
0.00 or if this results from a formula then edit the formula to return
"" instead of 0.

 

[Colin Hayes]

The formula ignores empty cells. I suggest you clear cells that are 0.00 or if this
results from a
formula then edit the formula to return "" instead of 0.

Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes

 

[GS]

Colin Hayes brought next idea :

Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes

Glad to help! Good luck...!