Quartile Function
 

I would like to use a formula to return which quartile the given data array
falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%,
etc.

The quartile function only returns the value of the various quartiles, while
I need to know within which quartile the value falls.

Any help is appreciated.

On Tue, 15 Mar 2005 01:01:02 -0800, "tika528"
wrote:

I would like to use a formula to return which quartile the given data array
falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%,
etc.

The quartile function only returns the value of the various quartiles, while
I need to know within which quartile the value falls.

Any help is appreciated.


Perhaps:

=FLOOR(A1,0.25)*4

will do what you describe?


--ron

For a given observation in the data set,

=SUMPRODUCT(--(obsQUARTILE(allData,{1,2,3,4})))+1

should do it.

Jerry

tika528 wrote:

I would like to use a formula to return which quartile the given data array
falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%,
etc.

The quartile function only returns the value of the various quartiles, while
I need to know within which quartile the value falls.

Any help is appreciated.

Hi Jerry, I tried this, but get a #name error- it seems to not recognize the
"obs" funciton. Actually, I don't get this one either. Is it a special
function from an add-in?

Thanks for your help.

"Jerry W. Lewis" wrote:

For a given observation in the data set,

=SUMPRODUCT(--(obsQUARTILE(allData,{1,2,3,4})))+1

should do it.

Jerry

tika528 wrote:

I would like to use a formula to return which quartile the given data array
falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%,
etc.

The quartile function only returns the value of the various quartiles, while
I need to know within which quartile the value falls.

Any help is appreciated.

"obs" is not a function, it is a place holder for your observation, just
as "allData" is a place holder for the endire data set. Replace the
place holders with appropriate cell references, range names, or constants.

Jerry

tika528 wrote:

Hi Jerry, I tried this, but get a #name error- it seems to not recognize the
"obs" funciton. Actually, I don't get this one either. Is it a special
function from an add-in?

Thanks for your help.

"Jerry W. Lewis" wrote:


For a given observation in the data set,

=SUMPRODUCT(--(obsQUARTILE(allData,{1,2,3,4})))+1

should do it.

Jerry

tika528 wrote:


I would like to use a formula to return which quartile the given data array
falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%,
etc.

The quartile function only returns the value of the various quartiles, while
I need to know within which quartile the value falls.

Any help is appreciated.