Excel - Numbers Greater than 500K to New Range: Episode 1672
1,849 views · Published 22 March 2013 · 6:48 · Indexed 28 October 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Copy all records over $500K to new range. Welcome back to another exciting episode of the Dueling Excel podcast! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be tackling a question sent in by Dandy: "Is there a formula that can pull values greater than 500,000 to a new range?" Get ready to see some amazing array formulas and VBA code as we dive into this challenge. Bill will be using his VBA skills to create a new function that will return an array of values greater than the specified limit. He will be passing two things to the function: the range of values and the limit. The results will be stored in a temporary array called "GetOverValues." Bill will also be using a clever trick to ensure that the array has enough space to hold all the values, even if the limit is set to zero. He will also be giving a shoutout to Excel MVPs Chip Pearson and Bill Manville for their helpful posts that aided in the creation of this function. Mike, on the other hand, will be using a different approach. He will be setting up a hurdle in a cell and using the COUNTIF function to count the number of values greater than the specified limit. He will also be using the LARGE function to sort the values in descending order. Mike will be using a clever trick with the ROWS function to increment the sorting formula as he copies it down. He will also be giving a shoutout to Bill Jelen for his amazing VBA skills. In the end, both Bill and Mike will have come up with impressive solutions to the challenge. Bill's VBA function will return an array of values greater than the specified limit, while Mike's formula will sort the values in descending order without the need for an array formula. Which solution will come out on top? You'll have to watch the video to find out! Don't forget to leave a comment in the YouTube comments if you have any suggestions or tips for improving these solutions. Thanks for tuning in to another exciting episode of the Dueling Excel podcast! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Pull numbers greater than 500,000 to a new range with a formula. (00:14) Question from Dandy: "Is there a formula to pull values 500,000 to a new range?" (00:24) Bill's VBA solution using a temporary array. (01:02) Mike's solution using the COUNTIF and LARGE functions. (02:45) Testing and limitations of Mike's solution. (03:19) Mike's shoutouts to Excel MVPs Chip Pearson and Bill Manville. (03:29) Bill's final thoughts on Mike's solution. (06:37) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Array formula for pulling values greater than 500,000 Comparing values in Excel using the greater than operator Creating an expandable range in Excel using the ROWS function Excel formula to pull numbers greater than 500,000 to a new range Naming a range in Excel Sorting formulas for numbers in Excel Specifying a large enough range for array formulas Transposing array formula in Excel Using the COUNTIF function to count values greater than a certain limit Using the IF function with a logical test in Excel Using the LARGE function to extract largest values in Excel VBA function to return an array of values Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153494/ Mike and Bill have a question from Dandy: "Is there a Formula that would pull values over 500,000 to a New Range?" Bill addresses the solution using VBA with a few modifications and Mike takes the Formula route. Follow along with Mike and Bill to see how to get the Range in Episode #1672. Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] http://www.mrexcel.com/slayingdragonsebook2011.html and "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html "The Learn Excel from MrExcel Podcast Series" MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920