| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). -- |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). -- |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. -- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. -- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
