View original (French)

Tied ranking

Solved
Zonteo Posted messages 111 Status Member -  
 kamalkay -
Good evening.
I am looking for the formula that allows me to find the rank with ties in the following form:
Example: 3rd ex
I know how to proceed to find 3rd, but my difficulty lies in the last element which is "ex" (ex-aequo).
I tried using data validation or conditional formatting, but I couldn't succeed.
Hoping for a favorable response to my request, I send you my best wishes for the year 2019.
Here is the link to my sample file.
https://www.cjoint.com/c/HLFqSgPFR1L

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello Zonteo

    Use this formula:
    =RANK(C2;$C$2:$C$10;0) & IF(COUNTIF($C$2:$C$10;C2)>1;" ex";"")

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    3
    1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
       
      we ran into each other ...
      Happy New Year 2019
      0
    2. Zonteo Posted messages 111 Status Member 1
       
      Great, it works. But it gives the result in the form of 3 examples.
      I will try to modify it so that it gives me the result I expect, which is the 3rd example.
      Thank you for the lead. If I can't manage it, I'll get back to you.
      Best regards.
      0
    3. kamalkay
       

      thank you very much it worked very well

      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Good evening
    what exactly do you want to do
    if you use the formula:
    =RANK(C2;$C$2:$C$10) on the height of your data, you will get the rank of ties, but of course since there are two firsts and two sixths, there is neither second nor seventh
    so please specify what you expect
    looking forward to hearing from you
    best regards

    0
    1. Zonteo Posted messages 111 Status Member 1
       
      Great, that works.
      But it's giving the result in the form of 3 examples.
      I will try to modify it so that it gives me the result I expect, which is the 3rd example.
      Sincerely, thank you for the lead. If I can't manage it, I will get back to you.
      Best regards.
      0
  3. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
     
    Hello,

    With: 
    =RANK(C2;$C$2:$C$10) & IF(COUNTIF($C$2:$C$10;C2)>1;" ex";"")


    --
    Best regards
    Patrice
    0
    1. Zonteo Posted messages 111 Status Member 1
       
      Good evening
      Great, it works.
      But it gives the result in the form of 3 examples.
      I will try to modify it so that it gives me the result I expect, which is the 3rd example.
      I would like to thank you for the suggestion. If I can't do it, I'll get back to you.
      Sincerely.
      0
    2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
        
      =RANK(C2,$C$2:$C$10) & IF(COUNTIF($C$2:$C$10,C2)>1,"ex","e")
      0
    3. Zonteo Posted messages 111 Status Member 1
       
      Good evening.
      I don't even have a voice or words to thank you.
      I have one last favor to ask you.
      I would like to know how to translate this formula into an Access query?
      It is the formula you just sent me, which is as follows:
      =RANK(C2,$C$2:$C$10) & IF(COUNTIF($C$2:$C$10,C2)>1,"ex","")
      I would like it, if possible with your agreement, in an Access query.
      Happy New Year 2019.
      Thank you.
      0