Solved

Returning Cell Range for Quartiles

Posted on 2002-03-11
6
289 Views
Last Modified: 2006-11-17
I am running the Quartile function on a data set.
the dataset includes collumns a-j
the quartile function is run on collumn D. (square footage).  The data is sorted by this collumn, so each quartile is a continuous range.

HOW do I return the rows for each quartile??
I need to know what rows each quartile is including.
so I would have Quartile 0 = rowX  (0 being the low point)
Quartile 1 = row y to row z
etc

Does anyone have a VBA function which will give me this data?  

thanks

Sasha

0
Comment
Question by:SBFurr
6 Comments
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 6856707
Hmm, sorry, do you want to get lower and upper (previous and next values for selected quartile in the list i mean) value of selected quartile? I mean

3
4
5
6
7

Quartile(range,2)=5: do you want to see 4 and 6 in the same row of the quartile function located?

Quart        Prev :        Next:
5            4             6


I may be missing something in question sorry but this is easy to do that with two VLOOKUP by setting Range_Lookup boolean value as TRUE. If i am on the right way please let me know the provide function, not VBA code because no need to use VBA i think.

If i couldnot understand you then i am sorry to waste your question room.

Regards
suat
0
 

Author Comment

by:SBFurr
ID: 6857257
It would be great if a lookup function would work.

how our data works is the quartile returns a value for square footage.

For Instance if:

    Quartile (range,0) = 1000  this is the minimum
    Quartile (range,1) = 1200  this is 25% mark
    Quartile (range,2) = 1500  this is 50% mark
    Quartile (range,3) = 1600  this is 75% mark
    Quartile (range,4) = 2000  this is the maximum

What I need is the row numbers for the data
between each quartile.
So I could look at the data:

minimum would be =1000
1-25% would be square footage >1000 and <=1200
26-50% would be sq ftge >1200 and <=1500
51-75% would be sq ftge >1500 and <=1600
75-99% would be sq ftge >1600 and < 2000
maximum would be =2000

I need the corresponding row numbers for each of these so that I would have

minimum value range = rows 1-3 would be 3 records with 1000 sq ftge
1-25% value range = rows ....
26-50% value range = rows...
51-75% value range = rows...
76-99% value range = rows...
100% value range = rows...

hope that makes this clearer.
thanks,
Sasha
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 6857288
to find lower value is really easy with VLOOKUP (by using Range_Lookup = True or blank as default).


I meant this:

http://abone.turk.net/3dhayat/quartile.xls

Please let me know if we are on same way.
suat
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 16

Expert Comment

by:sebastienm
ID: 6857388
SBFurr,

Below, in my example, I assume the data range in ordered
ascending. Also, i name the data range $D$7:$D$36 Data
(easier to read.)

Quartile:
L1: =QUARTILE(Data,0)
L2: =QUARTILE(Data,1)
L3: =QUARTILE(Data,2)
L4: =QUARTILE(Data,3)
L5: =QUARTILE(Data,4)
L6: ="Last Data Row"

Row Number in Data range:
M1: =1
M2: =MATCH(L2-0.000001,Data,1)+1
M3: =MATCH(L3-0.000001,Data,1)+1
M4: =MATCH(L4-0.000001,Data,1)+1
M5: =MATCH(L5-0.000001,Data,1)+1
M6: =ROWS(Data)
(if you deal with very small numbers, you may want
to use a smaller 0.000...1 instead of 0.000001 .

Row Number in Sheet:
N1: =ROW(OFFSET(Data,0,0,1,1))+M1-1
N2: =ROW(OFFSET(Data,0,0,1,1))+M2-1
N3: =ROW(OFFSET(Data,0,0,1,1))+M3-1
N4: =ROW(OFFSET(Data,0,0,1,1))+M4-1
N5: =ROW(OFFSET(Data,0,0,1,1))+M5-1
N6: =ROW(OFFSET(Data,0,0,1,1))+M6-1

Hope this help,
Sébastien
0
 
LVL 6

Accepted Solution

by:
bkpchs237 earned 100 total points
ID: 6857733
SBFurr,

The quartile function does not necessarily return a value that is present in your list of data.  So which value do you wish to refer to in this case?  The value above or below the quartile number?

I used a range of A1 thru A30.  My sample data is as follows:
In cells A1 thru A30 I placed the following values:
1, 3, 3, 13, 13, 13, 13, 13, 14, 34, 34, 43, 46, 46, 51, 51, 51, 57, 61, 75, 134, 215, 345, 346, 346, 461, 571, 1346, 7513, 15761

In cell B1 place the following formula:
=QUARTILE($A$1:$A$30,ROW()-1)
Copy this formula from B1 thru B5 as there are only four quartiles plus the initial value (i.e. =quartile(array,0) thru =quartile(array,4))

Then in cell C1 place the formula:
=VLOOKUP(B1,$A$1:$A$30,1)
Copy this formula from C1 thru C5 to find the value equal to or just below the quartile number for each quartile (0 thru 4).

Note: =quartile(array,0) is the same as =min(array) and =quartile(array,4) is the same as =max(array)

In cell D1 place the following formula:
="A"&MATCH(C1,$A$1:$A$30)
In cell D2 place the following formula:
="A"&MATCH(C1,$A$1:$A$30)+1&":A"&MATCH(C2,$A$1:$A$30)
Copy this formula from D2 thru D5.

This will effectively provide you with the range of the items within the quartile.  In my example you will return
A1
A2:A8
A9:A17
A18:A22
A23:A30

Hope this helps.

0
 

Author Comment

by:SBFurr
ID: 6857794
I appreicate all the help from everyone!
I selected the answer that was the easiest for me, personnally to understand.  
I must admit that I am used to Access, and all this putting formulas in each cell is a big boggling to me!  

thanks!

Sasha
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question