|
If funtion help
Hello, I am in a racing league and have a page in my spreadsheet which will distribute points to a driver depending upon where he/she has finished in the race. As an example, the driver who finishes in the 1st postion receives 21 points, 2nd position receives 17 points, etc. The list contains a total of twelve drivers. My intention was to enter the finishing position of each driver in a particular cell, and in another cell the formula will calculate the appropriate points. I have attempted to use the "If" function, as in IF(L22="1","21", If(, etc: However, a well known limitation to Excel is that you cannot "nest" more than 7 functions with this statement. I have used the CHOOSE function, which works nicely as when the finishing position is entered, the formula will assign the driver the appropriate number of points. However, I am still running into an issue. Each cell that has the CHOOSE funtion looks like this: ######. It's onlly until I enter the finishing position for the driver that it will return the points awarded. The last column of the spreadsheet there is a "Total Points" column which will add the total number of points for each driver throughout the race season. The cells in that column look like this: $VALUE! It is only until I enter the finishing position for the driver for every race that the Total Points column will change from $VALUE! to, say a total points of 75. I would like the Total Points column to show accumulated points after every race. With this funtion you have to finish every race in a season to show the total number of points for each driver I have noticed on a number of websites, such as this that the VLOOKUP function may be an option, but I am uncertain on how to use this for my spreadsheet. This is probably very confusing and have worded this the best that I can being a beginner to the program. Any help would be much appreciated. Thank you! -- fetzerPosted from http://www.pcreview.co.uk/ newsgroup access |
.. The last column of the spreadsheet there is a "Total Points" column
which will add the total number of points for each driver throughout the race season. The cells in that column look like this: $VALUE! For the "Total Points" column, Try using SUM() e.g.: =SUM(A1:E1) instead of: = A1+B1+C1+D1+E1 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Evaluating 12 positions would probably be better approached with a data list
and a lookup formula. For example, place your list in an out-of-the-way section of your sheet, say Y1 to Z12. Y1 to Y12 containing the finishing positions 1 to 12, And Z1 to Z12 containing the points awarded per position. Then try this formula: =IF(ISNA(MATCH(L22,$Y$1:$Y$12,0)),"",LOOKUP(L22,$Y $1:$Y$12,$Z$1:$Z$12)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "fetzer" <fetzer.1rjsvz@ wrote in message ... Hello, I am in a racing league and have a page in my spreadsheet which will distribute points to a driver depending upon where he/she has finished in the race. As an example, the driver who finishes in the 1st postion receives 21 points, 2nd position receives 17 points, etc. The list contains a total of twelve drivers. My intention was to enter the finishing position of each driver in a particular cell, and in another cell the formula will calculate the appropriate points. I have attempted to use the "If" function, as in IF(L22="1","21", If(, etc: However, a well known limitation to Excel is that you cannot "nest" more than 7 functions with this statement. I have used the CHOOSE function, which works nicely as when the finishing position is entered, the formula will assign the driver the appropriate number of points. However, I am still running into an issue. Each cell that has the CHOOSE funtion looks like this: ######. It's onlly until I enter the finishing position for the driver that it will return the points awarded. The last column of the spreadsheet there is a "Total Points" column which will add the total number of points for each driver throughout the race season. The cells in that column look like this: $VALUE! It is only until I enter the finishing position for the driver for every race that the Total Points column will change from $VALUE! to, say a total points of 75. I would like the Total Points column to show accumulated points after every race. With this funtion you have to finish every race in a season to show the total number of points for each driver I have noticed on a number of websites, such as this that the VLOOKUP function may be an option, but I am uncertain on how to use this for my spreadsheet. This is probably very confusing and have worded this the best that I can being a beginner to the program. Any help would be much appreciated. Thank you! -- fetzerPosted from http://www.pcreview.co.uk/ newsgroup access |
I posted an thread yesterday regarding an If function problem. I was the one working on a racing league spreadsheet. With your help and suggestions, that page in the spreadsheet now works wonderfully. This spreadsheet is quite elaborate(for me, anyway) in that it includes about 12 different pages of information ragarding our racing league; Points system, Awards system, Damage costs, etc. I have actually incorporated all of your suggestions onto my spreadsheet and I cannot tell you how pleased I am with the results. Thank you very much for the quick replies and help. This is information which obviously has corrected my problem and will be information that I can benefit from in the future. Thank you and best regards, Doug (fetzer) -- fetzerPosted from http://www.pcreview.co.uk/ newsgroup access |
Appreciate the feed-back.
And for future reference, this reply of yours to *this original thread*, is more apt to be seen and appreciated by the respondents, since they automatically know when replies are posted to their messages. Your copy of this post, with which you started a new thread, has a good chance of *not* being seen by the people to whom you intended it to be. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "fetzer" <fetzer.1rlmpz@ wrote in message ... I posted an thread yesterday regarding an If function problem. I was the one working on a racing league spreadsheet. With your help and suggestions, that page in the spreadsheet now works wonderfully. This spreadsheet is quite elaborate(for me, anyway) in that it includes about 12 different pages of information ragarding our racing league; Points system, Awards system, Damage costs, etc. I have actually incorporated all of your suggestions onto my spreadsheet and I cannot tell you how pleased I am with the results. Thank you very much for the quick replies and help. This is information which obviously has corrected my problem and will be information that I can benefit from in the future. Thank you and best regards, Doug (fetzer) -- fetzerPosted from http://www.pcreview.co.uk/ newsgroup access |
| All times are GMT +1. The time now is 09:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com