• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Returning Cell Range for Quartiles

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
SBFurr
Asked:
SBFurr
1 Solution
 
Suat OzgurWeb / Application DeveloperCommented:
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
 
SBFurrAuthor Commented:
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
 
Suat OzgurWeb / Application DeveloperCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sebastienmCommented:
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
 
bkpchs237Commented:
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
 
SBFurrAuthor Commented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now