If Statement, or am I going about this wrong?

**JJAndradeIII** · 10-09-2008, 10:47 AM

I've been working on this project for racing, where the points are calculated by the finishing order of the competitors. However the points are variable based on the # of competitors running in the event.

Ex.

30 competitors run, winner gets 60, then descends by 2 down to 30th place.
25 competitors run, winner gets 50, then descends by 2 down to 25th place.

So total # of competitors multiplied by 2 gives you the winner's point total.

My question is if there is a formula to determine, if the finishing order spot for competitor A shows 1 it finds the highest number(max formula) in the row, multiple by 2; however, if it does not show one, it needs to be able to calculate accordingly, so if it shows a 2, find max, multiply by 2, then subtract 2, or if it shows a 5, find max, multiply by 2, then subtract 8, etc down to 30. I've tried a nested if statement, but it certainly won't allow 29 nests in it. Is there any other formula I may have overlooked?

I appreciate any help that can be given.

John Andrade' III
[email protected]

**Andy Pope** · 10-09-2008, 11:21 AM

Have a look at the attached.
Assumes you have a table of competitors and finish position in B2:B31.

Their score would be in C2

=(MAX($B$2:$B$31)*2)-((B2-1)*2)

Post back if your result alyout is different.

**JJAndradeIII** · 10-09-2008, 11:25 AM

That's excatly what I've been looking for. I really appreciate this, you have no idea how long I've been looking at the spreadsheet. Again thank you so much.

**JJAndradeIII** · 10-10-2008, 02:21 PM

Ok, so at the beginning it was what I am looking for, however, the part I completely forgot about was the points awarded are a variable to a max, there will be no more than 60 pts awarded to the winner, so 30 competitors is considered a "full" field. Well, more than 30 competitors can start, so is there a way to use a less than in the formula, to where find max under 31?

Thanks again

John Andrade' III
[email protected]

**Andy Pope** · 10-11-2008, 06:49 AM

You will now need to know the size of the Competitor's being scored.
This is for 30 finishing scores. reset get 0

=IF((MIN(MAX($J$2:$J$51),30)*2)-((J2-1)*2)<2,0,(MIN(MAX($J$2:$J$51),30)*2)-((J2-1)*2))