Please help me with the formulae
- Thread starter hoanhnnc
- Start date
Please help me excel formulae
This We can use sumif function but so complicated to set up formulae. I please explain as follows;
We heave to types of delivers postal: Normal and urgent services.
Normal We have Internal price of province and external price(Price Regulation sheet)
Request: how to calculate the different prices depend on Total price in price calculation for Main sheet.
Thanks,
p45cal
Well-Known Member
The attached is work-in-progress which at this stage needs input from you.
On the Main sheet, there are some results in columns O and P which seem to tally fairly well with your values.
What I've done:
Set up several lookup tables on the Price Regulation sheet:
1. A table to look up the area number from the province. I've made many guesses here. So you need to make it correct and perhaps make it bigger. This table is a named range called AreaFromProvince and is at J2:K14. This table is used in column N of the Main sheet.
2. A further 8 tables created from your data in column A of the Price Regulation sheet, named ranges called Area0, Area1, Area3, Area4, Area0Urgent, Area1Urgent, Area3Urgent, and Area4Urgent. You may need to extend or reduce these to cover all possible quantities. I also note that Area3 and Area4 have exaactly the same values so we may not need separate tables for them?
On the Main sheet, the formulae in columns O and P have * 1.287 added to them; this is 17% gasoline and 10% tax.
In order to put the results in one of 4 columns you need to decide where a column will be dedicated to Urgent/Normal delivery information.
Over to you.
On the Main sheet, there are some results in columns O and P which seem to tally fairly well with your values.
What I've done:
Set up several lookup tables on the Price Regulation sheet:
1. A table to look up the area number from the province. I've made many guesses here. So you need to make it correct and perhaps make it bigger. This table is a named range called AreaFromProvince and is at J2:K14. This table is used in column N of the Main sheet.
2. A further 8 tables created from your data in column A of the Price Regulation sheet, named ranges called Area0, Area1, Area3, Area4, Area0Urgent, Area1Urgent, Area3Urgent, and Area4Urgent. You may need to extend or reduce these to cover all possible quantities. I also note that Area3 and Area4 have exaactly the same values so we may not need separate tables for them?
On the Main sheet, the formulae in columns O and P have * 1.287 added to them; this is 17% gasoline and 10% tax.
In order to put the results in one of 4 columns you need to decide where a column will be dedicated to Urgent/Normal delivery information.
Over to you.
Yes Sir I revised little file as follows;
Thanks Sir