Excel Rank Top N Values Inc Tied Values with Dynamic Array Formulas

Поділитися
Вставка
  • Опубліковано 29 жов 2024

КОМЕНТАРІ • 69

  • @IvanCortinas_ES
    @IvanCortinas_ES 4 роки тому +1

    Complex case (with trick), extraordinarily explained. You are one of the brightest people explaining Excel. Thank you very much for sharing Alan!

    • @Computergaga
      @Computergaga  4 роки тому

      Thank you very much, Iván. I am happy it helps.

  • @omarrashid4181
    @omarrashid4181 8 місяців тому

    Finally found what I was looking for. Thank you for a clear and detailed explanation

    • @Computergaga
      @Computergaga  5 місяців тому

      You're very welcome. Happy to help.

  • @miaquaintrelle
    @miaquaintrelle 2 роки тому +1

    Finally found what I was looking for. Thank you for a clear and detailed explanation 😊 🙏🏻

  • @billbandito
    @billbandito Рік тому

    Finding this video was a blessing, nice work and thank you so much!

    • @Computergaga
      @Computergaga  Рік тому

      You're very welcome! Thank you for your comments.

  • @Ryan-zz6hh
    @Ryan-zz6hh 4 роки тому

    Excellent video! Thanks for walking us through the possible errors and for explaining the overall solution clearly. Loving the new dynamic array functions.

    • @Computergaga
      @Computergaga  4 роки тому

      Thank you Ryan. Yes, they are amazing 😜

  • @reibalachandran4775
    @reibalachandran4775 Рік тому

    This video was a life saver! thanks so much! such a cleve solution!

  • @jerbear197
    @jerbear197 Місяць тому

    These tutorial videos should start out by explaining what version of the application they are using. They are on here for years and things change. I found it helpful but it took me a while to figure out that the team function no longer exists in Excel.

    • @Computergaga
      @Computergaga  Місяць тому

      Good to hear that it helped. I'm not sure what function you are referring to. But if it is an Excel function, it will be there. They don't get removed.

    • @jerbear197
      @jerbear197 Місяць тому

      @@Computergaga Issue was at 1:14 in the video. FILTER(Team[Name], will not work in 365. I've had to change to FILTER(Name, Team doesn't even show up in the auto drop down list when you start typing.

  • @caspy1uk241
    @caspy1uk241 2 роки тому

    just what i was looking for.. thx for the well produced video.. so easy to follow... what would you do if you had 2 columns of names and 2 columns of values to find the ranking of both combined ?... maybe a wee addon video to explain ?.. anyways// ill be following you now.. great stuff

  • @rany4799
    @rany4799 2 роки тому

    Another amazing video....I am catching up on all what I missed....love it😍🤩

  • @sachinrv1
    @sachinrv1 4 роки тому

    That's is fabulous, I liked the trick of Length function. Clearly FILTER is versatile function. By the way I was thinking of you might use the old fashioned =IF(ROWS($E$6:E6)>$E$3,"",ROWS($E$6:E6)), but SEQUENCE does it effortlessly. Cheers :)

    • @Computergaga
      @Computergaga  4 роки тому

      Thank you Sachin. It is a lot of fun seeing what these new functions can do. And how much easier they make Excel tasks that were once complicated.

  • @elijahgastineau4432
    @elijahgastineau4432 4 роки тому +1

    What should I do if I want the tied values to each take up a separate ranking in the table? I'm in a situation with 6 tied values and I want to make them each have their own ranking.

  • @anisulhoquechowdhury7873
    @anisulhoquechowdhury7873 2 роки тому

    Great!!!! Football world cup is knocking the door. Create a dynamic fixture where Quarter finalists/semi finalists/finalist will take position automatically after I input score in prior matches...

  • @GentzaBallesteros
    @GentzaBallesteros 4 роки тому

    i can't believe this solves almost the exact same problem i was dealing with. Thanks a lot!

    • @GentzaBallesteros
      @GentzaBallesteros 4 роки тому

      What can be done when the values are in more than 1 column, but the selected column to extract the top values, depend on the day of the week (the columns headers are the days of the week)? Maybe telling excel which column to pick? Thanks in advanceee!

    • @Computergaga
      @Computergaga  4 роки тому

      You're welcome. Thank you.

    • @Computergaga
      @Computergaga  4 роки тому +1

      Sure. This can be done. You can tell Excel what column to pick from a cell value if that works for you. Entering the weekday you are interested in into a cell. The INDIRECT function or a lookup such as MATCH (depending on your exact setup) would then be used to fetch the required column. I have a video here on a similar thing with the SUMIF function - ua-cam.com/video/OM4MAaQ4oN0/v-deo.html

    • @GentzaBallesteros
      @GentzaBallesteros 4 роки тому

      @@Computergaga Thanks for the response! I checked some of your videos and they are very helpful. I would like to make one last question, what if i want the function FILTER (or any other if this request can't be fulfilled with the FILTER function) to differentiate between 2 names with the same value (Bob and Hiran in this case) and list them downward as different elements? cause i need to treat them as different instances. Thanks a lot and have a good day!

    • @Computergaga
      @Computergaga  4 роки тому +1

      There are a variety of techniques. I have a video here using COUNTIF for unique rankings.

  • @yesplease6399
    @yesplease6399 8 місяців тому

    Beautiful video!!!!!! Thankyou!

  • @Seppe144
    @Seppe144 11 місяців тому

    @Computergaga what do i do if i dont want the names bob and hiran combined but just below each other

  • @xMoTiVz
    @xMoTiVz Рік тому

    Thank you very much for this.

  • @sasavienne
    @sasavienne 4 роки тому +1

    First *LIKE* before watching.. Thanks Alan. 👍.. Salim

  • @wayneedmondson1065
    @wayneedmondson1065 3 роки тому

    Cool trick! Thanks Alan :)) Thumbs up!!

  • @cgrablew
    @cgrablew 4 роки тому +1

    I like the video but what if you only wanted the top x for Birmingham? Can you do a filter inside of a filter?

    • @Computergaga
      @Computergaga  4 роки тому

      Oh absolutely. The criteria section would be this - (team[Region]="Birmingham")*(team[Value]=LARGE(team[Value],E6))
      You can check out more about the FILTER function in my video - ua-cam.com/video/kdl3mNEyIRE/v-deo.html

  • @19761999
    @19761999 Рік тому

    Brilliant!

  • @pmsocho
    @pmsocho 4 роки тому

    Great tricks! Thumbs up!

  • @riddhigi
    @riddhigi 2 роки тому

    Complex things explained in simple way, though would like to know how to add condition in this ranking computation like in this example if we want to rank it based on the region, how this can be done in dynamic way

    • @Computergaga
      @Computergaga  2 роки тому +1

      Thanks, Girish. I have a similar video for conditional rankings - ua-cam.com/video/g-L0uvVcEoM/v-deo.html

    • @riddhigi
      @riddhigi 2 роки тому

      Thanks Computergaga❤️

  • @MasTommy21
    @MasTommy21 3 роки тому

    What to do if i want to rank by REGION from your data? So there will be regions with multiple values... and must be summed first

  • @meckruler
    @meckruler 4 роки тому

    Hello,
    Do you know how I can get the formula to work in googlesheets? I tried manually entering your formula and came with issues, I tried downloading your excel and importing it into google sheets and after the upload/conversion, the formula comes out with #ERROR. Is google sheets limited in use of this function?

  • @MasTommy21
    @MasTommy21 3 роки тому

    How can i do that, but for multiple transactions and duplicating items?
    So i have to sum it all first, and then execute your formula. Help?

    • @Computergaga
      @Computergaga  3 роки тому

      I would probably create an intermediary table with the items and SUMIFS. Then run this formula off that range.

  • @Ann-er2yf
    @Ann-er2yf 4 роки тому

    Hi...What if I can't find the FILTER function on my Excel? Is this an add-on?

    • @Computergaga
      @Computergaga  4 роки тому

      Hi Ann, the FILTER function is only available in Excel 365.

  • @funwhitekid4071
    @funwhitekid4071 3 роки тому

    when you select the row it puts "team[value]" in the function how do you do this? when I select a row for an array it just gives me, for example C:C

    • @victoralcantar960
      @victoralcantar960 2 роки тому

      I know it's been a while, but that's because the first array (the one from which you get the ranking) is an Excel object called a Table and like Named Ranges, they have their own syntax for references. Anyway, I reply in case someone else has the same question.

  • @excel_along_the_way
    @excel_along_the_way 4 роки тому +1

    You can do this with one helper column by ranking the values without skipping a rank position. The formula is for this example MATCH(team[@Value],SORT(UNIQUE(team[Value]),,-1),0)

    • @excel_along_the_way
      @excel_along_the_way 4 роки тому

      You can also do this without helper columns by first wrapping you value array in the UNIQUE function and do the LARGE function. Then TRANSPOSE the FILTER function and apply the TEXTJOIN function.

  • @lorenwilliams6666
    @lorenwilliams6666 Рік тому

    How would I do this if I have a list on multiple tabs?

    • @Computergaga
      @Computergaga  Рік тому

      It depends exactly on what you mean by this, but if you're referring to the data source, they would need to be aggregated before we can rank them in a list.
      If you're after multiple ranking lists, then rinse and repeat the same steps for each tab.

  • @rakeshsoni7306
    @rakeshsoni7306 4 роки тому

    Super duper awsome

  • @ezelkarlkl1284
    @ezelkarlkl1284 4 роки тому

    Thank you again !. download link is dead :( Could you fix it ?

    • @Computergaga
      @Computergaga  4 роки тому +1

      My pleasure. Download link is fixed 👍 This is caused by my new website being installed a few weeks ago and a couple of directories have changed.

  • @nooralfa-it7720
    @nooralfa-it7720 2 роки тому

    what can I do if I have no FILTER function?

    • @Computergaga
      @Computergaga  2 роки тому

      There are various other ways to RANK. Another option is to use the RANK.EQ function with COUNTIFS to ensure unique rankings, video here - ua-cam.com/video/QFdsGGlSmrU/v-deo.html
      A lookup formula such as VLOOKUP can then order them with whatever additional detail you want.

  • @amirfirdaus448
    @amirfirdaus448 3 місяці тому

    How you make the top N

    • @Computergaga
      @Computergaga  3 місяці тому +1

      Easiest method: ua-cam.com/users/shortscfgx62N-dQo
      Takes 30 seconds in Excel 365.

  • @planxlsm
    @planxlsm 4 роки тому +2

    4:18 # SPILL Error
    7:30 New Rank
    8:55 Rank Helper Column