Forum Discussion

pevenden's avatar
pevenden
Copper Contributor
Nov 12, 2023

create custom formula

I would like to create this custom formula to calculate a points awarding system that automatically calculates. P and A are the variables in the formula. Is this possible? P = event finishing...
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Nov 23, 2023

pevenden as per the new information provided by JoeUser2004 regarding "the system includes your best five regatta finishes", the formula to generate Season Standings in my above response can be modified to sum the results of each team's best 5 finishes as follows:

 

=LET(
u, UNIQUE(C4:C20),
a, BYROW(u, LAMBDA(r, SUM(TAKE(SORT(FILTER(D4:D20, C4:C20=r)), -5)))),
HSTACK(SEQUENCE(ROWS(u)), SORT(HSTACK(u, a), 2, -1)))

 

Please see the attached updated workbook (v2).

pevenden's avatar
pevenden
Copper Contributor
Nov 23, 2023
Thanks all.
I have Office Professional Plus 2019
! am not worried about the 'top 5 finishes', I am using this for a different sport 'Horse competition' where each 'class' can have anywhere from 3 to 15 riders.
the 'class' competes 6-8 times per season, and the number of riders vary each time. The season points will include all events. Also, I am not worried about a 'fleet factor'
djclements if I alter B2 to a different number instead of 20, say 10, all i get is this: #NAME? in all the cells...
  • djclements's avatar
    djclements
    Silver Contributor
    Nov 23, 2023

    pevenden No worries. The functions I used in the those files will only work with MS365. The main formula that can still be used in Office 2019 for calculating points is:

     

    =(p*(1-((50*a+4800)/98))+((a*(50*a+4800))/98)-1)/(a-1)

     

    ...where p is the Position, a is the Attendance, and the Fleet Factor has been omitted.

     

    I've attached another workbook (v3) that should work with Office 2019. In this version, I used Excel Table formatting, with calculated columns for Attendance and Points. The formula used to calculate Attendance is:

     

    =COUNTIF([Race No.], [@[Race No.]])

     

    And the formula used to calculate Points is:

     

    =([@Position]*(1-((50*[@Attendance]+4800)/98))+(([@Attendance]*(50*[@Attendance]+4800))/98)-1)/([@Attendance]-1)

     

    Note: I've excluded the IFERROR function here, as you have stated the minimum number of riders in any given race is 3, so the above formula will return the #DIV/0! error when only 1 rider has been entered in the table for a given race.

     

    Add new data to the bottom of the table by inputting values in the Row No., Position and Jockey columns. Data Validation was used in the Jockey column to select from a list. The source for the pick-list is the Jockey column from the Season Standings table.. adding new Jockey names to this table will make them appear in the pick-list.

     

    After inputting new data, sort the Season Standings table by the Total Points column from largest to smallest to refresh the standings.

     

    Calculate Points and Standings

    • pevenden's avatar
      pevenden
      Copper Contributor
      Nov 24, 2023
      djclements That looks great!
      I will play with a bit (I've created a new 'test' sheet so I don't mess up your formulas... )
      and I think it will do the job perfectly...

      thanks everyone for your help in sorting this out...

Resources