Formula to Return 5 Highest/Lowest Scores from Pivot Table

[marvin jones]

Hi All,

I have a pivot table which contains a row of months with a column of employees. I have sorted the data with highest scores first, but don't know how to write a formula which returns with the top 5 highest/lowest employees per month.

Also, as in the example below, there are blanks in the pivot due to employees not being in.

Example:[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]04[/TD]
[TD]05[/TD]
[TD]06[/TD]
[TD]07[/TD]
[/TR]
[TR]
[TD]Driver 1[/TD]
[TD]60.05%[/TD]
[TD][/TD]
[TD]52.01%[/TD]
[TD]61.31%[/TD]
[/TR]
[TR]
[TD]Driver 2[/TD]
[TD]54.82%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Driver 3[/TD]
[TD][/TD]
[TD]39.49%[/TD]
[TD]51.81%[/TD]
[TD]60.10%[/TD]
[/TR]
[TR]
[TD]Driver 4[/TD]
[TD]50.55%[/TD]
[TD][/TD]
[TD][/TD]
[TD]55.41%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Please can anybody help.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 143009]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]HEIGHEST[/TD]
[TD="align: center"]LOWEST[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,1),Pivot!B:B,0))[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,1),Pivot!B:B,0))[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,2),Pivot!B:B,0))[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,2),Pivot!B:B,0))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,3),Pivot!B:B,0))[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,3),Pivot!B:B,0))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,4),Pivot!B:B,0))[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,4),Pivot!B:B,0))[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,5),Pivot!B:B,0))[/TD]
[TD]=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,5),Pivot!B:B,0))[/TD]
[/TR]
</tbody>[/TABLE]

Pivot!B:B refers to month 04 only.
You may refer to ranking cells in BIG() and SMALL() functions instead of numbers.

 

[marvin jones]

Hi Flashbond,

Thanks for your reply.

I'm getting a #NAME ? error.

=INDEX('Bradford - Data'!V8:V1000,MATCH(BIG('Bradford - Data'!W8:W1000,1),'Bradford - Data'!W8:W1000,0))

The pivot is in the Bradford - Data tab and the first name starts at V8 and the first value starts at W8.

Have i typed the formula incorrectly?

Thank you.

 

[Legacy 143009]

Hi Flashbond,

Thanks for your reply.

I'm getting a #NAME ? error.

=INDEX('Bradford - Data'!V8:V1000,MATCH(BIG('Bradford - Data'!W8:W1000,1),'Bradford - Data'!W8:W1000,0))

The pivot is in the Bradford - Data tab and the first name starts at V8 and the first value starts at W8.

Have i typed the formula incorrectly?

Thank you.

Aahaha ) Please replace BIG with LARGE

Sorry my bad

 

[marvin jones]

Brill - that worked just fine - thank you so much!

Is it OK to use small still?

 

[Legacy 143009]

Brill - that worked just fine - thank you so much!

Is it OK to use small still?

Yess... I see no reason not to work

 

[marvin jones]

Hi Again,

The formula which looks at the pivot table to find the smallest value and return the driver name: =INDEX('Bradford - Data'!$V:$V,MATCH(SMALL('Bradford - Data'!$W:$W,3),'Bradford - Data'!$W:$W,0)) is returning an odd value.

The name being returned is of a driver who did not work that month and so has no value. Is this formula assuming no value is the lowest value and therefore returning this driver? Is there a way the small formula above can search by a value of 0% and higher?

Thanks,