Difference Between Highest And Lowest?
 

If I have a column of numbers from A1:A100 which is constantly being updated, what
formula would I need to put into cell A101 which could calculate the difference
between the highest and lowest numbers. Some cells might be blank, which I would
choose to ignore rather than be calculated as zero.

Thanks.
--

Difference Between Highest And Lowest?
 

try A101+=MAX(A1:A100)-MIN(A1:A100)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Saxman" wrote:

If I have a column of numbers from A1:A100 which is constantly being updated, what
formula would I need to put into cell A101 which could calculate the difference
between the highest and lowest numbers. Some cells might be blank, which I would
choose to ignore rather than be calculated as zero.

Thanks.
--

Difference Between Highest And Lowest?
 

This should do it

=MAX(A1:A100)-SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

Mike

"john" wrote:

try A101+=MAX(A1:A100)-MIN(A1:A100)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Saxman" wrote:

If I have a column of numbers from A1:A100 which is constantly being updated, what
formula would I need to put into cell A101 which could calculate the difference
between the highest and lowest numbers. Some cells might be blank, which I would
choose to ignore rather than be calculated as zero.

Thanks.
--

Difference Between Highest And Lowest?
 

Mike wrote:

=MAX(A1:A100)-SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

That works fine.

Thanks so much.

--

Difference Between Highest And Lowest?
 

Your welcome

"Saxman" wrote:

Mike wrote:

=MAX(A1:A100)-SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

That works fine.

Thanks so much.

--