View original (French)

Need help creating a formula in Excel 2024

Gripette1973 Posted messages 25 Registration date   Status Member Last intervention   -  
Le Pingou Posted messages 12284 Registration date   Status Contributor Last intervention   -

The official names will be entered in each of the 12 teams of 5 players under the 'Scoresheet' tab.
The 'To sort' tab will be the tab that contains the formulas to output the top player scores.
I will need to sort it first each week since it will be divided into 3 categories (which will vary according to the week's scores).
There will be a total of 60 players divided into 3 categories of 20 players each.

The 3 categories are
- A-Net and A-Handicap (the league's top 20 averages)
- B-Net and B-Handicap (the 21st to 40th best averages in the league)
- C-Net and C-Handicap (the remaining 41st to 60th averages in the league)


Function of the formula I need:
In each category, I must take the top 6 Net scores across the total of the 3 games and also the top 6 Handicap scores across the total of the 3 games
(net score + additional pins awarded based on the average)
However, the system must extract the 1st Net score, then the 1st Handicap score, then the 2nd Net score, then the 2nd Handicap score, and so on up to the 6th
and this for each of the distinct categories
In addition, if there is a tie between two Net or Handicap scores, the formula must move the player into Net ahead of Handicap by taking the highest single score,
and the 2nd single and the 3rd single.

The A category must have its 12 scores (1 net, 1 handicap, in alternating order)
The B category must have its 12 scores (1 net, 1 handicap, in alternating order)
The C category must have its 12 scores (1 net, 1 handicap, in alternating order)

I hope I explained everything well. Here is the file for reference
For now, the only scores that come out correctly are in A
I am unable to get the formula to correctly extract the data in B and C
I am a bit desperate, my Excel understanding is limited

(Column F allows me to place the 'To sort' tab in alphabetical order by team, if needed)
(Column G allows me to place the 'To sort' tab in descending order of the best averages to place players into the correct A-B-C sections, each week)


Thank you so much for your help

https://docs.google.com/spreadsheets/d/1RWQwCsIPRihiA4b8B2fwoRZ8jkBOynbv/edit?usp=sharing&ouid=109844524517319414038&rtpof=true&sd=true

4 answers

  1.
    Le Pingou Posted messages 12284 Registration date   Status Contributor Last intervention   1 480
     

    Hello,

    Sorry, it is impossible to access the file...!

    Regards.
    Le Pingou

    0
  2. Le Pingou Posted messages 12284 Registration date   Status Contributor Last intervention   1 480
     

    Hello,

    A question: are you sure that the ranking calculations are correct in the columns (L to BS) of the sheet A TRIER...!

    Best regards.
    Le Pingou

    0
    1. Gripette1973 Posted messages 25 Registration date   Status Member Last intervention  
       

      Hello, it might not be, I’ve tried some things but it doesn’t work, I’m not comfortable enough with Excel

      0
  3. Le Pingou Posted messages 12284 Registration date   Status Contributor Last intervention   1 480
     

    Hello,

    Thank you, I am absent this Saturday; I will resume your request on Sunday.

    Patience

    Best regards.
    Le Pingou

    0
  4. Le Pingou Posted messages 12284 Registration date   Status Contributor Last intervention   1 480
     
    Hello,

    Here is my proposal.

    From what I understood, I corrected the formulas (on sheet A TRIER) for the Rang (total net and handicap) 1 to 6 of the range ($L$22:$AM$41). Then I extracted the 6 best scores for B-Net and B-Handicap (range BU18:CA31).

    Note: the values in column BW (HDC) seemed incorrect to me, I adapted the formulas.

    Additionally, see a small correction on sheet Matchplay (line 52).

    Please review to ensure all data meet the requirements.

    Thanks for the feedback... !

    Your file: https://www.swisstransfer.com/d/f4c60d33-ff99-41f5-afbf-4f09cd3fed2f

    Salutations.
    Le Pingou
    0