# Thread: half kilo calculations (E2000)

1. ## 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,F3-1*C3*2),IF(F3>15<30,F3-1*D3*2), IF(F3>30,F3-1*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

2. ## Re: half kilo calculations (E2000)

=IF(F3<15,F3-1*C3*2,IF(F3<30,F3-1*D3*2,F3-1*E3*2))

IF F3<15 then you get:
F3-1*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 F3-1*D3*2
Lastly if if F3>=15 (implicit from first IF) and F3>=30 (explicit from 2nd IF)
You will get: F3-1*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

3. ## 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

4. ## Re: half kilo calculations (E2000)

This one, then?

=B3+IF(F3<15,2*(F3-1)*C3,IF(F3<30,2*(F3-1)*D3,2*(F3-1)*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.

5. ## 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

6. ## 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*(H3-1)*C3,IF(H3<30,2*(H3-1)*D3,2*(H3-1)*E3))

7. ## Re: half kilo calculations (E2000)

Thanks Hans spot on as usual

Stephen

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•