The AND Function in Excel is Broken - Here's How to Fix It!

[@ExcelOffTheGrid]

★ Get the example file ★ Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0230 AND OR broken.zip

[@leerv.]

I love whenever I find opportunity to obviate the need for AND(), OR(), and even IF()! It looks like magic, and it's so sparkly clean :)

[@ExcelOffTheGrid]

I agree 👍

[@Adam_K_W]

Beautiful solution!

[@10ozGold]

Solution #3 is genius! Thank-you so much. Excellent video.

[@arbazahmad7177]

Excellent... 🎉 thanks for sharing

[@clivepetty2338]

Insightful!

[@haihathanh5061]

I love you logic, brilliant!

[@ExcelOffTheGrid]

Thanks 😁

[@shadrackawunyo7786]

Great video, great explanation

[@ExcelOffTheGrid]

Thank you. 😁

[@joshpeters813]

Thanks for simplifying the logic behind these calculative functions

[@ExcelOffTheGrid]

No problem! Happy to help.

[@westleyempeigne6541]

Great video Mark. Looks like we need to switch how we are thinking , how we are using Excel to simplify our calculations

[@ExcelOffTheGrid]

With dynamic arrays we need to think differently - it may or may not be simpler, but definitely a different approach.

[@karolinab9749]

Thank you, super short and packed with useful information!!!😊

[@ExcelOffTheGrid]

Glad it was helpful!

[@chrism9037]

Nice video Mark!

[@ExcelOffTheGrid]

Thanks Chris. 😁

[@serdip]

As always, absolutely BRILLIANT!

[@ExcelOffTheGrid]

Thank you. 😁

[@extraktAI]

This is great, thanks!

[@IvanCortinas_ES]

Evaluating formulas with F9 or with tooltip is always recommended to see the matrix returns. Excellent tutorial, Mark. Thanks.

[@ExcelOffTheGrid]

Great options. I think the tool tip is still a newish feature - but very useful.

[@huseyinburaktasci1638]

Thank you!!

[@yousrymaarouf2931]

Fantastic

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@GeertDelmulle]

20” into the video… A: no it isn’t broken, but it’s an aggregator - it aggregates all the results to a scalar result. If you want it to be an iterator, then wrap it with BYROW (or perhaps MAP to be on the safe side). …now on with the video. Edit after watching: mixed-mode calculations rule, indeed (but may be more difficult for others to understand) and they only work in numerical contexts. Then again: they are also my preferred way to go about it. Good Video, as always! :-)

[@ExcelOffTheGrid]

I see you've been through this pain already 😁

[@GeertDelmulle]

@@ExcelOffTheGrid Honestly, the first time I used mixed-mode calculations was on my HP-27s in the late ‘80s. I don’t consider it a pain, but rather a pleasure. ;-)

[@ExcelOffTheGrid]

Late 80's!!!! You're a proper wizkid... I'm just faking my way through this stuff 😂

[@GeertDelmulle]

@@ExcelOffTheGrid I checked: it was the 28s (I still have it).

[@GeertDelmulle]

@@ExcelOffTheGrid You’re not faking - your arguments are very sound.

[@aliab2201]

I really envy all the excel wizard.

[@ExcelOffTheGrid]

Ha ha ha - you really shouldn't. It just means we've wasted too much time on things people don't care about. 😂

[@Bhavik_Khatri]

I prefer solution 3. I typically ensure the formula is functional and then attempt to eliminate any unnecessary if statements. I often utilise min and max functions for dates rather than employing if statements.

[@ExcelOffTheGrid]

Good approach - always eliminate the unnecessary.

[@EXARCWithGrandpop]

Here is a question that is off topic. In your opinion, what tools or functions is the best way to retrieve supper large amounts of data (Data source is over 3million rows), that use over 100 filters/criteria to retrieve that data? Right now, the data is in several excel workbooks and we use power query to merge the data and apply the filters. It works but it is slow to retrieve the data.

[@ExcelOffTheGrid]

Sounds like it's time to get an SQL database to me.

[@EXARCWithGrandpop]

@@ExcelOffTheGrid The IT group will not allow one due to cost.

[@EXARCWithGrandpop]

Do you think I am doing the best option by using power query? Or is there something else to do?

[@ExcelOffTheGrid]

If you're not allowed to use the right tools for the job, then you've got to cobble together anything you can. You've not really got a lot of options. I would suggest saving the data as CSV files, rather than Excel workbooks - as that will refresh faster.

[@Ozgur-wq3sq]

One of a kind Clicbait 😅

[@ExcelOffTheGrid]

Unfortunately the title of "Because we used Excel before dynamic arrays, our understanding of the AND function may be incorrect, which could lead to issues when we do adopt dynamic arrays" would probably would get zero clicks 😂

[@gef24]

You can also add only 3 letters to the "broken formula" : Just replace AND with BITAND, OR with BITOR. This way, you can use exclusive OR (BITXOR).

[@ExcelOffTheGrid]

Well... that is very interesting - I didn't know that. I would have expected BITAND and AND to both be aggregators. Thought, it looks like BITAND only works with 2 numbers - so may still need to use some other techniques.