Excel Magic Trick 810: RANKIF Function RANK IF: Ranking With Conditions
Vložit
- čas přidán 26. 09. 2011
- Download Excel Start File people.highline.edu/mgirvin/Y...
Download file: people.highline.edu/mgirvin/E...
1. There is no such thing as RANKIF Function, but you can use SUMPRODUCT to RANK with Conditions or Criteria
2. See how to rank studnt's scores by department (like Marketing, Accounting, Finance, etc.)
3. Arrays of TRUE and FALSE converted to 1 0 ones and zeroes using Double Negative - Věda a technologie
An 8 year old video that came in handy in 2019. I'd love a Rankif function, but this works too! Plus, the double-negative trick now makes sense to me.
Finally I found a clear and simple explanation on how to use sumproduct to rank records taking into consideration their "group". In fact, using the examples I was able to find out how to look for more then on criteria and rank the records based on their row position using row(). Thanks a lot!
Thanks Mike. This solved a multiple criteria lookup problem that I was facing . I got two birds with one magic trick! Cheers!
This is terrific. I'm really getting a lot out of your videos!
Glad they help!
@demouram , I am glad that the video helps!!!
You are welcome!
Oh yes, this is great help!!! Thanks for making these available!!!
Exactly what I was looking for. Easy to follow and implement. Thanks!
Cool! I am glad that this helped!
Genius. This is exactly what I needed. Thank you!
@kw42chan , you are welcome!
This is awesome. Thanks for sharing !
Thanks for this wonderful video
Wow - who figured this out? Thanks for posting and thanks for posting under RANKIF. It allowed me to find it.
This is an outstanding tutorial - really enjoyed your style and it helped my problem
I am glad that it helps!
Thank you so much ❤
When I try it in the above example it does work. I am not sure what is happening with your formula.
Switch less than symbol to greater than.
Try: =SUMPRODUCT(-- (C2=$C$2:$C$91),--(B2 **** $B$2:$B$91))+1
**** means type greater than symbol
Godlike!
Thanks for another awesome video. Let's say these are golf scores and you want the lower number to be #1 (vs where you want the higher number to be #1). It is easy with Rank. How would you do that with the sumproduct?
brilliant - thanks
I am looking to rank a bunch of values then write a formula based on the rank value. So for example (Rank 1 * Rank 2) / Rank 3 . So the position of rank 1, 2, 3 etc will change across the sheet but no matter what I need the lowest rank to be the denominator (for example). Clear as mud in a beer bottle I know but hopefully you or someone else might be able to help.
Hmmm, this may solve my Rank with multiple conditions issue. Thanks Mike..
It's very useful function but i have problem. In your example there are two "marketing" with 3 rank (duplicates). I need extra column with absence and if there are duplicated ranks then better is with less absences.
See this video title:
Excel Magic Trick 707: RANK with Criteria - SUMPRODUCT
how would you ignore null values if the department column had blanks? because adding the 1 would rank everything with a null value as 1 therefore throwing off the rank. is there a way to wrap a condition around the sumproduct?
I need to do ranking for families based on family size (from 1 to 9) and the family monthly income there is a chart for manual ranking has multiple rows each for the size of the family for ranking from 1 to 30.
How can I do it on excel? can you help
video helps a lot, we can learn numerous tricks on youtube! thanks @excellsfun
THANKS
Hi, I love your videos they have helped me with several conundrums.
Nevertheless, now I have encountered a challenge that I have not been able to solve. I need to rank a database with multiple criteria that sometimes displays the same value within the ranking range e.g. one customer has bought the same amount of eight different products with the same profitability and no other products. When I try to rank based on profitability per customer with sumproduct I get the ranking eight for this specific customer because all the eight product has the same profitability value. When I use rank.eq and the countifs function, as you show in “Excel magic trick1354…” it does not work and returns values greater than the number of products the customer has acquired. I also tried the countifs as a standalone function which worked for most of my dataset but not for the entire dataset which is very frustrating because I don’t understand why it is returning an incorrect value for some of our customers but not all.
Unfortunately, I cannot sort my dataset.
In a perfect world, my wish is to rank the dataset based on customer and profitability without duplicate ranking number ranging from 1 to X. However, I could live with duplicate rankings.
The database is currently consisting of 50 000 rows and is growing day by day.
I’m keeping my fingers crossed that you have a brilliant idea how to solve this issue!
Kind regards,
Johanna
How would you perform a rank with an "or" function. For example, in Column A are dates. Jan 28th, Jan 29th...etc. Column B, Away Games. Column C, Home Games. I'd like to rank (based on date) the Chicago Blackhawks games. Sometimes they appear in the away game column, sometimes in the home game column.
Its so cool.
Glad it is cool for you : )
I noticed in the situation where a department has a tie. It will still assign a value of 1 and then 2. How can you get this to show the same rank for duplicates?
Love your videos. In this example, how do i change the top rank (#1) to be the smallest number, not the largest number on the list?
Change the "" in [array2]
How come I can't get this to work on excel 2013?
@krn14242 , cool!!
What if there are 2 or more the same score and I don't want to skip ranks? e.g. 2 people in Fiance scored 100, and 1 person scored 99, I want the rank to be (1, 1, 2 ) instead of (1, 1, 3). Thanks!
did you find out?
i have the same problem here. Anybody found out?
Pls help to eliminate duplicate ranks
Sir, would you kindly make a tutorial on this example through Power Query? But, if there is a Tie, then both should give the same Rank. For Example, If 3 out of 5 or 6 PolyScience Score 60 then, all 3 of them should give the same Rank. How should I do this through Power Query? I know, you're busy with other tutorials. But, still, please Sir
This didn't rank the top score for each category. It just ranked the scores for the overall list.
+imVNUTZ it ranked categories... you can see there are duplicates (11,14)