Trying to Figure Out how to Tackle Bowling Ties

[RudeBoy]

Currently I have an elaborate set of workbooks coded up for my Bowling League.
The main headache I face Seasonally is TIES. I have different categories that pay-out different amounts of Prize money.. I use Formula Ranking code as such: =RANK(M9, $M$9:$M$13319) When a tie occurs it is usually double or triple or quadruple sometimes.

Lets say as an example we have 10 places to pay and i am creating 10 Labels to pay these spots off.. After the bowling is done we have 4 bowlers tied for 1st place.. The Ranking Formula will kick out all 4 Bowlers with a Ranking of 1. So you have in the Spread-sheet N9=1,N10=1,N11=1 & N12=1. Now what happens is each place has a money value so lets keep it simple 1st place it $1000 2nd place is $900 3rd place is $800 and $700 is for 4th place.. Normally when we have a tie we combine the 4 spots $1000 thru $700 and then divide by 4. So $1700 divided by 4 = $425 each. Normally we have to do all this by hand and identify all these ties after we print the labels etc.. I want to be able to have EXCEL identify these ties upfront no matter how many there are and also figure out how much to divide to each bowler and have my labels kicked out and ready to print as soon as I get done entering all the scores.. I figure I maybe able to use this type of formula that I used in my Fantasy Football Sheets =INDEX('BYES-DIV'!E2:E33,MATCH(E8,'BYES-DIV'!C2:C33,0))
HELPppppppppppppppppppppppppppppppp

 

[T. Valko]

Currently I have an elaborate set of workbooks coded up for my Bowling League.
The main headache I face Seasonally is TIES. I have different categories that pay-out different amounts of Prize money.. I use Formula Ranking code as such: =RANK(M9, $M$9:$M$13319) When a tie occurs it is usually double or triple or quadruple sometimes.

Lets say as an example we have 10 places to pay and i am creating 10 Labels to pay these spots off.. After the bowling is done we have 4 bowlers tied for 1st place.. The Ranking Formula will kick out all 4 Bowlers with a Ranking of 1. So you have in the Spread-sheet N9=1,N10=1,N11=1 & N12=1. Now what happens is each place has a money value so lets keep it simple 1st place it $1000 2nd place is $900 3rd place is $800 and $700 is for 4th place.. Normally when we have a tie we combine the 4 spots $1000 thru $700 and then divide by 4. So $1700 divided by 4 = $425 each. Normally we have to do all this by hand and identify all these ties after we print the labels etc.. I want to be able to have EXCEL identify these ties upfront no matter how many there are and also figure out how much to divide to each bowler and have my labels kicked out and ready to print as soon as I get done entering all the scores.. I figure I maybe able to use this type of formula that I used in my Fantasy Football Sheets =INDEX('BYES-DIV'!E2:E33,MATCH(E8,'BYES-DIV'!C2:C33,0))
HELPppppppppppppppppppppppppppppppp

See if this helps:

http://www.mrexcel.com/forum/showpost.php?p=2678388&postcount=2

 

[shg]

I think you need to extend col I with some zeros to accommodate ties for last place ...

 

[RudeBoy]

Okay I created that.. But I get #N/A a value is not available to the formula or function

I had my Rankings from N9 to N29 via this type formula = =RANK(M9, $M$9:$M$13319)
Then I had in R9 to R29 the payout amounts 140, 120, 106, 96, 92, 88, 84, 80, 76, 72, 70,
68, 66, 64, 60, 58, 56, 54, 52, 50 and 40

In column S9 I have the Rankings again but I just did a =N9 to N29 to copy the rankings from my other column and i entered this Formula in column O9 and copied it down to O29 =AVERAGE(OFFSET(R$9,MATCH(N9,R$9:S$29)-1,,COUNTIF(N$9:N$29,N9)))

and I get a return of #N/A a value is not available to the formula or function?

Helpppppppppppppppppppppppppppppp

 

[T. Valko]

Okay I created that.. But I get #N/A a value is not available to the formula or function

I had my Rankings from N9 to N29 via this type formula = =RANK(M9, $M$9:$M$13319)
Then I had in R9 to R29 the payout amounts 140, 120, 106, 96, 92, 88, 84, 80, 76, 72, 70,
68, 66, 64, 60, 58, 56, 54, 52, 50 and 40

In column S9 I have the Rankings again but I just did a =N9 to N29 to copy the rankings from my other column and i entered this Formula in column O9 and copied it down to O29 =AVERAGE(OFFSET(R$9,MATCH(N9,R$9:S$29)-1,,COUNTIF(N$9:N$29,N9)))

and I get a return of #N/A a value is not available to the formula or function?

Helpppppppppppppppppppppppppppppp

That red highlighted section of the formula...

That has to be a 1 dimensional array (single row or single column).

 

[RudeBoy]

Ok thanks I fixed that and bam it came out.. =AVERAGE(OFFSET(R$9,MATCH(N9,S$9:S$29)-1,,COUNTIF(N$9:N$29,N9)))

Okay I got data now but It is not accurate so im not sure what needs to be tweaked??

I have in N12 & N13 a tie Rank of 4 for Fourth place. So basically 4th & 5th place tied.. The money amounts over in R12 is $96 for 4th place and for 5th place money it is R13 and it is $92. So the way we break the tie is we add 4th place $96 + 5th Place $92 which gives us $188 and we divide by the number of tied spots in this case 2 and the total for each should be $94.. The amount that is being kicked out for this formula is $90 each.. so we are close..
O12 has this =AVERAGE(OFFSET(R$9,MATCH(N12,S$9:S$29)-1,,COUNTIF(N$9:N$29,N12)))
O13 has this =AVERAGE(OFFSET(R$9,MATCH(N13,S$9:S$29)-1,,COUNTIF(N$9:N$29,N13)))

Helpppppp Let me know what im missing but we are close.. Thanks..

 

[T. Valko]

Ok thanks I fixed that and bam it came out.. =AVERAGE(OFFSET(R$9,MATCH(N9,S$9:S$29)-1,,COUNTIF(N$9:N$29,N9)))

Okay I got data now but It is not accurate so im not sure what needs to be tweaked??

I have in N12 & N13 a tie Rank of 4 for Fourth place. So basically 4th & 5th place tied.. The money amounts over in R12 is $96 for 4th place and for 5th place money it is R13 and it is $92. So the way we break the tie is we add 4th place $96 + 5th Place $92 which gives us $188 and we divide by the number of tied spots in this case 2 and the total for each should be $94.. The amount that is being kicked out for this formula is $90 each.. so we are close..
O12 has this =AVERAGE(OFFSET(R$9,MATCH(N12,S$9:S$29)-1,,COUNTIF(N$9:N$29,N12)))
O13 has this =AVERAGE(OFFSET(R$9,MATCH(N13,S$9:S$29)-1,,COUNTIF(N$9:N$29,N13)))

Helpppppp Let me know what im missing but we are close.. Thanks..

Here's a small sample file that demonstrates this.

Prize money distribution.xls 16kb

http://cjoint.com/?BEwc0MeTlcC

Try that out and see if you can adapt it to your layout/data.

 

[RudeBoy]

Okay I went back thru and found the problem.. My Ranking in Column H was not hard-coded but was pulling the ranking from Column C. So I HARD Coded the Rankings in the H Column 1, 2, 3, 4, 5, 6, 7, 8, 9 etc and that worked it now calculates correctly.. AWESOME Now I can build this into all my other Prize categories and rework my labels and I will be GOOD.. Thank you..

 

[T. Valko]

Okay I went back thru and found the problem.. My Ranking in Column H was not hard-coded but was pulling the ranking from Column C. So I HARD Coded the Rankings in the H Column 1, 2, 3, 4, 5, 6, 7, 8, 9 etc and that worked it now calculates correctly.. AWESOME Now I can build this into all my other Prize categories and rework my labels and I will be GOOD.. Thank you..

You shouldn't have to hard code the ranks.

Can you post a SMALL sample file that demonstrates the problem you're having? Don't need 1000's of rows of data. Just a "few" will do.

 

[RudeBoy]

If I wanted to create 1st Place, 2nd Place 3rd Place etc.. Is there an easy way to match the Ranks and turn that into a value of 1st Place, 2nd Place etc? So example if C1 = 1, "1st Place" etc but there are multiple positions, so what is the easiest way to translate the current Ranking to a value of 1st Place etc without messing up the formulas?

Helppppppppp

Thanks.. Let me know..