Search code examples
excelif-statementcheckboxexcel-2007vba

How to cope with more than 100 IF statements

I am creating an checklist using an array of checkboxes in MS Excel 2007. Based on the number of checkboxes, there are 2^7 (=128) possible combinations in which the checkboxes can be selected. Each combination of checkboxes selected gives a unique outcome.

What would be the most efficient way to successfully test for each condition, and give a result in a cell in a worksheet?

Example code: 

=if(and(chkbox1=true,chkbox2=true,chkbox3=false,chkbox4=false,chkbox5=true,chkbox6=false,chkbox7=true),"Unique outcome",if(and(chkbox8=true,chkbox9.............)))))))))

The method I have (unsuccessfully) attempted is to use IF statements. I have attempted to bypass the nested IF limit by creating 14 named ranges with approximately nine nested IFs each, and compile the 14 named ranges in a valid nested IF within the spreadsheet. Unfortunately, the processor is struggling to calculate the outcome of this (i.e. computer is "freezing").

  • Would reducing the number of nested IFs in the named ranges (but increasing the number of nested IFs in the spreadsheet) have an influence on efficiency?
  • Would it be better to use nested IFs in cells rather than named ranges?
  • Would VLOOKUP work?

At the moment, I am contemplating using VBA code as people have said that it would be the most efficient way to approach numerous conditions. Does anyone have any suggestions or recommendations?

Solution

  • String together a 1 or 0 for each TRUE or FALSE result then create a lookup table with a column for each possible binary value and another column for the outcomes (or rows rather than columns).