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

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

204 ratings | 5315 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 (64)
pmsocho (4 months ago)
Thanks for the video! Thumbs up as always!
Malina C. (5 months ago)
I don't know how it's possible, but I don't receive notifications of your videos. Dark magic happened ;(. I have to check my account settings. Thanks for this video :)
ExcelIsFun (5 months ago)
That happens to me too... : ( I usually just have to go to Subscription List at YouTube each day to check to which new videos have been posted. You are welcome for the video!
David Benaim (5 months ago)
This is interesting but too complicated. In Google Sheets (which has had dynamic arrays for way longer) you can just do =FILTER(Array,Condition 1,[Conditioon2],...). Failing that, I had hoped it would be a simple =FILTER(Array,AND(condition 1,Condition 2), but that doesn't work either. A very common need is to include a header row in the filtered range.
Ismail Ismaili (6 months ago)
thank you so much Mr.Mike I can't wait anymore please if you know any website for buying Microsoft 365 the version that gonna give me this updated eng. please provide me i want to buy it because until now no update shows on my excel so i need to buy new one and i need your guide because i'm afraid to buy the wrong one.
Peter Bartholomew (6 months 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'.
excel is fun (6 months ago)
+Peter Bartholomew , that is funny : ) I am sure your Excel-needlework is refined : )
Peter Bartholomew (6 months 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 (6 months ago)
What is your entry into the challenge, Peter?
ExcelIsFun (6 months 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 (6 months ago)
Thanks Mike. Nice of you to consider my question. :) :) Amazing!!!! Proud to be your student in excel!!!!!
ExcelIsFun (6 months ago)
Go Team!!!!!
Leila Gharani (6 months ago)
Thank you Mike! So many wonderful possibilities with the filter function :)
ExcelIsFun (6 months 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 (6 months ago)
Another fantastic video.. Thanks
ExcelIsFun (6 months ago)
You are welcome, RRR!!!
M. SZ. (6 months 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 (6 months 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 (6 months ago)
Great video with neat formulas
excel is fun (6 months ago)
Glad it was great and neat! Thanks, Vida : )
Thank You very Much sir...
excel is fun (6 months ago)
You are very welcome, JOYDEB!!!
SuperRayrobinson (6 months 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 (6 months ago)
For back and forth dialog to get Excel solutions try this great Excel Question site: mrexcel.com/forum
Shabbir Hussain (6 months 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 (6 months ago)
For back and forth dialog to get Excel solutions try this great Excel Question site: mrexcel.com/forum
Ecole Sed (6 months 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 (6 months 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 (6 months ago)
Awsome excel team directed by a briliant Mike Girvin...Excel is full of secret
ExcelIsFun (6 months 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 (6 months ago)
You are welcome, Syed!!! So glad you get to hang out and have fun with Excel : )
Wayne Edmondson (6 months 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 (6 months 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 (6 months 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 (6 months 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 (6 months 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 (6 months 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 (6 months 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 (6 months ago)
Great idea, Unirotovibe!!! Thanks for the Uni-roto-VIBE : )
K B (6 months ago)
great video. Thanks Mike
ExcelIsFun (6 months ago)
You are welcome for the video, K B!!! Thanks for watching and having fun : )
Rico S (6 months ago)
Those formulas are so pleasing to the eye. Good work sir.
ExcelIsFun (6 months ago)
Yes, they are, especially the FILTER(RangeOfNumbers,RangeOfNumbers) to get all the non-zero numbers : ) Thanks for the support, Rico S!!!
Russell Martin (6 months ago)
you just helped me in my calculus class as well. THANK YOU!
Russell Martin (6 months ago)
+ExcelIsFun I am not so sure that Taylor expansions are fun. LOL
ExcelIsFun (6 months ago)
Calculus is fun!!!! Glad the videos help, Russel. You are welcome and thanks for your support : )
rf05mjy (6 months 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 (6 months 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 (6 months ago)
It's a great time to an Excel nerd. Thanks!
ExcelIsFun (6 months ago)
Go Excel Nerds!!!! Thanks foe the support, Jeremy!!!
edgie da pogi (6 months ago)
Thanks Mike... another Amazing video...Can't wait to have this version... my life would be easy with it :)
ExcelIsFun (6 months ago)
Yes, this new Excel Calc Engine DOES make things easier : ) Thanks for the support, edgie!!
Doug H (6 months ago)
Nice! Filter Arrays are awesome
ExcelIsFun (6 months ago)
Awesome for sure, Teammate Doug!!!!
Chris Mancinelli (6 months ago)
Wow, totally amazing Mike!!
ExcelIsFun (6 months ago)
Glad it was amazing, Chris!
Hussein Korish (6 months ago)
Thanks Mike .... i think there is a whole world of "Reporting" coming ahead in Excel
ExcelIsFun (6 months ago)
Yes, a whole "new" world of Filtered Reports : ) Thanks for the support, Hussein!
Dave Bowman (6 months ago)
Bye bye advanced filter, hello FILTER! Thanks Mike
ExcelIsFun (6 months 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 (6 months ago)
Narendra rathore (6 months ago)
Rajat solanki (6 months ago)

Would you like to comment?

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