CHOOSE Question

snowsurfer · Nov 21, 2005

I was helped in other posts create a formula that evaluates your finish in a race and assigns points based on finish. Well I have run into a road block

We award points down to 20th place. I calculate it using the following code

Code:

=IF(N43="","",CHOOSE(N43,25,22,20,18,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1))

That works great. We also assign BUMP points that tells us when a racer needs to be moved to the next level of competition. You get 3 points for 1st, 2 for 2nd, and 3 for third. When I evaulate the finish and they got 4th or higher than the #VALUE is returned. This is the code I am using. How can I hide the #VALUE error if they get 4th or higher?

Code:

=IF(N43="","",CHOOSE(N43,3,2,1))

Should I just extend the code out like this

Code:

=IF(N43="","",CHOOSE(N43,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0))

We a

Nimrod · Nov 21, 2005

Code:

=IF(AND(N43>0,N43<4),CHOOSE(N43,3,2,1),"")

OR...

Code:

=IF(N43="","",IF(AND(N43>0,N43<4),CHOOSE(N43,3,2,1),0))

snowsurfer · Nov 21, 2005

Thanks that worked. Is it possible to take it a step further. I mentioned in my first post that you get 3 for st, 2 for 2nd, 1 for 3rd. This is not true when less than 5 enter an event. If less than 5 enter the event you get half points. 1.5 for 1st, 1 for 2nd, .5 for third.

Can I take your formula and and a IF statement that adds up the finish to determine if there are more than 4 kids.

Something like

Code:

=IF(SUM(W6:W13)<=10,TRUE,FALSE)

Of course True would then take the finish of the rider and figure out hal points, false would see that there were enough riders for full points and would then calculate full points

test2.xls