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
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.
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.
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'.
+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!
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" : )
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)
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.!!!
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
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!
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
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!
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 : )
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
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...
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.