This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

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

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

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

I meant this:

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

Please let me know if we are on same way.

suat

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)

M3: =MATCH(L3-0.000001,Data,1)

M4: =MATCH(L4-0.000001,Data,1)

M5: =MATCH(L5-0.000001,Data,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))

N2: =ROW(OFFSET(Data,0,0,1,1))

N3: =ROW(OFFSET(Data,0,0,1,1))

N4: =ROW(OFFSET(Data,0,0,1,1))

N5: =ROW(OFFSET(Data,0,0,1,1))

N6: =ROW(OFFSET(Data,0,0,1,1))

Hope this help,

Sébastien

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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

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

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.