Formula with Multiple Variables - help please

[bballsmurf4]

Need help with a formula on excel that I can't seem to get. Basically I trying to create a formula where the monthly interest payment is automatically calculated based in what range the monthly billing falls into, as well as what type payment it is (E33). But I have several variables. Is this possible in Excel -- if so PLEASE HELP! Trying to write something like this...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If E33=1<o></o>
AND C36 (Monthly Billing) is GREATER THAN $50 and LESS THAN $1501, THEN C37(Monthly Interest Payment) is 20% of the Monthly Billing (C36)<o></o>
OR if E33=1<o></o>
AND C36 (Monthly Billing) is GREATER THAN $1500 and LESS THAN $3001, THEN C37(Monthly Interest Payment) is 15% of the Monthly Billing (C36)<o></o>
OR if E33=1<o></o>
AND C36 (Monthly Billing) is GREATER THAN $3000 and LESS THAN $6001, THEN C37(Monthly Interest Payment) is 10% of the Monthly Billing (C36)<o></o>
OR if E33=1<o></o>
AND C36 (Monthly Billing) is GREATER THAN $6000 and LESS THAN $10,001, THEN C37(Monthly Interest Payment) is 10% of the Monthly Billing (C36)<o></o>
OR if E33=1<o></o>
AND C36 (Monthly Billing) is GREATER THAN $10,000, THEN C37(Monthly Interest Payment) is 5% of the Monthly Billing (C36)<o></o>
<o></o>
If E33=2<o></o>
AND C36 (Monthly Billing) is GREATER THAN $50 and LESS THAN $3000, THEN C37(Monthly Interest Payment) is 30% of the Monthly Billing (C36)<o></o>
OR if E33=2<o></o>
AND C36 (Monthly Billing) is GREATER THAN $1500 and LESS THAN $3001, THEN C37(Monthly Interest Payment) is 15% of the Monthly Billing (C36)<o></o>
OR if E33=2<o></o>
AND C36 (Monthly Billing) is GREATER THAN $3000 and LESS THAN $6001, THEN C37(Monthly Interest Payment) is 5% of the Monthly Billing (C36)<o></o>
<o></o>
If E33=3<o></o>
AND C36 (Monthly Billing) is GREATER THAN $50, THEN C37(Monthly Interest Payment) is 10% of the Monthly Billing (C36)<o></o>

 

[MikeDBMan]

here it is:

There are some issues, however. You talk about Greater than 1500 and then you talk about less than 1501. So 1500.01 is Greater than 1500 and 1500 is less than 1501. Is that how you want it? You have to be very careful how you designate your breakpoints as Excel doesn't know what you "really mean"!

I fixed one obvious type where E33=2. The first group was 50-3000 and the second group was 1500 to 30001. I changed the 50-3000 to 50-1501 I believe.

Also your formula does not take into account numbers less than 50 in D36.

The formula goes in Cell C37.

If you need to copy this formula down, you'll have to fix some absolute references in the formula but I could not tell anything based on what you posted. HTH.

   <table border="0" cellpadding="0" cellspacing="0" width="64"><colgroup><col width="64"></colgroup><tbody><tr height="20"><td style="height:15.0pt;width:48pt" height="20" width="64">=IF(AND(E33=1,C36>50,C36<1501),C36*0.2,IF(AND(E33=1,C36>1500,C36<3001),C36*0.15,IF(AND(E33=1,C36>3000,C36<6001),C36*0.1,IF(AND(E33=1,C36>6000,C36<10001),C36*0.1,IF(AND(E33=1,C36>10000),C36*0.05,IF(AND(E33=2,C36>50,C36<1501),C36*0.3,IF(AND(E33=2,C36>1500,C36<3001),C36*0.15,IF(AND(E33=2,C36>3000,C36<6001),C36*0.05,IF(AND(E33=3,C36>50),C36*0.1)))))))))</td> </tr></tbody></table>

 

[MikeDBMan]

I was thinking last night about your ranges and I believe the best way to handle it is to use <= and then > on the same number. For example, your ranges would be If x<=3000 , do such and such. Else if X>3000 do such and such. This way you cover ALL numbers and know exactly how things will be handled. The way your wrote your breaks is hard to know if things turn out all the time exactly as you expect them to turn out.

 

[fairwinds]

Hi,

Here is a way to do this with a shorter formula however perhaps you need to add som IF statements to handle e.g. small numbes falling outside.

=C36*LOOKUP(C36,CHOOSE(E33,{50,1500,3000,1000;0.2,0.15,0.1,0.05},{50,1500,3000;0.3,0.15,0.05},{50.00001;0.1}))