A formula to choose the correct table for my template

[Sky]

Hi Everyone ,

Could anyone help me.
I file consist of a table and a template.
The table consist of a few Airport.
The template is to calculate the total cost
For example
Is there a way, If I entered 100 and Origin Italy it will auto take
100*0.65*2.1=136.5 and if enter 30 it will use the min 40.
But on the other hand it also need to link with the fuel and security to
convert as shown below.

Table
Airport Dest Currency Origin Min -45 +45 +100 +500
ABC SIN USD USA 35 0.36 0.31 0.27 0.26
Rate 1.45
Fuel USD 0.55/kg
Security USD 0.17/kg

Airport Dest Currency Origin Min -45 +45 +100 +500
EFG SIN EUR Italy 40 0.55 0.66 0.65 0.64
Rate 2.1
Fuel EUR 0.45/kg
Security EUR 0.20/kg

Airport Dest Currency Origin Min -45 +45 +100 +500
HIJ SIN AUD Aust 40 0.55 0.40 0.39 0.38
Rate 1.1
Fuel AUD 0.35/kg
Security AUD 0.25/kg

Template
Origin Italy
Airport Description Rate Comment
Eur Port to Port 136.5 (100*0.65*2.1)
SIN Agency Fee 45
SIN Delivery Charge 30
SIN DO Fee 60
SIN Permit Fee 60
SIN Wharfage 60
EUR Fuel 115.5 (0.55*100*2.1)
EUR Security 35.7 ( 0.17*100*2.1)
SIN Total 542.70

 

[RyanR]

Assume:
A2 = your value
A3 = minimum

=if(A2<A3,A3*0.65*2.1,A2*0.65*2.1)

also, looking at your formula, if 2.1 is the rate, consider substituting the
2.1 in the formula to a cell reference.
Where possible, if you can reference a cell rather than using a static
value, it makes updating your spreadsheet a lot easier.

Also, depending on your layout, you may need to use absolute reference (e.g.
$A$2)

 

[Sky]

Hi RyanR,

It can only calculate those more than min 40.
I wish that if those less than min 40min will also shown the value is 40(A2)
Yes $A$3 can fit to calculate but according to the below there are three
table. The current formula can only count one table is ther is formula that
if I enter the one of the country it will default calculate that country
cost.