Excel - Multiple Criteria Testing (feat. J. P. Maltais ModelOff Finalist) - Episode 1614
2,387 views · Published 10 December 2012 · 3:39 · Indexed 22 October 2026
Channel: MrExcel.com · 2012 · Education
Microsoft Excel Tutorial: Multiple Criteria Testing the easy way! Find the Records that meet the given criteria. Welcome to another episode of the MrExcel netcast! I'm Bill Jelen and we are still here in New York City for the ModelOff World Financial Competition. This event brings together the 16 brightest Excel experts from around the world to showcase their amazing skills and tricks. Today, we have J.P. Maltais from Canada, who will be sharing his cool trick for Multiple Criteria Testing. J.P. Maltais hails from the French part of Quebec City and he heard about the competition through a friend's website. Intrigued by the challenge, he decided to participate and we are glad he did! J.P.'s trick involves using conditional formatting and the fact that Excel recognizes the numbers 1 and 0 as logical criteria. This allows for quick and easy testing of multiple conditions. To make the numbers 1 and 0 more user-friendly, J.P. uses a custom number format to display them as "yes" and "no" respectively. He then demonstrates his trick using a simple spreadsheet with three data sets - Mark, Sam, and Mary. By multiplying the numbers and using the IF function, J.P. is able to test for a condition and display a result of "all is well" or "better improve". This trick can be applied to a large number of criteria, making it useful for complex scenarios. J.P. shares how he used this trick in a real-life scenario involving a hundred planes and multiple conditions. By using this method, he was able to keep track of the conditions for each plane and make informed decisions. This just goes to show the practical application of Excel skills in various industries. To recap, J.P.'s trick involves using conditional formatting and the numbers 1 and 0 to test for multiple criteria. He also shares a custom number format to make the results more user-friendly. This is a great trick for anyone dealing with complex scenarios and multiple conditions. Thank you, J.P., for sharing your expertise with us and thank you for tuning in to another episode of the MrExcel netcast. See you next time! 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) Introduction to ModelOff World Financial Competition (00:15) J.P. Maltais presents Multiple Criteria Testing trick (01:24) Explanation of the trick and demonstration (02:23) Real-life application of the trick (03:04) Recap and conclusion by Bill Jelen (03:29) 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: Aircraft leasing transaction in Excel Custom number format in Excel Excel conditional formatting Excel formula for testing conditions Excel tricks for testing multiple criteria Multiple criteria testing Testing conditions in Excel Tracking data in Excel Using conditional formatting for data analysis Using IF function in Excel Using numbers 1 and 0 as logical criteria in Excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153429/ Bill 'MrExcel' Jelen: [Almost] Live From New York City to judge the ModelOff Worldwide Excel Championship! With 16 Excel Rock Stars, from around the Globe, in NYC - and Bill having access to them all - why not do some Podcasting OnSite! Today, Multiple Criteria Testing the easy way from JP Maltais! Find the Records that meet the given criteria; in Episode #1614 JP Maltais shows us how its done. ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/ ...This blog is the video podcast companion to the book, 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" Visit us: MrExcel.com for all of your Microsoft Excel Needs!
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