Try this entered as ab array ctrl+shift+enter
c1 must be empty, if not choose another cell

=(1/MIN(IF(A1:A122<C1,A1:A122))-(1/(MAX(A2:A122)+1)))*100

"Saxman" wrote:

Mike wrote:

=(1/(MIN(A2:A122)+1)-(1/(MAX(A2:A122)+1)))*100

This works fine, but will the above include blanks and zeros (which I don't)?

Hence COUNTIF in the original formula.

=MAX(A2:A122)-SMALL(A2:A122,COUNTIF($A$2:$A122,0)+1)

Your generous help is appreciated.

--