#### hoanhnnc

##### New Member
Dear All,

I want to calculate formulae under Price regulation sheet for Main Sheet.

Thanks

#### Attachments

• 60.5 KB Views: 7

#### hoanhnnc

##### New Member
Dear All,

I want to calculate formulae under Price regulation sheet for Main Sheet.

Thanks

#### pecoflyer

##### Active Member
A little patience please. You can't expect people helping you for free on their spare time sitting behind their keyboards waiting for you, can you?
(forums advise waiting 24hrs before bumping)

##### Active Member
try explaining your requirement, if you have to do manually for couple of records as example..

#### hoanhnnc

##### New Member
try explaining your requirement, if you have to do manually for couple of records as example..
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,

#### Attachments

• 60.5 KB Views: 3

#### 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.

#### Attachments

• 35.1 KB Views: 4

#### hoanhnnc

##### New 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.
Really thanks Sir.

This is correct with my wanting. Thanks.

#### Attachments

• 36.2 KB Views: 2

#### p45cal

##### Well-Known Member
See attached.
You've highlighted rows 48 and 68 in the Main sheet; I believe the values in cells H48 & I68 are incorrect.

#### Attachments

• 35 KB Views: 2

#### hoanhnnc

##### New Member
See attached.
You've highlighted rows 48 and 68 in the Main sheet; I believe the values in cells H48 & I68 are incorrect.
Thanks Sir