Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates
241.5 هزار بار بازدید -
12 سال پیش
-
Download Excel Start File:
Download Excel Start File: https://people.highline.edu/mgirvin/Y...
Download Excel File: http://people.highline.edu/mgirvin/Ex...
Concepts:
1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers.
2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE).
In this Video:
1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges.
2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges.
3. How Come VLOOKUP will not work
4. Find Hurdle Value for Top 5 using LARGE function
5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)
Download Excel File: http://people.highline.edu/mgirvin/Ex...
Concepts:
1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers.
2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE).
In this Video:
1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges.
2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges.
3. How Come VLOOKUP will not work
4. Find Hurdle Value for Top 5 using LARGE function
5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)
12 سال پیش
در تاریخ 1391/06/03 منتشر شده
است.
241,521
بـار بازدید شده