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


    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.

  • 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


    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]

Participate now!

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