• 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

hoanhnnc

New Member
Dear All,

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

Please help me!,

Thanks
 

Attachments

  • ems Ct.xls
    60.5 KB · Views: 7
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)
 
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

  • ems Ct.xls
    60.5 KB · Views: 3
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

  • Chandoo43387ems Ct.xlsx
    35.1 KB · Views: 5
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.
 
See attached.
You've highlighted rows 48 and 68 in the Main sheet; I believe the values in cells H48 & I68 are incorrect.
 

Attachments

  • Chandoo43387ems 13.01.2020.xlsx
    35 KB · Views: 4
Back
Top