Excel/OpenOffice Formula

[Jewlio Rides Again LLB]

I have a flat rate price list for moving consignments for up to a certain weight.

I also have a tiered price list based on actual weight.

1-10 kg = £1.
11-20 kg = £2.
21-100kg = 30p/kg.
101kg + = 35p/kg.

Is there a formula I can use to show the weight at which the tiered price list meets the flat rate price list.

From memory, say flat rate is £28.

Tiered price list = £2 for first 20kg, plus 30p/kg, so in this case, the weight at which the flat rate meets the tiered rate is around 106.67kg.

I want to make it so that we can change the price rate per kg when our rates change, and everything changes automatically, rather than going through each and every different rate and manually doing it?

Cheers
____________________
Mpd72: I can categorically say i’m Brighter than that, no matter how I come across on here.
HAHAHA HAHAHA Blew Chilly MyCrowSystems

[orac]

does the entire rate change when you get there?

so far as I can tell
£28-£2 = £26 (£2 for the first 20 kg)
£26/£0.30p = 86.6kg + the first 20kg

yet you say that 101kg is 35p/kg.

flat rate of £28, is that inclusive of the charge per kg
if the rate changes the pence per kilo it should be fairly easy with an if statement link to some cells holding each of the rates.
____________________
Current rides - 2016 Triumph Street Triple Rx, 1994 Suzuki Bandit 400 VM, TGB 204 Classic 125cc
"with nothing left to lose, there is everything to gain. It's not the size of the dog in the fight, it's the size of the fight in the dog"

[orac]

this should do what you need, for the most part at least

you can also have the logical arguments as cells making the weight boundaries easy to change, there is no reason why can have the as a decimal either, so b5<10.1 meaning as soon as it was even close to a meaningful weight over they would get charged for the next weight up
____________________
Current rides - 2016 Triumph Street Triple Rx, 1994 Suzuki Bandit 400 VM, TGB 204 Classic 125cc
"with nothing left to lose, there is everything to gain. It's not the size of the dog in the fight, it's the size of the fight in the dog"

[Jewlio Rides Again LLB]

Cheers

What I was trying to get across, as an example:

Tiered rates:

Weight -
1kg = £1
2kg = £1
3kg = £1
[...]
11kg = £2
12kg = £2
13kg = £2
[...]
21kg = £2.30
22kg = £2.60
23kg = £2.90
[...]

101kg = 100kg + 35p
102kg = 101kg + 35p

And so on

Flat rate provider is what we use to generally move larger stuff, as that covers up to 500kg. However, different areas have different rates, so it can be £30 to send something to a relatively local area, say Manchester, but £70+ to send something to Aberdeen, so in that instance, we'd use the tiered rate provider for larger weights.

From vague memory, £28 flat to Manchester would be around 98kg on the tiered rate provider, so that makes more sense to move it on flat rate provider, but £75 on flat rate to Aberdeen would work out at about 270kg on the tiered rate, so we would use anything less than 270kg on the tiered carrier in this instance.

If I've not confused you too much
____________________
Mpd72: I can categorically say i’m Brighter than that, no matter how I come across on here.
HAHAHA HAHAHA Blew Chilly MyCrowSystems

[orac]

I suspect that I have missed something as

[orac]

how complex a formulae would you like

if can calculate all of the possible outcomes you can use the min formula to find the lowest and combine it with lookup to find the cheapest rate.

but much more data will be needed for that, like all the data you can muster.
____________________
Current rides - 2016 Triumph Street Triple Rx, 1994 Suzuki Bandit 400 VM, TGB 204 Classic 125cc
"with nothing left to lose, there is everything to gain. It's not the size of the dog in the fight, it's the size of the fight in the dog"

[Jewlio Rides Again LLB]

Flat rate is £28 up to 500kg. It's a pallet haulage firm.

Tiered rate is a regular delivery firm, like fedex, tnt, DHL etc.

Basically, there will be a weight, at where the tiered rate becomes the same price as the flat rate. Let's say 98kg is where the tiered rate is £28. At 97kg we will use the tiered rate. At 99kg we will use the pallet firms flat rate.

This is harder to convey than I thought it would be
____________________
Mpd72: I can categorically say i’m Brighter than that, no matter how I come across on here.
HAHAHA HAHAHA Blew Chilly MyCrowSystems

[ScaredyCat]

I might be confused but to me it seems that your flat rate starts at 107Kg - anything below that is via dhl/fedex etc.

The cost cutoff is £28.10 @ 107Kg
____________________
Honda CBF125 ➝ NC700X
Honda CBF125 ↳ Speed Triple

[Jewlio Rides Again LLB]

No, because different postcodes have different rates via the pallet firm. If they were all £28, it wouldn't matter.

I'll get a copy of the current spreadsheet I use and show you.
____________________
Mpd72: I can categorically say i’m Brighter than that, no matter how I come across on here.
HAHAHA HAHAHA Blew Chilly MyCrowSystems

[Jewlio Rides Again LLB]

https://www.dropbox.com/s/jp8gzftwjao5t48/shipping.xlsx?dl=0
____________________
Mpd72: I can categorically say i’m Brighter than that, no matter how I come across on here.
HAHAHA HAHAHA Blew Chilly MyCrowSystems

[orac]

its hard when you don't hand over all the info.

you can use vlookup to find the postcode and the price for that postcode in the current spread sheet. use the formula I have already written or something similar to calculate the your tiered rate and display the result next to the flat rate price, its very easy for humans to compare prices like that (or use a if statement).

also with data you gave earlier, 98kg is £25.40, unless that prices you gave need to have VAT added to the total.

you need to do some work yourself, you have a formula that calculates the tiered price, you have a list of flat rate prices, you have the internet so can find how to use vlookup, you have everything you need.
____________________
Current rides - 2016 Triumph Street Triple Rx, 1994 Suzuki Bandit 400 VM, TGB 204 Classic 125cc
"with nothing left to lose, there is everything to gain. It's not the size of the dog in the fight, it's the size of the fight in the dog"