Upload

Loading icon Loading...

This video is unavailable.

Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates

Sign in to YouTube

Sign in with your Google Account (YouTube, Google+, Gmail, Orkut, Picasa, or Chrome) to like ExcelIsFun's video.

Sign in to YouTube

Sign in with your Google Account (YouTube, Google+, Gmail, Orkut, Picasa, or Chrome) to dislike ExcelIsFun's video.

Sign in to YouTube

Sign in with your Google Account (YouTube, Google+, Gmail, Orkut, Picasa, or Chrome) to add ExcelIsFun's video to your playlist.

Published on Aug 24, 2012

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)

  • Category

  • License

    Standard YouTube License

Loading icon Loading...

Loading icon Loading...

Loading icon Loading...

The interactive transcript could not be loaded.

Loading icon Loading...

Loading icon Loading...

Ratings have been disabled for this video.
Rating is available when the video has been rented.
This feature is not available right now. Please try again later.

Loading icon Loading...

Advertisement
Loading...
Working...
to add this to Watch Later

Add to