Recalculating Prize List when ties occur

**elevdown** · 03-31-2008, 09:43 AM

The following example is a listing of the number of people that placed in the top 6 positions. There is a tie for 3rd with 3 people. I need a formula in column "C" that will distribute 3rd, 4th & 5th place prizes equally among the 3 people. (Assume column "D" has the prizes for 1st place thru 6th place)
Note: the final desired result in column "C" is:
1st place = D2 = $100
2nd place = D3 + $50
3rd place = D4+D5+D6 divided by B4 = $13.33
4th place = 0 = $0
5th place = 0 = $0
6th place = D7 = $2

A B C D
1 Place #People Prize Prize List
2 1 1 $100
3 2 1 $50
4 3 3 $25
5 4 0 $10
6 5 0 $5
7 6 1 $2

Note that the value in column "B"has been calculated using the "Rank" function. Thanks in advance for your help!

**darkyam** · 03-31-2008, 10:03 AM

I can't really see the layout in my head from what you have above, but you can try =IF(B2=0,D1,SUM(OFFSET(C2,0,0,B2,1))/B2), if Place is in A, # of people in that place is in B, and Prize for that place is in C.

**elevdown** · 03-31-2008, 01:21 PM

Yes, this appears to work like I want if I change "D1" to "0" which allows me to put a value of zero in the Prize column if the number of people = 0.
(I obviously need to read up on the OFFSET function)
Thanks again!

**darkyam** · 03-31-2008, 02:29 PM

If there is no way 1st place can possibly not be won, then you can just use the formula starting with SUM(... for the first row. You can also leave D1 blank and it will evaluate to 0 if it is possible that no one would win 1st place.
The formula for Offset takes five arguments: cell to start at, number of rows to offset by, number of columns to offset by, number of rows in the range, number of columns in the range. So in this formula, it starts at C2, does not offset by any rows or columns, takes the number in B2 as the number of rows to include in the range, and makes the range 1 column wide. Offset is usually used for making dynamic named ranges. If you want to create a list of all names in column A and will be adding to that list, for example, you can create a named range called "Names" and have it refer to =Offset(Sheet1!$A$1,0,0,CountA(A:A),1) and the list will grow as you add to it.