The possibility of shortening this equation

[Hany ali]

Hello my Dears , Please Help me to Get the Same Result by the shortening from this equation

=IF($H8/12<3,($G8*4%)*($H8/12),IF(AND($H8/12>=3,$H8/12<6),($G8*5%)*($H8/12),IF(AND($H8/12>=6,$H8/12<7),($G8*6%)*($H8/12),IF(AND($H8/12>=7,$H8/12<8),($G8*7%)*($H8/12),IF($H8/12>=8,($G8*8%)*($H8/12),"")))))

 

[Peter Bartholomew]

= installment * LOOKUP(months/12, year, percent) * months/12

 

[Hany ali]

thanks alot mr.Peter Bartholomew
this Formula work as Well ,but when i have Months Number less than 12 ... this formula get Error #N/A

=G9*LOOKUP($H9/12,$C$8:$C$15,$B$8:$B$15)*$H9/12​

 

[bosco_yip]

Just adding a Max() for the Lookup value

In F8, formula copied down :

=G8*LOOKUP(MAX(1,$H8/12),$C$8:$C$15,$B$8:$B$15)*$H8/12

Regards
Bosco

 

[Peter Bartholomew]

True. I hadn't attached the file because I implemented the formula using Office 365.
You will see that I have inserted a 0 year threshold into the rules table.


Otherwise you would need to trap the error with IFERROR.

 

[Hany ali]

thanks alot mr.
bosco_yip
This is exactly required
thanks mr.
Peter Bartholomew
Now worksed well