The first 25 percent
 

I have a multi-row, multi column spreadsheet. Of the columns, 3 of them are
the most crucial. Let's say,

Column A is: User
Column B is: Date
Column C is: Cycle Time (Calculated from B and another column)

What I would like to know is the average (median, etc...) Cycle Time for
the FIRST 25% (50%, 75% etc...) of the Users signed up.

Any ideas?

The first 25 percent
 

=AVERAGE(C2:OFFSET(C2,25%*(COUNTA(A2:A65536)-1),0))
=AVERAGE(C2:OFFSET(C2,50%*(COUNTA(A2:A65536)-1),0))
=AVERAGE(C2:OFFSET(C2,75%*(COUNTA(A2:A65536)-1),0))
--
David Biddulph

"PAL" wrote in message
...
I have a multi-row, multi column spreadsheet. Of the columns, 3 of them
are
the most crucial. Let's say,

Column A is: User
Column B is: Date
Column C is: Cycle Time (Calculated from B and another column)

What I would like to know is the average (median, etc...) Cycle Time for
the FIRST 25% (50%, 75% etc...) of the Users signed up.

Any ideas?

The first 25 percent
 

Add a helper column - I will use column D but you could use any column
I will assume there are 22 rows - of course you have many more so change
formulas as needed
In D1 use the formula =RANK(B1,$B$1:$B$22,1)/COUNT($B$1:$B$23)
Format the cell as Percent and copy down the column

To compute the average of the top 25% use
=SUMPRODUCT(--($D$1:$D$22<=25%),$C$1:$C$22)/SUMPRODUCT(--($D$1:$D$22<=25%))

For the medians use =MEDIAN(IF($D$1:$D$22<=25%,$C$1:$C$22,""))
This is an array formula and must be enterd with CTRL+SHIFT+ENTER

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PAL" wrote in message
...
I have a multi-row, multi column spreadsheet. Of the columns, 3 of them
are
the most crucial. Let's say,

Column A is: User
Column B is: Date
Column C is: Cycle Time (Calculated from B and another column)

What I would like to know is the average (median, etc...) Cycle Time for
the FIRST 25% (50%, 75% etc...) of the Users signed up.

Any ideas?

The first 25 percent
 

The formulae which I gave below look at the first 25% (or whatever
proportion) in the list, so if you want the first 25% by date, you'd need to
sort by date.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=AVERAGE(C2:OFFSET(C2,25%*(COUNTA(A2:A65536)-1),0))
=AVERAGE(C2:OFFSET(C2,50%*(COUNTA(A2:A65536)-1),0))
=AVERAGE(C2:OFFSET(C2,75%*(COUNTA(A2:A65536)-1),0))
--
David Biddulph

"PAL" wrote in message
...
I have a multi-row, multi column spreadsheet. Of the columns, 3 of them
are
the most crucial. Let's say,

Column A is: User
Column B is: Date
Column C is: Cycle Time (Calculated from B and another column)

What I would like to know is the average (median, etc...) Cycle Time for
the FIRST 25% (50%, 75% etc...) of the Users signed up.

Any ideas?