

Subject:
Exceeded number of ifs allowed in Excel
Category: Computers > Algorithms Asked by: memccaffreyga 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"))))))))  


There is no answer at this time. 

Subject:
Re: Exceeded number of ifs allowed in Excel
From: jack_of_few_tradesga 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: ansel001ga 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: mfrippga 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 nonunique 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: mfrippga 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) 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 