MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with a formula


Posted by Libby on February 08, 2002 10:54 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 Larry Kramer on February 09, 2002 8:31 AM

if(mydata>upper,1,if(mydata>median,2,if(mydata>lowerquartile,3,4)))