Nested Ifs

Using Array Constants in your formulas

Array constants can help simplify 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 …

Using Array Constants in your formulas Read More »

Avoiding Nested IFs

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 …

Avoiding Nested IFs Read More »