Help with a formula
Posted by Libby on February 08, 2002 11:04 AM
Please can you tell me how I would set up a formula for the follwing sum.
I want to do a "if formula" for a datasheet based on quartiles.
I want to return a value of 1 - 4 based this rule
if < then lower quartile return a value of 4
If > then lower but < then median return a value of 3
If > then median but < the upper return a value of 2
If > than Upper return a value of 1
I will carry this acrossmy whole data set
My data
40%
Lower quartile
35%
Median
46%
Upper quartile
51%
Looking forward to hearing from you
Thanks for help
Libby
Posted by Juan Pablo G. on February 08, 2002 11:22 AM
In A2:B4 i have the limits:
{"Lower",0.35;"Median",0.46;"Upper",0.51}
In A7 I have the 40%. Used this formula
=(A7<$B$2)*4+(A7>=$B$2)*3-(A7>=$B$4)-(A7>=$B$3)
You can drag this formula to fill all your data.
Juan Pablo G.
Posted by Mark W. on February 09, 2002 2:42 PM
Of course, this could be done without IFs using QUARTILE()...
Suppose, A1:A21 contains all of your data such
as...
{0
;0.05
;0.1
;0.15
;0.2
;0.25
;0.3
;0.35
;0.4
;0.45
;0.5
;0.55
;0.6
;0.65
;0.7
;0.75
;0.8
;0.85
;0.9
;0.95
;1}
You could enter the array formula...
{=SUM((A1<=QUARTILE(A:A,{1,2,3,4}))+0)}
in each of the corresponding cells in column B
to display the quartile for each value, or you
could calculate the quartile for a specific
value (e.g., 0.4) using...
{=SUM((0.4<=QUARTILE(A:A,{1,2,3,4}))+0)}
Note: These are array formulas which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Also, the QUARTILE() function is available only
after adding in the Analysis ToolPak.
Posted by Libby on February 10, 2002 4:09 AM
BIG THANKS for your time guys works great.
