View original (French)

Need your help with Excel spreadsheet

Solved
Tichoupi Posted messages 19 Status Member -  
Tichoupi Posted messages 19 Status Member -
Hello,

I need to create an Excel file for the running of a show jumping competition, which should allow me to generate a ranking based on both points and time on the clock. I have no idea what formulas to use... So I'm turning to you!

I look forward to your replies.
Thank you in advance.

A.
Configuration: Windows Vista Internet Explorer 7.0

16 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    That's very kind, but how do you assign a grade based on time and points?

    Without specific information, nobody will be able to help you...

    As "we don't do it for you" but rather provide help on specific points, you can include an excerpt from your binder and a mock-up of what you would like at
    https://www.cjoint.com/
    and paste the provided link in your message

    --
    Best regards, Michel
    0
  2. Tichoupi Posted messages 19 Status Member
     
    Michel, that's very kind, but a slightly less aggressive tone seems more appropriate... If you don't really want to respond, no one is forcing you to do so... Also, being a novice on the forum, I would have preferred explanations rather than aggression!

    Anyway, I don't want to assign a score based on time and points but a ranking. That is to say, in a jumping competition, the one with the fewest points and the best time (that is, the lowest) wins, so the one who gets a higher time or number of points will be second and so on... What blocks me are the formulas to put in F (attached table)...

    https://www.cjoint.com/?jtqec73pSq

    That's it,
    Thank you for your responses.
    0
  3. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Sure!
    So to get back to the initial subject, how does it work on paper and pencil, that is to say without a computer?
    Please send us a copy of the ranking, from your club or another, or from the Federation of Equestrian Sports.
    What the forum members can do is help you formulate the rules of counting, scoring, and ranking. But these rules are indeed for you to provide!
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
    1. Tichoupi Posted messages 19 Status Member
       
      There are no fixed rules, in fact, there are only criteria that will later establish the ranking. In the events organized by my stable, the two criteria taken into account are the time and the number of points obtained (points in show jumping competitions are counted as penalties, meaning each knocked down bar gives 4 points to the rider, the 2nd one gives him another 4 points which makes a total of 8 points, and so on. Also, a time limit is set for each course, let's say 1 minute; if the rider exceeds this minute, he will also be penalized by 1/4 point for each additional second. I don't know if I have been clear enough...). So in my table, I need one (or more) formula(s) that could take these criteria into account. In my table, the columns are:

      A1 B1 C1 D1 E1 F1
      Name First Name Horse Points Time Ranking

      Should I add another column for the time overrun points? Do you see a solution?
      Thank you for your response. I am available to provide you with any additional information or explanations.

      A.
      0
  4. gilou
     
    Hello
    well in excel there are functions for that RANK or LARGE already there you are less in the void now you have to determine the ranking criteria for example: the 1st best time (you will have to use SMALL) and the most points (after addition) this will be (LARGE). Once you have ranked your values you can create a ranking using another function: VLOOKUP; which will search for this value based on the value searched for; it looks complex but in reality it’s not hard; the key is to already "sort" at the beginning
    see you later
    0
    1. Tichoupi Posted messages 19 Status Member
       
      Indeed, it seems complex to me... I don't understand where I should put these functions... And how to write them (is it like the IF function = (;;;))? I must seem a bit silly to you... Thank you anyway for taking the time to respond to me.

      A.
      0
  6. gilou
     
    re
    click on this link it's a kind of template that will give you an idea of which functions to use +
    how; click around in the tables and try to link causes and effects normally you will notice that if you use a value from a cell, it will give that, because I used this or that function; but as you have been advised you need to make a "battle plan" on paper so that the path is clear; after that the functions do the rest (as long as they are well supplied)
    http://www.cijoint.fr/cjlink.php?file=cj200909/cijfLr9JeN.xls
    don't worry
    0
  7. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello.

    Here is a proposal:
    Excel 2003 ► https://www.cjoint.com/?jvnCdZxWo1
    Excel 2007 ► https://www.cjoint.com/?jvnDlfXbfb
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
    1. Tichoupi Posted messages 19 Status Member
       
      Raymond,

      I'm working on your table and I've just noticed a small issue that I would like to resolve... Indeed, the ranking is based solely on the total penalties, whereas I would like it to also be based on time since currently, a person who scored 4 points in 00:00:58 and another person who scored 4 points in 00:00:59 end up in the same position, whereas in reality the second case should be "behind" the first since they took 1 second longer... How can we fix this?
      0
  8. Tichoupi Posted messages 19 Status Member
     
    Thank you both very much for taking the time to help me! I will look into your suggestions, and I'll give you the final table :)

    Thanks again.
    0
  9. gilou
     
    I don't understand where I should put these functions... And how to write them (is it like the IF function =(;;;) ?).
    Indeed, you select a cell, go to fx, there are plenty of functions; you find the one that suits you and off you go: there are some that are easy to understand and others that are harder, that's to get you started.
    0
    1. Tichoupi Posted messages 19 Status Member
       
      Gylou,

      I'm also working on your Fx trick and I'm really struggling with using the VLOOKUP function...
      0
  10. Tichoupi Posted messages 19 Status Member
     
    Hello everyone,

    With the kind help of Raymond and Gilou, I was able to make progress on my table, but I am encountering quite a few obstacles due to my ignorance regarding certain formulas...
    Here is the table in question:

    https://www.cjoint.com/?jxpBxXTDas

    As you may have seen (or not) in the previous message, I told Raymond that the ranking was only based on column G (Total penalties), but this column needs to be associated with the time achieved in order to have a more accurate ranking... Unfortunately, I do not know how to do that...

    Also, I would like the ranking to reappear more clearly (to make our task easier during the competition) in the right table by displaying the name of the rider, their first name, and that of their horse...

    I am not sure if I was clear enough... I am available for any further clarifications!

    Thank you in advance for your help.

    A.
    0
  11. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello, tichoupi.

    You will notice that I worked from the indications you provided, which suggested that penalties were only calculated after exceeding the allotted time; therefore, everyone who took less than the reference time and didn't knock down any obstacles were tied.

    With the clarifications you provided, I believe I understand that we should first rank by time, then break any ties by taking into account the penalties, which are those due to knocked-down obstacles plus the penalties for exceeding the time limit. Is that right?
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
  12. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    And is that better for you?
    https://www.cjoint.com/?jxtHZekIl2
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
  13. Tichoupi Posted messages 19 Status Member
     
    Yes, of course, and thank you for paying attention to all my requests!

    However, I may have explained it poorly because, in fact, it’s the time that will break any ties. The number of points determines the position and the time breaks the ties... How can I correct that?
    On the other hand, the second table is perfect!

    Thanks again for your help! You’re really a pro.
    0
    1. gilou
       
      Hello, hello RAYMOND PENTIER
      I had kind of dropped the subject but since I see it's coming up again, I'll add my contribution. There will certainly need to be some changes regarding the chronological bonuses. I hope I won't confuse you; if that's the case, just scratch it.
      0
      1. gilou > gilou
         
        I had forgotten the link
        http://www.cijoint.fr/cjlink.php?file=cj200909/cij3a5Hgx6.xls
        0
      2. Tichoupi Posted messages 19 Status Member > gilou
         
        Thank you for taking the time to respond to me, really thank you :).
        0
  14. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Ah !
    I slightly modified the times by adding 1 minute everywhere, and I allocated points and a time to Karim, who was at zero.
    So Denise, Henri, and Julie have 12 penalty points. To differentiate them, we take their times:
    Henri (1 min 59 s) will be ranked before Julie (2 min 5 s), who will be ranked before Denise (2 min 7 s)?
    But what does the penalty for exceeding the allotted time bring?
    By adding it to the penalties for obstacles touched, we indeed have
    * for Denise ►12+1.75=13.75 pts
    * for Henri .-►12+0.00=12.00 pts
    * for Julie .--►12+1.25=13.25 pts
    which does not change the ranking already established!
    For this time excess penalty to have an impact on the ranking by points and time, it would require that, for example, Karim with 8 penalty points has a delay of more than 16 seconds compared to Denise, with 12 points, to surpass her in total penalties and end up ranked after her. Did I get it all right this time?
    And I thank you for giving me the opportunity to engage in an interesting exercise ...
    https://www.cjoint.com/?jydjzwhOUS
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
  15. Tichoupi Posted messages 19 Status Member
     
    Dear Raymond,

    It seems that everything is really perfect, however, to close the subject, I just have one little question:

    I plan to make a sheet (Excel) for each event (we have 5). Is a copy/paste enough or do I need to redo all the formulas from the previous sheet?

    Thank you so much for all the answers you have provided me; you have been a tremendous help!
    Thanks again.

    A.
    0
  16. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Well hello.

    Glad to hear it satisfies you.

    If you are using separate files (with Save As), no problem.
    If you are using multiple sheets in the same file (with Move or Copy/Create a Copy), you'll need to adapt because of the name given to cell L2:
    * either in column G you replace TpsRéf with $L$2,
    * or you redefine the name TpsRéf in each sheet (with Insert/Name/Define),
    this to allow you to have different reference times for each competition.
    --
    Retirement is nice! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
  17. Tichoupi Posted messages 19 Status Member
     
    Great, I'll try to do all of that! Again, a huge thank you, I'll share the final table with you!

    See you soon

    A.
    0