Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Returning Cell Range for Quartiles

Posted on 2002-03-11
Medium Priority
319 Views
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
Question by:SBFurr
[X]
###### 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

LVL 17

Expert Comment

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

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

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

LVL 16

Expert Comment

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

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,
0

LVL 6

Accepted Solution

bkpchs237 earned 400 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

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

Question has a verified solution.

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

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.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
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â€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll