View Question
Q: Exceeded number of ifs allowed in Excel ( No Answer,   4 Comments )
 Question
 Subject: Exceeded number of ifs allowed in Excel Category: Computers > Algorithms Asked by: memccaffrey-ga List Price: \$2.00 Posted: 09 May 2006 07:52 PDT Expires: 08 Jun 2006 07:52 PDT Question ID: 726928
 Just below is my formula. Unfortunately I've exceeded the number of if's that Excel allows. Is there a way to rewrite this instead? =IF(\$D\$3=L14,\$D\$5*0.05,IF(\$D\$3=K14,\$D\$5*0.2+K19,IF(\$D\$3=J14,\$D\$5*0.2+K19,IF(\$D\$3=I14,\$D\$5*0.2+K19,IF(\$D\$3=H14,\$D\$5*0.1+K19,IF(\$D\$3=G14,\$D\$5*0.1+K19,if(\$d\$3=f14,\$d\$5*.15+k19,IF(OR(\$D\$3=E14,\$D\$3=D14),E5,"0")))))))) Request for Question Clarification by maniac-ga on 09 May 2006 19:22 PDT Hello Memcaffrey, I see a few alternatives. Please respond with an indication of which method you'd like to pursue. [1] An excel macro (function) could handle this easily and could be extended for more possible values. [2] If you have space on the spreadsheet to add a row (perhaps hidden) to compute the result in each if function and then use lookup() [plus some additional conditions] to provide the result. This could also be extended for more possible values. --Maniac
 Answer
 There is no answer at this time.

 Comments
 Subject: Re: Exceeded number of ifs allowed in Excel From: jack_of_few_trades-ga on 09 May 2006 11:41 PDT
 I have run into a similar problem before. My solution was to put part of the equation in another cell and reference that cell for information in your long (yet slightly shorter) list of IFs. It's not as easy or pretty, but it can get the job done.
 Subject: Re: Exceeded number of ifs allowed in Excel From: ansel001-ga on 11 May 2006 17:53 PDT
 In your particular case, you can lesson the number of nested if statments by using a couple more OR's in your formula. The formula is the same if D3 = K14, J14, or I14. Use one OR statment instead of three nested IF's. The formula is the same if D3 = G14 or H14. Use one OR statment instead of two nested IF's.
 Subject: Re: Exceeded number of ifs allowed in Excel From: mfripp-ga on 14 May 2006 15:48 PDT
 If the values in cells D14:L14 are unique, you can use either of these expressions: =CHOOSE(IF(ISNA(MATCH(\$D\$3, D14:L14, 0)), 0, MATCH(\$D\$3, D14:L14, 0))+1, "0", E5, E5, \$D\$5*0.15+K19, \$D\$5*0.1+K19, \$D\$5*0.1+K19, \$D\$5*0.2+K19, \$D\$5*0.2+K19, \$D\$5*0.2+K19, \$D\$5*0.05) =IF(\$D\$3=L14,\$D\$5*0.05,0) + IF(\$D\$3=K14,\$D\$5*0.2+K19,0) + IF(\$D\$3=J14,\$D\$5*0.2+K19,0) + IF(\$D\$3=I14,\$D\$5*0.2+K19,0) + IF(\$D\$3=H14,\$D\$5*0.1+K19,0) + IF(\$D\$3=G14,\$D\$5*0.1+K19,0) + IF(\$D\$3=F14,\$D\$5*0.15+K19,0) + IF(OR(\$D\$3=E14,\$D\$3=D14),E5,0) The second method is more transparent than the first method, and can be applied to an arbitrary set of lookup cells. But it will definitely not work if any of the cells you are searching have the same values. The first method will work with non-unique values, but it will do the calculation for the leftmost matching cell, instead of the rightmost cell, which your original formula would have done (e.g., if D3 matches both K14 and J14, my technique will use the formula for K14, and yours would use the formula for J14).
 Subject: Re: Exceeded number of ifs allowed in Excel From: mfripp-ga on 14 May 2006 16:06 PDT
 Here's a hybrid version that will work exactly like your original expression, but without running into any limits with nested if() functions. This will give priority to the last matching value in the list. =CHOOSE(INT(LOG10(10^1+IF(\$D\$3=D14, 10^2, 0)+IF(\$D\$3=E14, 10^3, 0)+IF(\$D\$3=F14, 10^4, 0)+IF(\$D\$3=G14, 10^5, 0)+IF(\$D\$3=H14, 10^6, 0)+IF(\$D\$3=I14, 10^7, 0)+IF(\$D\$3=J14, 10^8, 0)+IF(\$D\$3=K14, 10^9, 0)+IF(\$D\$3=L14, 10^10, 0))), "0", E5, E5, \$D\$5*0.15+K19, \$D\$5*0.1+K19, \$D\$5*0.1+K19, \$D\$5*0.2+K19, \$D\$5*0.2+K19, \$D\$5*0.2+K19, \$D\$5*0.05)
 Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service. If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for

 Google Home - Answers FAQ - Terms of Service - Privacy Policy