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!
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
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
http://geocities.com/aaronblood/ExamplesLogic.html