Returning Cell Range for Quartiles

Posted on 2002-03-11
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

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



Question by:SBFurr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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


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.


Author Comment

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.
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:

Please let me know if we are on same way.
Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

LVL 16

Expert Comment

ID: 6857388

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.)

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,

Accepted Solution

bkpchs237 earned 100 total points
ID: 6857733

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:
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:
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:
In cell D2 place the following formula:
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

Hope this helps.


Author Comment

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!  



Featured Post

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!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

740 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