help with IF formula
Posted by Pat on August 15, 2000 6:29 PM
Can anyone help with writing the correct formula to accomplish the following...
I am trying to develop a spreadsheet in Excel to track fantasy football statistics. Each player scores points based on their performance each week. The better they do the more points they score. I would like to develop a spreadsheet where I can enter the players statistics and have excel compute their score.
An example is:
QB1 - 100 yards - 8 points
QB2 - 300 yards - 15 points
I cannot figure out the correct formula to accomplish this. I have tried nesting IF statements how ever excel only allows 7 nested IF statements and I need up to 20.
Can anyone help please. I am going nuts trying to figure this out.
Tanks
Pat
Posted by Scott on August 16, 0100 7:12 AM
Pat:
Give this a try - it may work faster.
Start with a section in your workbook that contains two adjacent columns of data - one for the yardage and the second for the points. For example:
Yardage Points
100 8
300 15
etc...
Once you have created all the necessary yardage/point combinations (the 20 you mentioned above), select the range of cells and name them. Ctrl-F3 is the shortcut for the name menu (in my test file I named it scores).
In cell C1 (or any cell you wish) enter in the yardage for a player. In cell D1 (again, anywhere else) enter in this formula:
=VLOOKUP(C1,scores,2,false)
The VLOOKUP command takes the value in cell C1, searches in the first column of the named range and then returns the value in the second column when a match is found (see below).
Yardage Points
100 =VLOOKUP(C1,scores,2,false)
will turn into
Yardage Points
100 8
The error message #N/A means that the yardage value you entered was not found in your named range.
Let me know how it works out!
Posted by Pat on August 16, 0100 8:41 AM
I think the formula you suggested will work. One thing I forgot to mention is that the points each player is awarded is based on a range of numbers. For example, the scoring chart for QB is as follows:
YARDS POINTS
0-29 0
30-59 1
60-89 2
90-119 3
etc.....
If I enter 99 yards in cell C1, I want excel to recognize that 99 is between 90-119 so it would award 3 points. How would I modify the earlier formula you suggested to accomplish this....
Thanks
Pat
Posted by Joe on August 16, 0100 3:02 PM
Pat,
List the minimum yardages for each point total (0,30,60,90 in your example) in the left column and eliminate the "false" from the formula Scott suggested.
Joe
Posted by DAvid on August 15, 0100 10:10 PM
http://geocities.com/aaronblood/ExamplesLogic.html
