Logical formula for multiple input

**sagulolo** · 03-07-2012, 11:36 AM

Hi

trying to formulate the excel to return a percentage rebate/charge from the table which contain of 2 argument(purchase year & how many installment) but got error, i was trying to apply nesting if to do it but error come "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

this is my formula, however error come when i insert another nesting if, i believe i do the wrong way, please correct me.

=IF(AND(C11<=2006,C13<=3),"65%",IF(AND(C11<=2006,C13<=6),"60%",IF(AND(C11<=2006,C13<=12),"55%",IF(AND(C11<=2006,C13<=18),"50%",IF(AND(C11<=2006,C13<=24),"35%",IF(AND(C11=2007,C13<=3),"55%",IF(AND(C11<=2007,C13<=6),"50%")))))))

Appreciate your assistance.

example 2.xls

Best Regards

Eric Tham

**Fotis1991** · 03-07-2012, 11:46 AM

Hi. Welcome to the forum.

In Excel 2003, there is a limit of 7 IF.

But there is way to do it.

Which is the extra condition?

Look this

=IF(AND(C11<=2006;C13<=3);"65%";IF(AND(C11<=2006;C13<=6);"60%";IF(AND(C11<=2006;C13<=12);"55%";IF(AND(C11<=2006;C13<=18);"50%";IF(AND(C11<=2006;C13<=24);"35%";IF(AND(C11=2007;C13<=3);"55%";IF(AND(C11<2007;C13<=6);"50%";IF(C13>1000;"FOTIS"))))))))

Is this helps you?

**Andrew-R** · 03-07-2012, 11:50 AM

As you're using a table you don't need any if statements at all ...

=INDEX(B2:F7,MATCH(C11,{0,2007,2008,2009,2010,2011},1),MATCH(C13,{0,4,7,13,19},1))

**sagulolo** · 03-07-2012, 12:21 PM

Dear Fotis1991,

thank for your assistance, my problem solve already but im still curious about your method, my extra condition is as per below:-

=IF(AND(C11<=2006,C13<=3),"65%",IF(AND(C11<=2006,C13<=6),"60%",IF(AND(C11<=2006,C13<=12),"55%",IF(AN D(C11<=2006,C13<=18),"50%",IF(AND(C11<=2006,C13<=24),"35%",IF(AND(C11=2007,C13<=3),"55%",IF(AND(C11< =2007,C13<=6),"50%",IF(AND(C11< =2007,C13<=12),"45%".......till i covered the whole table))))))))

Dear Andrew-R,

thank you so much, my problem solve.

Best Regards

Eric Tham

**Fotis1991** · 03-07-2012, 06:57 PM

Hi

An answer,just because i need to answer when someone ask me something.

...Dear Fotis1991,

thank for your assistance, my problem solve already but im still curious about your method, my extra condition is as per below:-....

Of course Andrew solution, it's much better!

My idea, was for a solution that we used mutch time ago

In each columns, 7 IF and then Concatenate these columns.