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 »