Help with a rates formula please

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Help with a rates formula please

Post by steveh »

Cross Post

This post has been posted on another forum already without a resolution yet

Good morning

I need some help please in revamping a formula for a tariff

I was going to try and explain everything here but it got too complicated. I have attached a copy of the workbook which calculates a rate based on a table in the rates and surcharges tab, however my supplier has now simplified the tariff structure to a base rate for 1 kilo and then a rate per half a kilo thereafter so I would only need to reference A4:A5 and B4:F5.

There are about 5 different calculations (highlighted in red) in the calculation tab but I honestly can't remember how it got that complicated.

Steve
You do not have the required permissions to view the files attached to this post.
Last edited by HansV on 26 Jan 2010, 15:09, edited 1 time in total.
Reason: to correct spelling in subject
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with a rates formula please

Post by HansV »

Does this formula in E32 do what you want?

=INDEX('Rates + Surcharge'!$J$4:$N$4,C32)+INDEX('Rates + Surcharge'!$J$5:$N$5,C32)*ROUNDUP(2*MAX(D32-1,0),0)
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Help with a rates formula please

Post by steveh »

Cheers Hans

Bang on the money

Thanks

Steve
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin