Hello:

I may be in the wrong location for this, but I'm trying to create a formula that I can key into Excel. If I know the formula, I can key it in, but I can't figure out what the formula is.

Here is the problem.

At the end of our bowling league, we'll be awarding money to each team. So, team one may get 30 percent of the total pot, team 2 may get 20 percent of the pot, and so on and so forth until we've used up 100 percent of the prize fund.

The number of teams changes each season, so I'd like to have a forumla that allows me to key in the number of teams. By looking at the number of teams, the forumla (I hope), will be able to tell me how much of a percentage each team should be awarded. The spread will be the same between teams. So, if the the last place team is awarded 2% of the money and the spread is 3%, then the next to last place team will be awarded 5%, and the next place team will be awarded 8% and so on and so forth.

Can any of you help me with this forumla?

Here are the figures:

100 = percentage of money to be given out
N = number of teams
S = spread of percentage won from one team to the next

I don't think we need any more numbers, I just can't figure out the formula.

Thanks,

Magus

Not sure I understand what you're asking for...

If you have, say, three teams then they can be 1st, 2nd, and 3rd. Equal this to six parts (a total of the places) so the first team would get 3/6th, the second 2/6ths, and the third 1/6th. That is, 50%, 33%, and 17%.

For, say, eight teams, the same principle applies. The first team would get 8/36th, the second team 7/36th, down to the last team 1/36th. That is, 22% for the first team down to 3% for the last.

So, one way would be =sum((number of teams-place)+1/(total of places)) as a percentage.

Or is this nothing like what you're trying to do?

Vino:

Thanks for the reply.

In your first example, you say the first team would get 50%, the second team would get 33%, and the third team would get 17%. The total for the three numbers is 100% and that is EXACTLY what I'm trying to do.

There is only one part of your formula that I don't understand. You said "Equal this to six parts (a total of the places)". If there are only three teams, wouldn't there only be three places? You could only possibly have 1st, 2nd, and 3rd, right? Where did the six parts come from in this example.

In your second example, eight teams, you use 36 places.

If you'll explain to me where that number is coming from I'll have it.

Thanks,

Magus

Hi

OK... if you imagine 1st, 2nd, and 3rd being awarded points, the winner would get 3, second place 2, and last would get 1.

For eight teams, the first place would get 8 points, the second 7, down to the last place again getting 1.

So, the denominator would be a total of the points available for the number of teams taking part.

With three teams... 3+2+1=6

With eight teams, 8+7+6+5+4+3+2+1=36. The first team would be 8/36, the second 7/36, and so on.

For ten teams, 10+9+8+7+6+5+4+3+2+1=55. The first team would be 10/55, the second 9/55, and so on.

Any clearer?

Vino:

That makes perfect sense. I can take it from here.

Thanks for your help. You've solved my problem.

Thanks,

Magus

Excellent!