Showing the top result from a range of cells

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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.
 
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.
Click to expand...

Try this formula in ColG...

=IF(MIN($F:$F)=$F1,"Lowest",IF(MAX($F:$F)=$F1,"Highest",""))
 
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",""))
Click to expand...

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
 
GS said:
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
Click to expand...


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.
 
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.
Click to expand...

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.
 
GS said:
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.
Click to expand...

Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes
 
Colin Hayes brought next idea :
Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes
Click to expand...

Glad to help! Good luck...!
 
Back
Top