Group by

[mcquam]

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.

 

[mcquam]

I need to exclude the top 20% of durations rather than a fixed number.

 

[Herbert Seidenberg]

Using Pivot Table and Percentrank:
http://www.savefile.com/files/1582656

 

[mcquam]

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

 

[Herbert Seidenberg]

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.

 

[mcquam]

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?

 

[Herbert Seidenberg]

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

 

[mcquam]

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%.

 

[Herbert Seidenberg]

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%.

 

[Herbert Seidenberg]

Add another Pivot Table
http://www.savefile.com/files/1586123

 

[mcquam]

Absolutely brilliant. I can't thank you enough.

 

[Peter Tremain]

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