Formula For Points Based On Race Position

Dave33 · Aug 31st 2008

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

**PCI** · Aug 31st 2008

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

MaximS · Aug 31st 2008

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)

Code

Function POINTS(Position As Range, Member As Range, Karts As Range)
If Member = "Yes" Then
    Select Case Position
        Case 1
            x = 200
        Case 2
            x = 175
        Case 3
            x = 155
        Case 4
            x = 140
        Case 5
            x = 130
        Case 6
            x = 120
        Case 7
            x = 110
        Case 8
            x = 100
        Case 9
            x = 90
        Case 10
            x = 80
    End Select
 
    POINTS = x + Karts
 
    Else
    
    POINTS = ""


End If
End Function

Display More

If you need any help just ask

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

Dave33 · Aug 31st 2008

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

Sicarii · Aug 31st 2008

Re: Developing A Formula

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

MaximS · Aug 31st 2008

Re: Developing A Formula

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

Dave33 · Aug 31st 2008

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

**PCI** · Aug 31st 2008

Re: Developing A Formula

Thank you Dave for closing the thread, thank Sicarii.

Dave33 · Aug 31st 2008

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

shg · Aug 31st 2008

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]

Dave33 · Aug 31st 2008

Re: Developing A Formula

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

Dave

**PCI** · Aug 31st 2008

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

Dave33 · Aug 31st 2008

Re: Developing A Formula

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

Formula For Points Based On Race Position

Participate now!

Share