Prize Payout Calculation

[johnrlalor]

I have been working on a competition spreadsheet and have one item which is still causing me problems.
The competition is made up of 2 categories plus an overall prize winner.
What I am trying to acheive is that if a particular golfer wins the overall prize, the 2nd place golfer in his/her category wins the 1st place category prize as in the example below:

The overall winner can come from either category, all I really need is a formula which will reference column D to define the positions in column F, any help is appreciated.

	Category	Pts	Pos	True Category Pos	Cat Pos for prize calculation
Player 1	1	40	5	3	2
Player 2	1	50	3	2	1
Player 3	1	30	8	5	4
Player 4	1	70	1	1	-
Player 5	1	40	5	3	2
Player 6	2	20	9	4	4
Player 7	2	20	10	5	5
Player 8	2	60	2	1	1
Player 9	2	40	5	3	3
Player 10	2	50	3	2	2

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

 

[steve the fish]

You don't mention your excel version but if 2010 or later try:

=IF(E2="","",IF(AGGREGATE(15,6,1/($B$2:$B$12=B2)*$D$2:$D$12,1)=1,IF(E2-1=0,"-",E2-1),E2))

 

[johnrlalor]

Hi Steve,
That does exactly what I want. Can you explain what the aggregate (15,6,1 is doing as I may need to increase/decrease the participants in each category.
Thanks
John

 

[steve the fish]

It is doing a calculation using the function SMALL (this is the 15) ignoring errors (the 6). The 1/($B$2:$B$12=B2) produces an error if any of the cells in B2:B12 don't equal B2 and a 1 if they do. This error is then ignored (back to the 6). The one is multiplied by D2:D12 to produce a series of numbers. SMALL acts on these numbers to produce the smallest number (,1 at the end). If it equals 1 we know the overall winner is within our category.