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:

04 05 06 07 Driver 1 60.05% 52.01% 61.31% Driver 2 54.82% Driver 3 39.49% 51.81% 60.10% Driver 4 50.55% 55.41% <tbody> </tbody>
Please can anybody help.

<tbody>
</tbody>

 

[Legacy 143009]

	HEIGHEST	LOWEST
1	=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,1),Pivot!B:B,0))	=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,1),Pivot!B:B,0))
2	=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,2),Pivot!B:B,0))	=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,2),Pivot!B:B,0))
3	=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,3),Pivot!B:B,0))	=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,3),Pivot!B:B,0))
4	=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,4),Pivot!B:B,0))	=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,4),Pivot!B:B,0))
5	=INDEX(Pivot!A:A,MATCH(BIG(Pivot!B:B,5),Pivot!B:B,0))	=INDEX(Pivot!A:A,MATCH(SMALL(Pivot!B:B,5),Pivot!B:B,0))

<tbody>
</tbody>

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,