0

I need to be able to use the following formula in Excel to choose a formula used on a different sheet based on the values in drop-down menus in cells D2 and D3. The only problem is that the formula contains more than 7 nested IF statements, which Excel tells me is invalid.

The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

How can I break this formula up to be able to achieve the same outcome?

=IF(D2=1,Dropdowns!C8,IF(AND(D2=2,D3="I"),Dropdowns!C9,IF(AND(D2=2,D3="II"),Dropdowns!C10,IF(AND(D2=2,D3="IIIa,b"),Dropdowns!C11,IF(AND(D2=3,D3="I"),Dropdowns!C12, IF(AND(D2=3,D3="II"),Dropdowns!C13, if(and(D2=3,D3="IIIa"),Dropdowns!C14, if(and(D2=3,D3="IIIb"),Dropdowns!C15, if(and(D2=4,D3="I"),Dropdowns!C16, if(and(D2=4,D3="II"),Dropdowns!C17, if(and(D2=4,D3="IIIa,b"),Dropdowns!C18, "error")))))))

  • What's the problem? It's a perfectly valid formula.. – Raystafarian Oct 1 '14 at 19:44
  • Excel does not allow more than 7 nested IF statements. – Matt Jenkins Oct 1 '14 at 19:57
  • I get the following message when I enter the formula... "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format." The file format is xls. Also tried xlsm. – Matt Jenkins Oct 1 '14 at 20:00
  • What version of excel? – Raystafarian Oct 1 '14 at 20:08
  • I am using Excel 2013. – Matt Jenkins Oct 1 '14 at 20:09
1

Alternative to many nested IFs

When you require a long series of nested IFs, there is another approach that does not restrict you to the nested IF limit. It uses a "selection" approach, which can also be easier to create because it is visually more straightforward.

Nested IFs evaluate conditions consecutively until it finds one that is true. The logic and the output values are an intermixed series. The alternative approach evaluates all of the conditions in one step and uses the result to select the right value from a list.

It combines two techniques, the CHOOSE function and a Boolean expression. In Excel 2003, you could CHOOSE from up to 29 values. Excel 2007 and later versions allow up to 254 selections, without using any IF statements.

The CHOOSE Function

You have a long formula so I won't reproduce the entire thing, but here's the approach (the placeholder "index" is explained in the next part):

=CHOOSE(<index>,"Error",Dropdowns!C8,Dropdowns!C9,Dropdowns!C10, ...)

Your whole list of IF statement result values gets included. The way this works is the "index" is calculated from all of the conditions that are part of your nested IF chain. The result will be the sequence number of the value in the CHOOSE list.

The Index

The index uses Boolean arithmetic (calculations based on 0/1 values of True/False conditions). You build the index as an expression like this:

1 + condition1 * 1 + condition2 * 2 + condition3 * 3 + ...

The initial 1 will be explained in a minute. The rest is all of your IF test conditions in the associated order to match the results list. Each test evaluates to a 1 or 0, which then gets multiplied by its associated index number (the 1, 2, 3, ... following each condition). Since only one of these tests will be true, that determines the value of the index (a sum of zeros for all false conditions plus an index value for the True one).

Substituting the test conditions from your nested IF example would look like this:

1 + (D2=1)*1 + AND(D2=2,D3="I")*2 + AND(D2=2,D3="II")*3 + ...

The entire expression goes in place of the <index> placeholder in the formula shown under the CHOOSE Function heading. The Choose function then selects the target value from the list based on the index.

Your error condition is if none of the tests are true, which would evaluate to a sum of zero. The index is the position number of the value in the list, which starts with 1. Adding 1 to the index calculation (shown as the first term), makes the error condition evaluate to 1 and increases all of the other results by 1. So the first result in the list is your error message.

  • 1
    Thanks for the reply. Although I used a different method that I listed above, I believe that this method would also work. – Matt Jenkins Oct 2 '14 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.