MS Excel 2003 Selecting an array for a quartile formula 1

[kevvo]

Hi there!

I'm currently working on a spreadsheet where I need to calculate a quartile based on data in one of the columns. However the data is categorised by a variable in a preceeding column. Here's an example:

Column A Column B
Type 1 1.4
Type 1 2.8
Type 1 3.4
Type 1 1.2.....(for hundreds of rows, then...)
Type 2 3.6
Type 2 2.1
Type 2 2.0....etc...

I wish to include a column "C" which returns the top quartile for data items in each range defined by column A. For example, the top quartile for all of "type 1" data, to be shown in column C (so in effect, the same quartile value will be displayed next to all type 1 data items).

The spreadsheet is massive, with lots of different data types, so I would like to define the quartiles based on the ranges in column A through a formula that can be copied and pasted throughout the sheet.

Any advice appreciated!

 

[SkipVought]

Hi,

This requirement is very nebulous.

Could you be a tad more specific?

What is your formula?

When you say, "...that can be copied and pasted throughout the sheet," what does that mean? Pasted in different COLUMNS or in different ROWS in column C?

Skip,

_[red][/red]

 

[kevvo]

Hi Skip!

Yes, it is a strange one!

In essence I would like to return the quartile in column C, and I know that this would be a duplicated value for the data items of any particular category.

I would just like to be able to extend the formula throughout the rest of the rows in column C, but the quartile calculated on arrays based on the categories defined in column A.

So the structure would look like this:

Column A Column B Column C
Type 1 1.4 Quart all B values relating to type 1
Type 1 2.8 Quart all B values relating to type 1
Type 1 3.4 Quart all B values relating to type 1
Type 1 1.2 Quart all B values relating to type 1
Type 2 1.3 Quart all B values relating to type 2
Type 2 2.1 Quart all B values relating to type 2
Type 2 2.0 Quart all B values relating to type 2

etc...

I thought I could do a quartile with the "sumif" formula embedded in it like so:

=quartile(sumif(A$1:A$5000,A1,B$1:B$5000),1)

But obviously utilising a sum doesn't return the true quartile. In essence I need the formula to select the range of data items related to each type defined in column A.

I hope this sheds some light...

 

[SkipVought]

I named the RANGES
[tt]
TYP VAL
[/tt]
Ranges begin in row 2...
[tt]
=QUARTILE(OFFSET(A1,MATCH(A2,TYP,0),1,COUNTIF(TYP,A2),1),1)
[/tt]


Skip,

_[red][/red]

 

[kevvo]

That's the one! A little bit of fiddling and it works a treat, and saved me about 2 hours of manually selecting ranges!

Thanks Skip!