If funtion help

[fetzer]

Hello,

I am in a racing league and have a page in my spreadsheet which wil
distribute points to a driver depending upon where he/she has finishe
in the race.

As an example, the driver who finishes in the 1st postion receives 2
points, 2nd position receives 17 points, etc. The list contains a tota
of twelve drivers. My intention was to enter the finishing position o
each driver in a particular cell, and in another cell the formula wil
calculate the appropriate points. I have attempted to use the "If
function, as in IF(L22="1","21", If(, etc: However, a well know
limitation to Excel is that you cannot "nest" more than 7 function
with this statement.

I have used the CHOOSE function, which works nicely as when th
finishing position is entered, the formula will assign the driver th
appropriate number of points. However, I am still running into a
issue. Each cell that has the CHOOSE funtion looks like this: ######.
It's onlly until I enter the finishing position for the driver that i
will return the points awarded.

The last column of the spreadsheet there is a "Total Points" colum
which will add the total number of points for each driver throughou
the race season. The cells in that column look like this: $VALUE! I
is only until I enter the finishing position for the driver for ever
race that the Total Points column will change from $VALUE! to, say
total points of 75. I would like the Total Points column to sho
accumulated points after every race. With this funtion you have t
finish every race in a season to show the total number of points fo
each driver

I have noticed on a number of websites, such as this that the VLOOKU
function may be an option, but I am uncertain on how to use this for m
spreadsheet. This is probably very confusing and have worded this th
best that I can being a beginner to the program. Any help would b
much appreciated. Thank you

 

[Don Guillett]

try
=IF(ISNUMBER(C3),CHOOSE(C3,21,22),"")

 

[Max]

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

 

[RagDyeR]

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!
==============================================



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!

 

[fetzer]

I posted an thread yesterday regarding an If function problem. I wa
the one working on a racing league spreadsheet. With your help an
suggestions, that page in the spreadsheet now works wonderfully.

This spreadsheet is quite elaborate(for me, anyway) in that it include
about 12 different pages of information ragarding our racing league
Points system, Awards system, Damage costs, etc. I have actuall
incorporated all of your suggestions onto my spreadsheet and I canno
tell you how pleased I am with the results.

Thank you very much for the quick replies and help. This i
information which obviously has corrected my problem and will b
information that I can benefit from in the future.

Thank you and best regards,

Doug (fetzer

 

[Ragdyer]

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.