• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help me with the formulae

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)
 

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

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

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