Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to choose the correct table for my template
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to choose the correct table for my template
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) -- Cheers, RyanR --- pls click yes if I''ve helped you --- "Sky" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula to choose the correct table for my template
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. "RyanR" wrote: 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) -- Cheers, RyanR --- pls click yes if I''ve helped you --- "Sky" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't choose an imput cell for my data table in another worksheet | Excel Discussion (Misc queries) | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
need CORRECT amortization template please | Excel Discussion (Misc queries) | |||
correct date/days in a template | New Users to Excel | |||
How do I make a template to choose a new school name? | New Users to Excel |