Formula for calculator Table

[Sky]

Hi,

Could anyone help me.
I had a table on some rate as per below shown.
If I entered 100 it will auto take 100*0.27*1.45=39.15 as formula

Airport Dest Country Currency Min -45 +45 +100 +500
ABC SIN USD 35 0.36 0.31 0.27 0.26

Airport Description Rate Comment
USA Port to Port 39.15 (100*0.27=27*rate 1.45)
SIN Delivery Charge 30
SIN Agency Fee 45
SIN DO Fee 60
SIN TH Charge 40
SIN Permit Fee 60
SIN Wharfage 60
Total 334.15

 

[Eduardo]

Hi,
could you please explain where you enter 100, what is the number 35 under
currency, you have a column -45 and a column minimun, which criteria do you
use to select which column to apply

 

[Sky]

Hi Eduardo,

I enter the 100 next to Port to Port. - 100 mean 100kg
35 is to tell me the min cost.
-45 mean below 45kg and +45 mean above 45kg
Is there a formula to auto run once I enter 100 (100*0.27=27*rate 1.45)=39.15.
39.15 is higher than 35 so it is OK.
If the ans is 34 it should use 35 as min.

 

[Eduardo]

Hi,
I assume that you enter 100 in C4 and you want the calculation in D4, I
assume as well that -45 is in F2, 45 in G2, etc and the FX 1.45 is in D3,
change in formula to fit your needs

=IF(IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),C4*D3*H2,C4*D3*I2)))<E2,E2,IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),C4*D3*H2,C4*D3*I2))))

so in D4 enter

 

[Sky]

Hi Eduardo,

Sorry still couldn't get it.
Could kindly send me an Excel file to my email (e-mail address removed)'

 

[Eduardo]

Hi,
Do you get a message error, copy the formula as sent and then change the
values as follow
D3 is the cell where you enter your FX in your case 1.45
F2 is the cell where you have the value if it is less than 45
G2 is where you have the value for 45 kg but less than 100
H2 is the value for more or equal to 100Kg and less than 500

change the above cells for the ones as per your spreadsheet you should be fine

 

[Sky]

Thanks Eduardo.

I got it. How about if I add another 1000 - 0.25.
Is there a way guide me If i need to change any thing or add more information

 

[Eduardo]

Hi,
In that case you need to change the formula to

IF(IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),C4*D3*H2,IF(AND(C4>=500,C4<1000),C4*D3*I2,C4*D3*I2)))<E2,E2,IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),IF(AND(C4>=500,C4<1000),C4*D3*I2,C4*D3*H2,C4*D3*I2))))

 

[Sky]

Thanks Eduardo

Hi,
In that case you need to change the formula to

IF(IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),C4*D3*H2,IF(AND(C4>=500,C4<1000),C4*D3*I2,C4*D3*I2)))<E2,E2,IF(C4<45,C4*D3*F2,IF(AND(C4>=45,C4<100),C4*G2*D3,IF(AND(C4>=100,C4<500),IF(AND(C4>=500,C4<1000),C4*D3*I2,C4*D3*H2,C4*D3*I2))))