Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Hi Everyone ,
I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Have you considered using a table where you would have things set up in
columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Hi Jlatham,
Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Ok, I'll try. You can even put this table on a sheet all by itself so that
people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Thanks JLatham,
Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Hi Jlatham,
According to the below sample the 0.65 is a fit rate cell but i need to be compare of a few cell. Can Vlookup link with IF =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 "Sky" wrote: Thanks JLatham, Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
You can use VLOOKUP() with IF() statements, yes.
=B1 * IF(VLOOKUP(....)<X4,VLOOKUP(....),VLOOKUP(....)) Think of VLOOKUP as a single value, and it can be used in formulas just like any single cell or value can be used. All of those VLOOKUP()s in my pretend formula above wouldn't have to be VLOOKUP()s, could have been something like: =IF(G13<45,VLOOKUP(a1,G9:J99,2,False),0) * g13 * f2 or something like that even. "Sky" wrote: Hi Jlatham, According to the below sample the 0.65 is a fit rate cell but i need to be compare of a few cell. Can Vlookup link with IF =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 "Sky" wrote: Thanks JLatham, Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Thanks Jlatham, I had a table consist of three Country. Each country had a few rate to compare. First of all, on my template I need to choose a correct country. From there it will compare the rate. For example on my template A B C D E F G H I J 1 Country Airport Min -45 45 100 500 1000 Rate 2 Austria GHI 50 0.5 0.4 0.3 0.2 0.1 1.05 3 Italy DEF 40 0.4 0.35 0.34 0.33 0.32 2.2 4 USA ABC 35 0.36 0.31 0.27 0.26 0.25 1.45 A6 B6 Country Italy Weight 90kg Calculation is I need to compare D1 to I1. 90kg is fall under the range of F1 so 0.35 is the rate to be choose. Ans 0.35*90*2.2=69.3 if the ans is less than 40 it will also shown 40(ans cannot less than 40) Currently I only compare manually on Italy I how that the country is link to the below formula. If it enter USA it will auto use USA column to calculate. I am trying to do this is due to I had 30-40 country to calculate. =IF(IF(B6<45,B6*I3*D3,IF(AND(B6=45,B6<100),B6*E3* I3,IF(AND(B6=100,B6<500),B6*F3*I3,IF(AND(B6=500, B6<1000),B6*I3*G3,B6*I3*H3))))<C3,C3,IF(B6<45,B6*I 3*D3,IF(AND(B6=45,B6<100),B6*E3*I3,IF(AND(B6=100 ,B6<500),B6*F3*I3,IF(AND(B6=500,B6<1000),B6*I3*G3 ,B6*I3*H3))))) "JLatham" wrote: You can use VLOOKUP() with IF() statements, yes. =B1 * IF(VLOOKUP(....)<X4,VLOOKUP(....),VLOOKUP(....)) Think of VLOOKUP as a single value, and it can be used in formulas just like any single cell or value can be used. All of those VLOOKUP()s in my pretend formula above wouldn't have to be VLOOKUP()s, could have been something like: =IF(G13<45,VLOOKUP(a1,G9:J99,2,False),0) * g13 * f2 or something like that even. "Sky" wrote: Hi Jlatham, According to the below sample the 0.65 is a fit rate cell but i need to be compare of a few cell. Can Vlookup link with IF =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 "Sky" wrote: Thanks JLatham, Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Hi JLatham,
Is it possible to help on this? "Sky" wrote: Thanks Jlatham, I had a table consist of three Country. Each country had a few rate to compare. First of all, on my template I need to choose a correct country. From there it will compare the rate. For example on my template A B C D E F G H I J 1 Country Airport Min -45 45 100 500 1000 Rate 2 Austria GHI 50 0.5 0.4 0.3 0.2 0.1 1.05 3 Italy DEF 40 0.4 0.35 0.34 0.33 0.32 2.2 4 USA ABC 35 0.36 0.31 0.27 0.26 0.25 1.45 A6 B6 Country Italy Weight 90kg Calculation is I need to compare D1 to I1. 90kg is fall under the range of F1 so 0.35 is the rate to be choose. Ans 0.35*90*2.2=69.3 if the ans is less than 40 it will also shown 40(ans cannot less than 40) Currently I only compare manually on Italy I how that the country is link to the below formula. If it enter USA it will auto use USA column to calculate. I am trying to do this is due to I had 30-40 country to calculate. =IF(IF(B6<45,B6*I3*D3,IF(AND(B6=45,B6<100),B6*E3* I3,IF(AND(B6=100,B6<500),B6*F3*I3,IF(AND(B6=500, B6<1000),B6*I3*G3,B6*I3*H3))))<C3,C3,IF(B6<45,B6*I 3*D3,IF(AND(B6=45,B6<100),B6*E3*I3,IF(AND(B6=100 ,B6<500),B6*F3*I3,IF(AND(B6=500,B6<1000),B6*I3*G3 ,B6*I3*H3))))) "JLatham" wrote: You can use VLOOKUP() with IF() statements, yes. =B1 * IF(VLOOKUP(....)<X4,VLOOKUP(....),VLOOKUP(....)) Think of VLOOKUP as a single value, and it can be used in formulas just like any single cell or value can be used. All of those VLOOKUP()s in my pretend formula above wouldn't have to be VLOOKUP()s, could have been something like: =IF(G13<45,VLOOKUP(a1,G9:J99,2,False),0) * g13 * f2 or something like that even. "Sky" wrote: Hi Jlatham, According to the below sample the 0.65 is a fit rate cell but i need to be compare of a few cell. Can Vlookup link with IF =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 "Sky" wrote: Thanks JLatham, Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any sugguestion to set up this Calculation?
Give me a little time with this one! You may want to get in touch with me
directly, and if possible, send me a copy of the workbook as an email attachment. If you want to go that route, my email address is (make obvious fixes to make it a good address: HelpFrom [at] JLathamSite [dot] com "Sky" wrote: Hi JLatham, Is it possible to help on this? "Sky" wrote: Thanks Jlatham, I had a table consist of three Country. Each country had a few rate to compare. First of all, on my template I need to choose a correct country. From there it will compare the rate. For example on my template A B C D E F G H I J 1 Country Airport Min -45 45 100 500 1000 Rate 2 Austria GHI 50 0.5 0.4 0.3 0.2 0.1 1.05 3 Italy DEF 40 0.4 0.35 0.34 0.33 0.32 2.2 4 USA ABC 35 0.36 0.31 0.27 0.26 0.25 1.45 A6 B6 Country Italy Weight 90kg Calculation is I need to compare D1 to I1. 90kg is fall under the range of F1 so 0.35 is the rate to be choose. Ans 0.35*90*2.2=69.3 if the ans is less than 40 it will also shown 40(ans cannot less than 40) Currently I only compare manually on Italy I how that the country is link to the below formula. If it enter USA it will auto use USA column to calculate. I am trying to do this is due to I had 30-40 country to calculate. =IF(IF(B6<45,B6*I3*D3,IF(AND(B6=45,B6<100),B6*E3* I3,IF(AND(B6=100,B6<500),B6*F3*I3,IF(AND(B6=500, B6<1000),B6*I3*G3,B6*I3*H3))))<C3,C3,IF(B6<45,B6*I 3*D3,IF(AND(B6=45,B6<100),B6*E3*I3,IF(AND(B6=100 ,B6<500),B6*F3*I3,IF(AND(B6=500,B6<1000),B6*I3*G3 ,B6*I3*H3))))) "JLatham" wrote: You can use VLOOKUP() with IF() statements, yes. =B1 * IF(VLOOKUP(....)<X4,VLOOKUP(....),VLOOKUP(....)) Think of VLOOKUP as a single value, and it can be used in formulas just like any single cell or value can be used. All of those VLOOKUP()s in my pretend formula above wouldn't have to be VLOOKUP()s, could have been something like: =IF(G13<45,VLOOKUP(a1,G9:J99,2,False),0) * g13 * f2 or something like that even. "Sky" wrote: Hi Jlatham, According to the below sample the 0.65 is a fit rate cell but i need to be compare of a few cell. Can Vlookup link with IF =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 "Sky" wrote: Thanks JLatham, Can Vlookup compare a few cell? For example for B P2PRate I need to compare a few cell. Can vlookup this cell (IF(G13<45,G13*D3*F2) "JLatham" wrote: Ok, I'll try. You can even put this table on a sheet all by itself so that people don't have to look at it or be tempted to mess with it (you can even hide the sheet). For example's sake, we will say that sheet has been named CostTableSheet On the sheet, you probably set up the first row with labels to describe what is under them in the column. Since we're limited on space here, I'm only going to set up a few columns: A B C D 1 Origin P2PRate Fuel Rate SecRate 2 Italy .65 .55 .17 3 USA .36 .45 .26 4 Austria .55 .35 .25 I may have numbers wrong, but the idea is to show you how to get information out of the table. In this case the address of our table is, CostTableSheet!$A$2:$D$4 On another sheet, someone choses "Italy" from a list, or types it into a cell, and that cell is A1 on that sheet. They also enter 100 into another cell, say B1 on that sheet. You could have a formula like =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * 2.1 which would be the equivalent of = 100 * .65 * 2.1 You can substitute other VLOOKUP() formulas for any part of the formula - perhaps the 2.1 changes and is in column E on the CostTableSheet, then the formula might look like: =B1 * VLOOKUP(A1,CostTableSheet!$A$2:$D$4,2,FALSE) * VLOOKUP(A1, CostTableSheet!$A$2:$D$4,5,FALSE) Notice in the two VLOOKUP() statements, the only difference is that one has ,2, and the other has ,5, the first one says "when you find a match in the table to the value in A1, return the value in the 2nd column of the table". The second one says "when you find a match in the table to the value in A1, return the value in the 5th column of the table". See Excel's help on VLOOKUP for mor information. I will say that the last parameter ,FALSE tells it that your list of countries does not have to be in order. "Sky" wrote: Hi Jlatham, Could you show me some sample on how to create a table "JLatham" wrote: Have you considered using a table where you would have things set up in columns like: Origin SINport2port SINfuel SINtotal Italy 136.5 115.5 37.5 If you're having to do the actual math, you may want to use more columns, one for each variable, such as .65, .55 and .17 because I notice that those are the variables in the formula you show, with 100 and 2.1 being constants. Although I suspect that the 2.1 is also variable (as 1.45 and 1.1 ??). But with an appropriately created table, you could use VLOOKUP() to gather the information for a country and do the math on the main sheet. Hope this helps some. "Sky" wrote: Hi Everyone , I am thinking a way to set up a template for other to use. Could anyone give some comment or suggestion to help me to calculate the cost? or May I know is there a formula to help me on my template? or Is there others ways for me to improve or amend this template? or I had a file consist of a few 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 and shown 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 SIN Port to Port 136.5 (100*0.65*2.1) SIN Fuel 115.5 (0.55*100*2.1) SIN Security 35.7 ( 0.17*100*2.1) SIN Total 287.7 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
Calculation | Excel Discussion (Misc queries) | |||
need to do a calculation. | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |