Difference Between Highest And Lowest?

[Saxman]

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.
--

 

[Guest]

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)

 

[Guest]

This should do it

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

Mike

 

[Saxman]

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

That works fine.

Thanks so much.

--

 

[Guest]

Your welcome

That works fine.

Thanks so much.