Thread: Tariff formula assistance please

Good afternoon

Daddy Longlegs kindly gave me a solution to a tariff problem that I encountered

=IF(O13="","",CHOOSE(MATCH(O13,{0,0.5,5,20,30}),D13,D13+F13*CEILING(O13*2-1,1),O13*L13,O13*M13,O13*N13))

In another tariff I have a slightly different structure which is

B10 = First kilo charge (this is always charged + any additional Kilos)
C10 = Rate per kilo for less than 10 kilos
E10 = +10 kilos
F10 = +20 kilos
G10 = + 30 kilos
H10 = + 45 kilos

If then for example I had 25 kilos the result would be B10+24*F10, in my normal crude way I have tried to express this in a formula (that needless to say does not work) to show you what I mean

=IF(J10<10,B10+C10*J10-C10,IF(J10>10<19,B10+E10*J10-E10,IF(J10>20<30,B10+F10*J10-F10,IF(J10>30<45,B10+G10*J10-G10,IF(J10>45,B10+H10*J10-H10)))))

I tried to adapt the Daddy Longlegs formula to reflect this

IF(J10="","",CHOOSE(MATCH(J10,{0,1,10,20,30,45} but I got this far and everyhting else I tried just did not help

Any suggestions gratefully received

Danny

Could you perhaps explain what the last topic is.
An example would be very helpful.

what do you have in cell J10 ?
can you post your sheet with what exactly you want?
Can you also clarify if you want to calculate based on differential values, ex: for 25 kilos, base cost + cost for first 10 kilos + cost for next 10 kilos + cost for next 5 kilos OR

base cost + cost for 25 kilos?

Thanks for your response

This was the content of the original post to which daddy longlegs gave me the answer, I did try to find out how to mention this in my new posting but I cannot see a post number anywhere?

Thanks

Hello

I have a rates tariff which I would like to automate,

The tariff structure is

First half kilo : Per half kilo up to 5 : +5 kilos : +20 kilos : + 30 kilos

I am trying to do so by using something like this:-

=IF(O13<0.5,D13,IF(O13>0.5<5,13*F13*2+D13-F13,IF(O13>5<20,O13*L13,IF(O13>20<30,O13*M13,IF(O13>30,O13*N13)))))

Which does not work.

Basically I want the user to enter a weight into O13 and then have the rate calculated as follows

1. if the weight in O13 is less than 0.5 the rate in D13 applies
2. If the weight is greater than 0.5 the rate is the first half kilo (D13) + the rate for every half kilo up to 5 kilos (if somebody shipped 3 kilos the rate would be D13 -first half) + 2.5 kilos * F13 * 2)
3. if the weigh is >5 kilos but <20 kilos the flat rate is O13*L13
4. If the weight is >20 kilos but <30 kilos the flat rate is O13*M13
5. if the weight is >30 kilos the flat rate is 013*N13

Could somebody help me to restructure my formula so that it works

Thank you very much

Lets assume your costs are:

- base cost: 100
- first 5 kilos is 5 dollars or whatever
- 5 - 20: 10
- 20 - 30: 20
- above 30: 30

use the following formula. You enter the kilo value in E9

=100+IF(E9>30;30*(E9-0.5);IF(E9>20;20*(E9-0.5);IF(E9>5;10*(E9-0.5);(5*E9))))

see if this is what you want, if not, we can modify
would be good if you can paste some examples

Thanks Ron

That is working perfectly

Danny