HomeОбразованиеRelated VideosMore From: ExcelIsFun

Excel Dynamic Arrays: FILTER Array Function AND & OR Logical Tests (Excel Magic Trick 1529)

148 ratings | 2397 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1529.xlsx This video teaches all about AND Logical tests and OR Logical Test and the intricacies of how to construct and use them in Excel, including single cell logical Test sand Array Logical tests. It also examines how Excel Functions and Features interprets FALSE and 0 (zero) as FALSE and TRUE and Any Non-Zero Number as a TRUE. Then we take the lessons of Logical tests and Apply them in Various ways in the FILTER ARRAY Function. We also get to see a great trick from Peter Bartholomew about how to create a shorter formula than the one as seen in Excel Magic Trick 1521. We also entertain a question from John Borg about an OR Logical Test inside the FILTER Function. Reference video about FILTER Function EMT 1521: https://www.youtube.com/watch?v=5zRab2Grz7Q Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516): https://www.youtube.com/watch?v=7jJMDGZpjSk
Html code for embedding videos on your blog
Text Comments (58)
Peter Bartholomew (22 hours ago)
Hi Mike. I really enjoyed the video -- but then, I should! I particularly liked the introduction on Boolean operators in Excel. I was going to make some cheeky remark along the lines of 'it's almost like you're a professional teacher' :) but then I saw the post from Richard Hay: "Superb pedagogy in the fist 10 minutes to present AND vs OR -- best I have ever seen on this topic! :) " Guess I should just run with that. I could post a challenge. How would you use MMULT to develop a formula that performs some form of COUNTIFS/SUMIFS but, instead of working on a predetermined number of fields, both the array and the criterion range are dynamic and can be changed without requiring additional parameters in the aggregation formula. What reminded me of it was that it requires a further step in the journey down 'Boolean Way'.
Peter Bartholomew (20 hours ago)
+ExcelIsFun I have sent a couple of files via LinkedIn. Centuries ago I believe refined young ladies used to demonstrate their needlework technique by producing samplers. I seem to have gone through a similar phase a few years ago with Excel, only lacking the refinement!
ExcelIsFun (21 hours ago)
What is your entry into the challenge, Peter?
ExcelIsFun (21 hours ago)
I am a teacher, Peter. I have taught in the Business Department at Highline college for 16 years, and before that I traveled the world teaching people about Boomerangs : ) Although, maybe "cheerleader" is a more correct term, than using "teacher" : )
John Borg (2 days ago)
Thanks Mike. Nice of you to consider my question. :) :) Amazing!!!! Proud to be your student in excel!!!!!
ExcelIsFun (2 days ago)
Go Team!!!!!
Leila Gharani (3 days ago)
Thank you Mike! So many wonderful possibilities with the filter function :)
ExcelIsFun (22 hours ago)
You are welcome, Teammate! I am trying to get back to the MSPTDA class, but these AMAZING Array Formula keep getting in my way... but it is fun : )
RRR program (3 days ago)
Another fantastic video.. Thanks
ExcelIsFun (3 days ago)
You are welcome, RRR!!!
M. SZ. (3 days ago)
Thank you, Mike. The 365 only made me think about the software situation in private businesses and the public sector. (not in the US) What I have seen: they have Office 2007, and have no intention to upgrade even if there may be a good offer from Microsoft, and several advantages of the new features. Once I was asked to downgrade from 2010 to 2007 (this happened in 2016) so as not to be different. Of course, many features work fine in 2007, no problem, but the advanced features that help efficient data management are far more sophisticated in the advanced versions. Now I have 2013, and I do not downgrade. (sorry for being a bit off-topic)
excel is fun (3 days ago)
Yes, it is a problem in some entities, like your work and mine, where they will not allow us to have the latest version, even though the entity would be greatly benefited with the new tools... Thanks for watching, M. SZ.!!!
Vida (3 days ago)
Great video with neat formulas
excel is fun (3 days ago)
Glad it was great and neat! Thanks, Vida : )
JOYDEB CHAKRABARTY (4 days ago)
Thank You very Much sir...
excel is fun (3 days ago)
You are very welcome, JOYDEB!!!
SuperRayrobinson (4 days ago)
Hi Mike , need some help on an excel spreadsheet , its probably really basic for you but complicated for me , can you help please?
excel is fun (3 days ago)
For back and forth dialog to get Excel solutions try this great Excel Question site: mrexcel.com/forum
Shabbir Hussain (4 days ago)
Sir, Please help me to create a macro for my little database. I have already mentioned all my ORDER # in column-A and all my items names in Row-1 in SHEET2. I have an ORDER FORM created on SHEET1, I want to create a macro which will shift all entries from my FORM to SHEET2 DATABASE. Thanks and Regards
excel is fun (3 days ago)
For back and forth dialog to get Excel solutions try this great Excel Question site: mrexcel.com/forum
Ecole Sed (4 days ago)
I love this page!! It helps a lot!! i dont know if you have a video showing what i REALLY need to do at work and no google search is helping, i work at a school with 1200 student. i have one specimen grades table in exel and a list of names, i need to copy and paste each name into the cell that says name in the specimen for 1200 student!! im sure theres a way to do that more efficiently and i was hoping youd help me with it or point me to the video where u show how to do it. any help would be much much appreciated. Keep up the good work!
ExcelIsFun (4 days ago)
I think you can use VLOOKUP. Try this video: https://www.youtube.com/watch?v=LdhLAa-Vqmg Also, for back and forth dialog to get specific Excel answers, try this great Excel Question site: mrexcel.com/forum
Mohamed Chakroun (4 days ago)
Awsome excel team directed by a briliant Mike Girvin...Excel is full of secret
ExcelIsFun (4 days ago)
Yes, I so agree: Excel is full of secrets : ) Thanks for watching and for your support, Mohamed!!!
Thanks Mike for this EXCELlent video.
ExcelIsFun (4 days ago)
You are welcome, Syed!!! So glad you get to hang out and have fun with Excel : )
Wayne Edmondson (4 days ago)
Nice Mike, Peter and John.. thanks for sharing your wisdom and wizardry with array functions and AND and OR logical tests. Can't wait to get the functionality so that I can test and practice your methods and techniques. Thumbs up!
ExcelIsFun (4 days ago)
Thanks for the Thumbs Up and Support, Wayne! The AND and OR Logcial Test stuff can be used in the new Array Functions, and all the old excel features such as Formulas, Functions, Array Formulas and Data Analysis : )
Rob Michaels (4 days ago)
Thank you for explaining so clearly how to use multiplication and addition logical tests in the new FILTER functions. I'm glad I have Office Insider so I can try all your Dynamic Array magic tricks!
ExcelIsFun (4 days ago)
All that AND and OR Logical test stuff can be used everywhere in Excel: Formulas, Functions, data Analysis and so much more : ) Thanks for watching and for your support, Rob!
Richard Hay (4 days ago)
Another outstanding video!! Superb pedagogy in the fist 10 minutes to present AND vs OR--best I have ever seen on this topic! :)
ExcelIsFun (4 days ago)
Glad you like the AND and OR - since it is so much of what we do in Data Analysis... Thanks for your support, Richard.
Unirotovibe (4 days ago)
Hi Mike, amazing video as always ! In the first example, you should also populate the "if_empty" part of the formula, because if there's no match for both criteria, ie. Google and Sioux, you'll get an error
ExcelIsFun (4 days ago)
Great idea, Unirotovibe!!! Thanks for the Uni-roto-VIBE : )
K B (4 days ago)
great video. Thanks Mike
ExcelIsFun (4 days ago)
You are welcome for the video, K B!!! Thanks for watching and having fun : )
Rico S (4 days ago)
Those formulas are so pleasing to the eye. Good work sir.
ExcelIsFun (4 days ago)
Yes, they are, especially the FILTER(RangeOfNumbers,RangeOfNumbers) to get all the non-zero numbers : ) Thanks for the support, Rico S!!!
Russell Martin (4 days ago)
you just helped me in my calculus class as well. THANK YOU!
Russell Martin (4 days ago)
+ExcelIsFun I am not so sure that Taylor expansions are fun. LOL
ExcelIsFun (4 days ago)
Calculus is fun!!!! Glad the videos help, Russel. You are welcome and thanks for your support : )
rf05mjy (5 days ago)
With the new Dynamic Array enabled Excel, I noticed some of my old files were added SINGLE( ) in the formulas. I remember a while ago you told a joke about DATE( )… maybe it's about time to renew that joke, by adding DATE( ), SINGLE( )...etc...For jokes that only Excel Pro will laugh...
ExcelIsFun (4 days ago)
100% TRUE!!!! I think the formula is like this: DATE(SINGLE("Your Name"),"Where You What To Go", "Who You Want To Date")
Jeremy McMahan (5 days ago)
It's a great time to an Excel nerd. Thanks!
ExcelIsFun (4 days ago)
Go Excel Nerds!!!! Thanks foe the support, Jeremy!!!
edgie da pogi (5 days ago)
Thanks Mike... another Amazing video...Can't wait to have this version... my life would be easy with it :)
ExcelIsFun (4 days ago)
Yes, this new Excel Calc Engine DOES make things easier : ) Thanks for the support, edgie!!
Doug H (5 days ago)
Nice! Filter Arrays are awesome
ExcelIsFun (4 days ago)
Awesome for sure, Teammate Doug!!!!
Chris Mancinelli (5 days ago)
Wow, totally amazing Mike!!
ExcelIsFun (4 days ago)
Glad it was amazing, Chris!
Hussein Korish (5 days ago)
Thanks Mike .... i think there is a whole world of "Reporting" coming ahead in Excel
ExcelIsFun (4 days ago)
Yes, a whole "new" world of Filtered Reports : ) Thanks for the support, Hussein!
Dave Bowman (5 days ago)
Bye bye advanced filter, hello FILTER! Thanks Mike
ExcelIsFun (4 days ago)
You are welcome, Dave!!! I still think there are a few complex Filters that Advanced Filtere might do with more ease than FILTER..., but for many tasks FILTER WILL be easier : ) Thanks for support : )
sahar kathier (5 days ago)
👍
Narendra rathore (5 days ago)
👍
Rajat solanki (5 days ago)
First

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.