Showing the top result from a range of cells
 

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.

Showing the top result from a range of cells
 

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,"Hig hest",""))

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Showing the top result from a range of cells
 

GS wrote on 2/25/2011 :
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,"Hig hest",""))

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

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

You should be able to copy this beyond G6:G17

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Showing the top result from a range of cells
 

In article , GS
writes
Actually, select G6:G17 and revise the formula as follows:

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

You should be able to copy this beyond G6:G17

--
Garry


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.

Showing the top result from a range of cells
 

Colin Hayes expressed precisely :
In article , GS
writes
Actually, select G6:G17 and revise the formula as follows:

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

You should be able to copy this beyond G6:G17

--
Garry


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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Showing the top result from a range of cells
 

In article , GS
writes
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.

--
Garry

Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes

Showing the top result from a range of cells
 

Colin Hayes brought next idea :
In article , GS
writes
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.

--
Garry

Hi Garry

Yes , that's fixed it. Working perfectly.

Thanks very much for your time and expertise.

Best wishes

Glad to help! Good luck...!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc