Most Excel Users Don't Know Excel Logic!

[@robparker1625]

It's so much better when practical examples are used. MARVELous.

[@MyOnlineTrainingHub]

😁cheers, Rob! Nice play on words 😉

[@growthmindsetwitharsalan]

100/100 for creativity. We all know your excel skills 👍. But I guess acting was even better. Loved it 🤩

[@MyOnlineTrainingHub]

🤣I'm not sure I'd go that far, but glad you enjoyed it! 🙏

[@yokoyama7590]

Great explanations and examples! You are a true Avenger of false formulas!

[@MyOnlineTrainingHub]

😁 thanks so much 🙏

[@Quidisi]

Great video! One challenge though @12:15 Sadly, IFS doesn't appear to stop calculating when it reaches TRUE, unlike the IF function, which DOES stop calculating. You can observe this by creating some IF and IFS test functions and stepping them through the Formula Evaluator. I can have a dozen IFS conditions - and even though the very first one will be TRUE, it still evaluates every single condition anyway (i.e. it doesn't stop). In fact, it's worse than that. You know how we often end our IFS with TRUE (as a catch-all)? Well, you can end your ifs with a dozen TRUE catch-alls, and it will keep evaluating each and every one - it doesn't stop at the first one. For example, end an IFS with ....TRUE, "Done", TRUE, Cos(15), TRUE, Sin(45), TRUE, Tan(90)) and you'll see in the Evaluator that it calculates every one of those TRUEs Anyway, this is why, if I have a large spreadsheet, I now try and avoid IFS 😩

[@MyOnlineTrainingHub]

Ah, yes I had forgotten that. Thanks for sharing. To be clear for others reading this, while it does calc all tests, it returns the first TRUE, so the order of tests is still important, but it's not more efficient. I dug up an old email thread about IFS which contained a link to this post by Charles Williams about short circuiting in array formulas that might be interesting to others as well: fastexcel.wordpress.com/2023/01/06/short-circuiting-in-array-formulas/

[@BizTrix]

It's definitely needed for others to see better approaches for using logical tests and brings forward an important modeling point -- structuring your data to leverage the full power of the function is critical. Thank you. A point for consideration: the plus operator (+) is actually an arithmetic operator. TRUE and FALSE are Boolean data types, not numbers. As you mention, this is why the unary operator (--) exists; it explicitly converts the Boolean type to a numeric type. Depending on what we're doing in the spreadsheet, Excel will implicitly convert the Boolean values to bit values (i.e. a number) which can then be used in arithmetic. This is why, for example, when A1 = TRUE and A2 = SUM(A1), you get the value of 0, but when B1 = A1*1, you get 1. (The arithmetic operators are implicitly converting the Boolean type to a number; this is a programming technique for overloading operators). Thus, TRUE and FALSE are not actually 1 and 0. This is also why SQL Server, for example, does not have the Boolean data type; no need to waste CPU time on the data conversion from Boolean to bit. In other languages, the OR comparison operator is often ||. Compliers use the definition that 0 is truly 0, which is why it evaluates to false; by definition, everything else is not 0, or true. The binary notation of values (0 in decimal is 0 in binary) also confirms this point (and everything else in binary is not 0).

[@MyOnlineTrainingHub]

Thanks for sharing the insights, @BizTrix 🙏😊 This, in particular, was interesting: "Compliers use the definition that 0 is truly 0, which is why it evaluates to false; by definition, everything else is not 0, or true."

[@petr6617]

A few notes: 1. It would be useful to start with a truth table for all the functions mentioned (only a percentage of users know XOR, it can crypt/encrypt beautifully). 2. It would be useful to explain more thoroughly the occurrence of --, i.e. (-1)*(-1) in the SUMPRODUCT function. 4. The SIGN function is convenient to use together with the CHOOSE function (for three branching). 5. As far as I remember, the ISEVEN and ISODD functions were very slow. 6. I would end the topic of logical functions with Karnaugh maps and the Quine-McCluskey method. 7. Users often use the IF function in conditional formatting... 8. The logical operator/function AND corresponds to multiplication (*). The logical operator/function OR corresponds to addition (+, has the logic of several checkboxes, used by MsgBox in VBA as paramaters - icon and buttons for example).

[@MyOnlineTrainingHub]

Thanks for watching and sharing!

[@Excelingtheeasyway]

One word describes this video.. Brilliance. The perfect blend of everything with amazing information from start to finish...Brilliance.

[@MyOnlineTrainingHub]

Wow, thank you so much! 🥰

[@simona7517]

Terrific video, lots of stuff I didn't know as a long-time Excel power user. Thanks!

[@MyOnlineTrainingHub]

Awesome to hear 🙏😊

[@jonr6680]

Confess that I often break down complex formulae into individual cell elements to see them working right, THEN copy the components into a single cell to build one final monster equation. These advanced tips on logic are gold, thanks! Especially how Boolean convert to numeric, had no idea. Also the Marvel Thor theme, nailed it. Thor is such a down to earth superhero/ demigod, he quickly adapted to earthly ways... But as the saying goes, he only has a hammer so treats every problem like a nail.

[@MyOnlineTrainingHub]

😁 so pleased you learned some new tips and enjoyed the Thor theme! 🙏

[@courtn580]

Wow Mynda, you always exceed expectations in your videos - so much I don't know, and loved the humour with Chris, thank you.

[@MyOnlineTrainingHub]

Thanks for watching and your kind words 🥰

[@Sara-j3m4j]

You're my hero, Mynda.

[@MyOnlineTrainingHub]

Thanks for your support 🥰

[@XanderSchoolen]

Thanks for the video! It made me smile every now and then, but didn't distract too much of the things you wanted to explain.

[@MyOnlineTrainingHub]

Glad to hear it, Xander! 🙏

[@andyloats383]

All the Marvel stuff really got in the way for me, especially when you consider that actors are on strike and you felt it appropriate to copy and paste their likenesses.

[@MyOnlineTrainingHub]

Thanks for your comments, Andy. Hadn't thought about that given that all their past work is still being aired. Will give it some more thought though. Appreciate you taking the time to comment.

[@andyloats383]

@@MyOnlineTrainingHub I wanted to reiterate though, one the main reasons for my comment is that I think you have the absolute best excel content available, anywhere and I really, really value what you’ve created. Having said that, I should also be conscious that you might want to explore and evolve. Anyway, Sorry I came across negative.

[@deeptoot1453]

Wow! What an amazing quality of excel walkthrough!! I am learning advanced excel as a university course (Finance) and just disocvered your channel. I already loved fooling around excel anyways but this is so interesting. Half of the stuff on your channel I don't even have to learn but I am sure at one point or another it will be beneficial to my career and it's interesting anyways! Great teaching skills and I like the humor in the video as well! Everything is so clear it's truly exceptional!

[@MyOnlineTrainingHub]

Fantastic to here 🙏 good luck with your studies 😊

[@saleemtaj2647]

Hi Please make same type o log for all "forecast formulas" Thanks

[@spilledgraphics]

minute 8:34 = gem! 💎 amazing analogy Mynda! thanks!

[@spilledgraphics]

I admit it, I was part of that group (minute 15:01) until couple of days ago.

[@MyOnlineTrainingHub]

😁glad you enjoyed it 🙏

[@luisosornio9752]

An excellent explanation on how Boolean affects the way we calculate in Excel, an advanced level video explained with funny examples. Thanks!!

[@MyOnlineTrainingHub]

Glad you liked it 🙏😁

[@frengkyu]

My favorite Excel tutorial video, Mynda Treacy deserves an Oscar, thank you. 🤩

[@MyOnlineTrainingHub]

😂 so pleased you enjoyed it!

[@Seftehandle]

How do you write the formula with text on the side next to the result?

[@MyOnlineTrainingHub]

I use the FORMULATEXT function 😉

[@ExcelUpNorth]

Thor’s having a tough time qualifying… Someone needs to check on him! Thanks for the video. I like to use the logical functions for readability but it’s good to know these pure logical tests are available to use too 😁

[@MyOnlineTrainingHub]

😁 Great to hear, Darryl!

[@chrism9037]

Mynda, as a HUGE Marvel fan, I loved this video 😂😂

[@MyOnlineTrainingHub]

😁 great to hear, Chris!

[@kumarsb]

Thank you so much. I learned so much. You make complex concepts so simple.

[@MyOnlineTrainingHub]

Wow, that's great to hear 🙏

[@MichaelBrown-lw9kz]

This lesson is fantastic.

[@MyOnlineTrainingHub]

Glad you think so, Michael! 🙏

[@williamhougland-cdot2165]

what is the impact on memory when using one or the other formulas... as I have to deal with 135000 rows and 35 columns, memory/speed of calculations become a driver in which formulas I use (impact of conditional formatting is an example of the impact to memory/operation speed)

[@MyOnlineTrainingHub]

I can imagine! After each section I mentioned which is going to be most efficient to use.

[@azwarmzafar]

Great video editing and idea is great, very informative thanks.

[@MyOnlineTrainingHub]

Glad you liked it! 🙏😊

[@jeffmcdonald2687]

OK. Must always include Thor. You two are perfect together.

[@MyOnlineTrainingHub]

😁thank you!

[@scottbailey5925]

Appreciate your very creative & humorous presentation!

[@MyOnlineTrainingHub]

Thank you kindly! 🥰

[@krishanphophalia9303]

Absolutely loved your short yet eye opening sharing about the logical function. Your creativity was at it’s best TH”OR” (nice pick by Vijay)! ❤ it.😍 Thanks for sharing! 🙏

[@MyOnlineTrainingHub]

So glad you enjoyed it! 😁🙏

[@laurel1634]

Great subject, well organized. As someone who is using quite a few IF and IFS, I didn't know these options existed. I need to rewatch a few times and use them on my own for the logic to stick. I like the straight no frills videos, but maybe the cut ins with Thor will make it more memorable, more so than a cookie company.

[@MyOnlineTrainingHub]

😁thank you! Glad you found it helpful.

[@GuillermoPalchik]

Hi Mynda, I LOVE your channel and your videos, and this one is no exception. Quick question: How do you get the actual formula to display on cell F17 at @3:38?

[@MyOnlineTrainingHub]

Thanks so much, Guillermo! I used the FORMULATEXT function.

[@vijayarjunwadkar]

Wow, super video! Will AND OR NOT invite THOR! No, but XOR would! By the way TH"OR" has "OR" in his name, but still! Loved this new way of edutainment! ❤ Thank you Mynda, keep those coming! 😊👍

[@MyOnlineTrainingHub]

😁 Thanks, Vijay! Good point about TH"OR" 🤦‍♀️

[@barttitulaerexcelbart9400]

Hi Mynda. Verry nice video again. I knew that all values above 1 are treated as TRUE, Mike Girvin explained this a few times. But I never, ever had seen the SIGN formula. Although there are workarounds, this function is now in my Excel "vocabulaire"....😊

[@MyOnlineTrainingHub]

Awesome to hear, Bart!

[@RogerStocker]

Nice way of teaching! 💯

[@MyOnlineTrainingHub]

I appreciate that, Roger! 🙏

[@mohammedassem8371]

Great Video .I have a question when I copy and paste into Excel, the page layout change why?

[@MyOnlineTrainingHub]

I guess you're coping from somewhere that the format is different and you're pasting in the format as well as the data. Try paste special > values.

[@reubsification]

These are getting really good!

[@MyOnlineTrainingHub]

Glad you like them! 🙏😁

[@hayekfriedman9078]

A great fun video. Both informative and entertaining.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@a.achirou6547]

Thank you for this lesson.

[@MyOnlineTrainingHub]

My pleasure 😊

[@byambatsogtchuluunbaatar4899]

Beautiful lesson. Thank you.

[@MyOnlineTrainingHub]

🙏 so glad you liked it!

[@robb1267]

Those eyes at 1:21! 😀😀😀😀😀😀😀 Another great video, thanks!

[@MyOnlineTrainingHub]

😁thanks for watching!

[@kebincui]

Awesome video. Thanks Mynda 👍❤

[@MyOnlineTrainingHub]

Thanks for watching! Glad you enjoyed it 😁

[@7absinth]

How about SWITCH() function? It will do the same.

[@MyOnlineTrainingHub]

No, SWITCH is more like CHOOSE in that it returns an item from a list.

[@7absinth]

@@MyOnlineTrainingHub for example SWITCH(TRUE(), CRITERIA, OUTPUT, ELSE)

[@operamaniak81]

Great presentation, tank you!

[@MyOnlineTrainingHub]

Glad you liked it! 🙏😁

[@lisarevelli3015]

I love this! I needed to laugh! Thank you!

[@MyOnlineTrainingHub]

Glad you enjoyed it! 😁

[@johnmainini]

Your just awesome and I LOVE THOR!!!!!

[@MyOnlineTrainingHub]

Thanks so much, John! 😆

[@alcoolbc]

In university, I found logic as dry as unbuttered toast after a bowl of unbuttered, salty popcorn. Thankfully, this video is full of facts and way more fun than my university prof (alas, only a "C" in the course...). Anywho, quite delightful... thank you.

[@MyOnlineTrainingHub]

😁wonderful to hear! 🙏

[@roywilson9580]

Great video, thanks for sharing.

[@MyOnlineTrainingHub]

Thank you and thanks for watching, Roy! 🙏

[@Seftehandle]

F9 for next step in 14:10 is excellent

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@signalmas5673]

Funny and educational. Nailed it !

[@MyOnlineTrainingHub]

Thanks so much! 😁

[@Dale-ko9kc]

You make me want to learn this.

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@mogarrett3045]

lol another great tutorial Mynda....fun too

[@MyOnlineTrainingHub]

😊 thank you!

[@crispexcel]

Hillarious and enlightening

[@MyOnlineTrainingHub]

😁thank you!

[@ManthaarJanyaro]

❤ for Excel from Sindh, Pakistan.

[@MyOnlineTrainingHub]

Thank you for watching!

[@Weehawk]

Amazing video

[@MyOnlineTrainingHub]

Glad you think so! 🙏

[@GeertDelmulle]

Funny format! :-) In the beginning of the video, that biting of your finger was a bit telling… :-) Admittedly, we are a bit jealous, because we don’t want to share ‘our Mynda’, not even with Thor… ;-) PS: the logic functions are scalar valued, true, but we can always ‘vectorize’ everything in Excel nowadays, using BYROW, BYCOL, MAP,…

[@MyOnlineTrainingHub]

Thanks Geert! Good point about vectorizing with the new dynamic array functions 🙏

[@XLLearner_Courses]

Nice touch with Thor

[@MyOnlineTrainingHub]

😁thanks so much!

[@ClaudeBalleux]

Usefull and funny ! Thank you

[@MyOnlineTrainingHub]

Glad you liked it, Claude!

[@keylanoslokj1806]

You are funny and naughty in a very informative way 😂❤

[@MyOnlineTrainingHub]

Thanks 😁

[@goutamnayak5011]

In USA companies still using ms Excel for their works ???

[@MyOnlineTrainingHub]

In every country!

[@MrJarod2003]

you have defen make excel videos more interesting for those who has to use excel, but never had the tension to learn , lol

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@robertoschenone9648]

Great video as usual! but I'm looking forward to just plain excel video tutorial without all that junky extra stuff like Thor and the like! We excel addicted are tough and no frills guys!

[@MyOnlineTrainingHub]

Coming soon! 😁

[@abrahamtio]

99% seems like a very specific number. how was this statistic determined?

[@MyOnlineTrainingHub]

Well, only 1% of Excel users use Tables, so I'm pretty confident at least as many don't know that in Excel positive and negative numbers = TRUE for many functions, conditional formatting etc. In fact, it's probably more than 99%, I was being conservative 😉

[@abrahamtio]

@@MyOnlineTrainingHub 1% is also a very specific number. how do you know only 1% of excel users use tables?

[@DanKnight]

You made a mistake on the list of superheroes...you forgot to add your name.

[@rudiklein]

Slick 😎

[@MyOnlineTrainingHub]

@danknight 😁🙏

[@TheIvalen]

3:39 "all chests" - Mynda, I think you're confused by Thor's 'brains'. *wink*

[@MyOnlineTrainingHub]

😂 probably!

[@marilubadillo8108]

He is right, extremely handsome a doesn't need Excel knowledge. awesome video. thanks

[@MyOnlineTrainingHub]

😁Thanks for watching!

[@tiger131071]

Excellers Assemble!

[@MyOnlineTrainingHub]

😁

[@tiger131071]

Very much enjoyed this video 😂 Great work @@MyOnlineTrainingHub

[@numchuck180]

The thor/marvel reference was entertaining at the start but became very distracting and cringe very quickly.

[@MyOnlineTrainingHub]

Thanks for watching and the feedback 🙏

[@TheIvalen]

Logical operations make people's heads thor. Hah

[@MyOnlineTrainingHub]

😂😂 very good!

[@newdimension4731]

you cannot put a COMMA after the 100 it's semi colon ; for my 365 good thing I know I to get around.

[@MyOnlineTrainingHub]

If you're on a European build, then yes, you usually replace commas with semicolons and periods with commas.

[@hayekfriedman9078]

Do you think Thor could come around to my house and explain Excel logic, say after 8pm? Any night he chooses! You're kinda being a bit mean to Thor, BTW!

[@MyOnlineTrainingHub]

🤣I think he's a bit busy at the moment promoting Furiosa, but maybe after.

[@tihomirhristov77]

💚

[@MyOnlineTrainingHub]

🙏😁

[@IamTheReaper911]

👩‍🏫 this is 🍎 is for you 🫡 😎

[@MyOnlineTrainingHub]

😂🙏😍

[@peterbartholomew7409]

Another approach would be to define your own AND function = ANDλ(A, B) where ANDλ = MAP(A, B, LAMBDA(x, y, AND(x, y)))

[@MyOnlineTrainingHub]

Love it, Peter! Thanks for sharing 😊

[@ryangraham6878]

lol 😂

[@MyOnlineTrainingHub]

😁thanks for watching!

[@dogmalogy3666]

😂

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@geckostudiodotcom]

Love it omg its so hilarious.. hahahahah these are all brainsssss omg

[@MyOnlineTrainingHub]

😁 thanks for watching!