Results 1 to 7 of 7
Thread: half kilo calculations (E2000)

20041101, 11:23 #1
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
half kilo calculations (E2000)
I am working on a courier tariff that has a charge for the first full kilo, a half kilo rate for weights under 15, a half kilo rate for weights between 15 and 30 and then a half kilo rate for weights above 30 kilos, I have the following columns
(a) destination ([img]/forums/images/smilies/cool.gif[/img] first kilo © <15 per half (d) >15<30 per half (e) >30 per half (f) Enter Weight (g) Total Charge
I have tried the following
=IF(F3<15,F31*C3*2),IF(F3>15<30,F31*D3*2), IF(F3>30,F31*E3*2) the 1 is to compensate for the first full kilo charge in Column B
Which returns a #VALUE error and I cannot see why, also typing this I realise that I will have a problem because my formula will always charge for a full kilo because of the *2 instruction.
Any help (as usual) greatfully received
Stephen

20041101, 11:32 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: half kilo calculations (E2000)
How about this:
=IF(F3<15,F31*C3*2,IF(F3<30,F31*D3*2,F31*E3*2))
IF F3<15 then you get:
F31*C3*2
If F3>=15 then you go to the 2nd IF:
So if F3>=15 (implicit from first IF) and F3<30 (explicit from 2nd IF)
Then F31*D3*2
Lastly if if F3>=15 (implicit from first IF) and F3>=30 (explicit from 2nd IF)
You will get: F31*E3*2
You could also use:
=F3  2* IF(F3<15,C3,IF(F3<30,D3,E3))
Which should give you the same result. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> the "1" does nothing in the calcs.
If this is not correct logic, could you give some example calcs and how you would do them manually so we could figure the formula from examples?
Steve

20041101, 11:46 #3
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: half kilo calculations (E2000)
Hi Steve
Perhaps the attachment shows a clearer example of how I would like this to work, basically I have a first kilo rate of say

20041101, 12:19 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: half kilo calculations (E2000)
This one, then?
=B3+IF(F3<15,2*(F31)*C3,IF(F3<30,2*(F31)*D3,2*(F31)*E3))
You may want to use <= instead of <, it is not clear from your description what happens if the weight is exactly 15 or 30 kg.
The formula assumes that if the weight is over 15 kg, everything except the first kg is charged at the "Over 15 (per half)" rate, and similarly if the weight is over 30 kg, as in your example.

20041101, 12:59 #5
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: half kilo calculations (E2000)
Thank you Hans but in a way that works to perfectly in so far as if a user was to say input a weight of 14.5 kilos to say Belguim this formula correctly returns the result of

20041101, 13:30 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: half kilo calculations (E2000)
Put the following formula in H3 and fill down:
=CEILING(F3,0.5)
This rounds up the weight to the nearest half kg. Then change the formula for the tariff to
=B3+IF(H3<15,2*(H31)*C3,IF(H3<30,2*(H31)*D3,2*(H31)*E3))

20041102, 10:14 #7
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: half kilo calculations (E2000)
Thanks Hans spot on as usual
Stephen