# Formula For Points Based On Race Position

• Hello all. This is my first post and hopefully someone can help me. I am involved with my son in Go-Kart racing and I am in charge of keeping the season points for each participant. I have a spreadsheet I'm trying to develop to help maintain the points. Hopefully I can explain what I'm trying to do.
The way the point system works is you receive a pre-determined amount of points based on where you finish in a race plus the number of participants in the race. Ex: there are ten karts in a race. 1st place receives 200 points plus the number of karts so 200+10=210. Second place receives 175+10=185. What I would like to do is enter the number of karts in a race and then list the name of the participant and where he finished. When I enter the finishing position this automatically calculate their points. Pretty easy so far.
Now here’s the fly in the ointment, only members can receive points. Non members do not but they are counted in with the total number of karts. So there could be 10 participants, 8 members 2 non members. This is where I get stuck. I cannot come up with a layout or formula to get this to work. Below is what I’m looking for.

Driver Name, Finishing position, Member, Points

John Doe .............1.................Yes......210
XXXXX.................2.................Yes......185
XXXXX.................3.................No........0..
XXXXX.................4.................Yes......165
XXXXX.................5.................Yes......150
XXXXX.................6.................Yes......140
XXXXX.................7.................NO.......0..
XXXXX.................8.................NO.......0..
XXXXX.................9.................Yes......130
XXXXX.................10...............Yes......120

Points system
1st 200 pts,
2nd 175 pts
3rd 155
4th 140
5th 130
6th 120
7th 110
8th 100
9th 90
10th 80

In the example above 3rd place was a non-member so 4th place receives 3rd place points.
Hopefully I have explained this in a way someone can help. Thank you all in advance.

Dave

• Re: Developing A Formula

Assuming in K2 to K11 are the initial set of point.
In C2 put :
[frc]
=IF(C2="yes",INDEX(K\$2:K\$11,COUNTIF(C\$2:C2,"yes"))+10,0)
[/frc]
and drag down

## Files

Triumph without peril brings no glory: Just try

• Re: Developing A Formula

Here is simple User Defined Formula:

To use it:

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function examples -
5. Get out of VBA (Press Alt+Q)
6. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

If you need any help just ask

I've attached the file witch few validation lists to prevent errors.

## Files

• Re: Developing A Formula

Thank you all for the replies. I'm not really an advanced user of Excel. I've attached the layout I'm looking to achieve. If you can help me that would be great. Thanks

## Files

• Re: Developing A Formula

Changing PCI's formula to match your alignments...be sure to thank PCI.

## Files

• Re: Developing A Formula

I have tailored my idea to your layout and needs. Please see attachement for details.

## Files

• Re: Developing A Formula

Thank you PCI, MaximS, and Sicarii. I now have two versions that work and this will save me much time as well as being more accurate. I really appreciate it.

Dave

• Re: Developing A Formula

Thank you Dave for closing the thread, thank Sicarii.

Triumph without peril brings no glory: Just try

• Re: Developing A Formula

I spoke too soon. There is a minor issue. The number of karts will be variable which will change the points. My example was 10 karts but it could be anywhere from 3 to 25. I would need to enter the number of karts in a cell to add to the formula. Sorry to be a pain. Thanks

Dave

## Files

• Re: Developing A Formula

In H4 and copy down,

[COLOR="Blue"]=IF(G4="yes", INDEX(B\$17:B\$29, COUNTIF(G\$4:G4,"yes")) + \$C\$14, 0)[/COLOR]

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Developing A Formula

That does it! You guys are amazing. Thanks again.

Dave

• Re: Developing A Formula

Just to put frosting on the cake we could add:
in H4 =IF(G4="yes",INDEX(Point_System,COUNTIF(G\$4:G4,"yes"))+Total_Kart,0)
Where
C14 is named Total_Kart = =COUNTA(C\$4:C13)
C\$17:C\$29 is named Point_System
Vrooommm

## Files

Triumph without peril brings no glory: Just try

• Re: Developing A Formula

I love it! Not only frosting on the cake but with a cherry on top as well. Thanks

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!