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

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

Not open for further replies.

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. ### etafModerator

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

Joined:
Apr 21, 2003
Messages:
111

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. ### etafModerator

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

File size:
17.5 KB
Views:
210
5. ### 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.

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]

File size:
17.5 KB
Views:
146
6. ### etafModerator

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

7. ### cristobal03

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

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.

As Seen On