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

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.

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.

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).

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)