Excellent video! Thanks for walking us through the possible errors and for explaining the overall solution clearly. Loving the new dynamic array functions.
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.
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.
@@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.
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
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 :)
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.
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...
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!
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
@@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!
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
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
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?
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.
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)
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.
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.
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.
Complex case (with trick), extraordinarily explained. You are one of the brightest people explaining Excel. Thank you very much for sharing Alan!
Thank you very much, Iván. I am happy it helps.
Finally found what I was looking for. Thank you for a clear and detailed explanation
You're very welcome. Happy to help.
Finally found what I was looking for. Thank you for a clear and detailed explanation 😊 🙏🏻
You're welcome, Mia. Thank you.
Finding this video was a blessing, nice work and thank you so much!
You're very welcome! Thank you for your comments.
Excellent video! Thanks for walking us through the possible errors and for explaining the overall solution clearly. Loving the new dynamic array functions.
Thank you Ryan. Yes, they are amazing 😜
This video was a life saver! thanks so much! such a cleve solution!
You're welcome! Thank you.
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.
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.
@@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.
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
Another amazing video....I am catching up on all what I missed....love it😍🤩
Thank you.
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 :)
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.
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.
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...
Absolutely!!
i can't believe this solves almost the exact same problem i was dealing with. Thanks a lot!
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!
You're welcome. Thank you.
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
@@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!
There are a variety of techniques. I have a video here using COUNTIF for unique rankings.
Beautiful video!!!!!! Thankyou!
My pleasure!
@Computergaga what do i do if i dont want the names bob and hiran combined but just below each other
Thank you very much for this.
You're very welcome!
First *LIKE* before watching.. Thanks Alan. 👍.. Salim
Thank you Salim 😀
Cool trick! Thanks Alan :)) Thumbs up!!
Thanks, Wayne.
I like the video but what if you only wanted the top x for Birmingham? Can you do a filter inside of a filter?
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
Brilliant!
Thank you 😊
Great tricks! Thumbs up!
Thank you.
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
Thanks, Girish. I have a similar video for conditional rankings - ua-cam.com/video/g-L0uvVcEoM/v-deo.html
Thanks Computergaga❤️
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
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?
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?
I would probably create an intermediary table with the items and SUMIFS. Then run this formula off that range.
Hi...What if I can't find the FILTER function on my Excel? Is this an add-on?
Hi Ann, the FILTER function is only available in Excel 365.
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
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.
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)
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.
How would I do this if I have a list on multiple tabs?
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.
Super duper awsome
Thank you Rakesh.
Thank you again !. download link is dead :( Could you fix it ?
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.
what can I do if I have no FILTER function?
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.
How you make the top N
Easiest method: ua-cam.com/users/shortscfgx62N-dQo
Takes 30 seconds in Excel 365.
4:18 # SPILL Error
7:30 New Rank
8:55 Rank Helper Column