Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Can't figure out a formula for Excel.


  • Please log in to reply

#1
magusbuckley

magusbuckley

    Member

  • Member
  • PipPipPip
  • 625 posts
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
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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?
  • 0

#3
magusbuckley

magusbuckley

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 625 posts
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
  • 0

#4
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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?
  • 0

#5
magusbuckley

magusbuckley

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 625 posts
Vino:

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

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

Thanks,

Magus :)
  • 0

#6
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Excellent! :)
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP