Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a column of dates and a column of durations. I have used a pivot
table to work out the average duration by month. However, I also need to calculate the average after excluding the highest durations for each month. I have tried PERCENTRANK which works OK for the whole column but I can't figure out how to group it by month. I also don't want to have to have a column for each month using lookup or match. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I need to exclude the top 20% of durations rather than a fixed number.
"mcquam" wrote: I have a column of dates and a column of durations. I have used a pivot table to work out the average duration by month. However, I also need to calculate the average after excluding the highest durations for each month. I have tried PERCENTRANK which works OK for the whole column but I can't figure out how to group it by month. I also don't want to have to have a column for each month using lookup or match. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() "Herbert Seidenberg" wrote: Using Pivot Table and Percentrank: http://www.savefile.com/files/1582656 What a wonderfully elegant solution! I'd be really grateful if you would explain what Date R does in: =PERCENTRANK(IF(MONTH(Date)=MONTH(Date R),Duration),IF(MONTH(Date)=MONTH(Date R),Duration),2). |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Date R refers to the intersection of the defined range Date
and the row of that cell. A space in a formula works as an Intersection Operator. Tools Options General R1C1 Reference Style (uncheck) will convert the expression into a more familiar style. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Many thanks, i think i've got it now and i have it working nicely on my spreadsheet. i do have a question though. My results were not exactly as i expected. For example, using your data, you have 23 entries for January 08, so I would expect 4 or 5 records to represent 20% (depending on rounding). Your figures show 7 records, or around 30%. Have I picked you up wrong? |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Use this formula instead:
=1-(SUMPRODUCT(--((MONTH(Date)=MONTH(Date R)) *Duration=Duration R))-1) /(SUMPRODUCT(--(MONTH(Date)=MONTH(Date R)))-1) http://www.savefile.com/files/1585335 |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Thank you very much. I'm sorry to come back again but the formula resloves to div/0 if there is only one date. If I simply exclude the error, it incorrectly assumes it is above 80%. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Jun 2, 1:36 am, mcquam wrote:
Thank you very much. I'm sorry to come back again but the formula resloves to div/0 if there is only one date. If I simply exclude the error, it incorrectly assumes it is above 80%. |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Absolutely brilliant. I can't thank you enough. |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi, Not sure if i am in the right spot but i am in a real bind and hoping someone can assist.
I have a sheet containing 2 Columns of Data Column A Job Title and C is Salary. A B Elec $8 Plumber $12 Tech $10 Elec $76 Plumber $22 Tech $56 I can easily percentrank the total of all salaries by using the following =percentrank(B1:B150,B1) however; I need to percentrank the titles rather than the total of all such as percentrank of all "Tech's". I have tried If and Sumproduct but neither provide the result. Any help appreciated. Many thanks Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
I wish to group my worksheets under group tabs | Excel Worksheet Functions | |||
How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
Taking age group Ie ages 20-29 and picking out net sales for group | Excel Worksheet Functions | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions |