Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percent of use | Excel Discussion (Misc queries) | |||
Using Percent | New Users to Excel | |||
Percent | New Users to Excel | |||
Percent of | Excel Discussion (Misc queries) | |||
Formatting a number to look like a Percent without a percent sign | Excel Discussion (Misc queries) |