Solved

Returning Cell Range for Quartiles

Posted on 2002-03-11
6
285 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:smozgur
Comment Utility
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
Comment Utility
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:smozgur
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:sebastienm
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now