Nested Ifs

Using Array Constants in your formulas

I find I use this kind of formula a lot… =IF(AND(B5<>{“A”,”B”,”C”,”D”}),”Yes”,”No”) Instead of: =IF(AND(B5<>”A”,B5<>”B”,B5<>”C”,B5<>”D”),”Yes”,”No”) … and imagine if you want to test 20 items for B5 is not equal to?  It’s shorter and easier to read and edit.  I discovered this by just playing around way back but it turns out these are called Array …

Using Array Constants in your formulas Read More »

Avoiding Nested IFs

We have all done them but there comes a time to find a better way.  In Excel 2003 you were limited to 7 levels of nesting but 2007 onwards the level could be increased to 64. Both these levels are beyond my pain barrier for an overly complex formula fraught with danger. Even if you …

Avoiding Nested IFs Read More »