Reciprocals Anyone?
 

I have the following formula in my worksheet which basically gives me the
maximum/minumum numbers in a column.

How can I modify by adding 1 to the maximum/minumum numbers, then calculate the
reciprocals of those numbers, followed by the subtraction.

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

If max is 50 and min is 20.

Adding 1 to each = 51 and 21.

Reciprocals =

1/51 = 0.019
1/21 = 0.047

Max - Min, 0.047 - 0.019 = 0.028.

I would then like to multiply the result by 100 giving 28% (deviation).



--

Reciprocals Anyone?
 

Try


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

I have the following formula in my worksheet which basically gives me the
maximum/minumum numbers in a column.

How can I modify by adding 1 to the maximum/minumum numbers, then calculate the
reciprocals of those numbers, followed by the subtraction.

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

If max is 50 and min is 20.

Adding 1 to each = 51 and 21.

Reciprocals =

1/51 = 0.019
1/21 = 0.047

Max - Min, 0.047 - 0.019 = 0.028.

I would then like to multiply the result by 100 giving 28% (deviation).



--

Reciprocals Anyone?
 

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.

--

Reciprocals Anyone?
 

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.

--

Reciprocals Anyone?
 

I would think you could just combine the two:

=(1/(MIN(SMALL(A2:A122,COUNTIF($A$2:$A122,0)+1))+1)-(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.

--