1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Need Excel help with a simple formula for Football Pool

Discussion in 'Business Applications' started by 5DoubleO, Sep 22, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. 5DoubleO

    5DoubleO Thread Starter

    Joined:
    Apr 21, 2003
    Messages:
    111
    Hello All,

    Thank for taking the time.

    I have a simple question.

    I am organizing a football pool and I need help with a certain formula.

    Players are awarded points for their statistics throughout the football season.

    I have a simple formula for that. But......

    Players are also awarded bonus points for how many yards they amass in a game.

    Example:

    QuarterBack Passing Yards per game

    300-349 Yards Passing = 5 Points
    350-399 Yards Passing = 10 Bonus Points
    400-449 Yards Passing = 15 Bonus Points
    450-499 Yards Passing = 20 Bonus Points
    500 + Yards Passing = 30 Bonus Points

    I have set up a spreadsheet that tracks all the players stats on a game by game status.

    Is there a formula which allows me to set a value for the number of bonus points allowed depending on the value of a cell or cells?
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    at a first glance you could just do a Vlookup
    300 - 5
    349 - 5
    350 - 10
    399 - 10
    400 - 15
    449 - 15
    450 -20
    499 -20

    set a table up with the two columns
    then look uo the yards for the player - ie 430 - it would look up and find the nearest value which would give 15

    use the approx and next highest - thats why i have the limits for each bonus in the table - anything in between will go to the highest value
     
  3. 5DoubleO

    5DoubleO Thread Starter

    Joined:
    Apr 21, 2003
    Messages:
    111
    Thanks for the reply.

    I really don't think I understand what to do.

    Could you explain again but in a much simpler fashion.

    Pardon my Excel ignorance.

    Thanks again
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    Ok
    have a look at the spreadsheet attached

    sheet1 - has the data
    colA = name
    ColB = the yards accumulated
    ColC = uses alookup from sheet ydtable to work out the bonus value
     

    Attached Files:

  5. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    I've attached a sample of what etaf was talking about. Play with it a bit--especially the Yards column. I'll explain a bit about the function if you'd like.

    HTH

    chris.

    [edit]
    I guess I ought to explain anyway, for future readers. The important function is in Column D, the Bonus column. I used the VLOOKUP worksheet function (you can search for that in Excel's help files). The basic function is

    =VLOOKUP(lookup value, lookup range, return column, lookup type)

    Lookup value is the value you're interested in searching for; in your particular case it is the number of yards gained by a particular player. Since each player will presumably gain a different number of yards, instead of writing a specific VLOOKUP for each player, I put the number of yards in a column and use the cell reference as the lookup value.

    Lookup range is the range of cells that you want to match your lookup value against; in your case it's the number of bonus points per yardage earned. You'll notice in the function this range has a bunch of $ operators in it. These lock the range so that you can copy and paste the function all over the worksheet and still point to these exact same cells. You could even put these cells in another sheet and change the lookup range in the VLOOKUP function to [SheetName]![Range]. One possible example might be =VLOOKUP(lookup value, Sheet2!$A$1:$B$15, return column, lookup type).

    Return column is simply the column in the lookup range that has the values you want to see. In etaf's example, the bonus point values are in the second column, so the return column is 2.

    Lookup type determines whether VLOOKUP tries to find the exact value or the next closest (approximate) value. If you wanted to find the exact value, you'd need a row for every possible yardage between 0 and your upper limit...so at least 500 rows. etaf's way is much easier (with the minor oversight of not having listed 0 bonus points for the range below 300, which I amended in my sample). Because VLOOKUP defaults to an approximate lookup, we can omit lookup type and still have a working function.

    I hope that makes it a little clearer, if you have any questions (or if this doesn't sort your problem) let us know please.
    [/edit]
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    cristobal03 - snap - :) both doung it at same time offline :)
     
  7. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    haha! oh well. boring day at work.
     
  8. 5DoubleO

    5DoubleO Thread Starter

    Joined:
    Apr 21, 2003
    Messages:
    111
    First and foremost.........

    Thank You all. The examples given are great. I understand it so much more when I see it as opposed to reading what to do.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/401213

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice