column E56:E140 contains a base rate for 1 kilo
column F56:F140 contains a rate for any additional kilos up to 100 kilos
column G56:G140 contains a rate for any additional kilos over 100 kilos
+ J11

I have firstly tried to imagine if I was calculating for 1 row only but I can't even get that far =IF(D14<100,E56+(D14*F56)+J11-F56,IF(D14>100,E56+(D14*F56)+(D14*G14)-(F56*100)+J11-F56)) under 100 it works fine but the over 100 bit goes totally to pot.

I have been trying everything I can think of since about 3.30am so any advice or assistance will be appreciated

column E56:E140 contains a base rate for 1 kilo
column F56:F140 contains a rate for any additional kilos up to 100 kilos
column G56:G140 contains a rate for any additional kilos over 100 kilos
+ J11

I have firstly tried to imagine if I was calculating for 1 row only but I can't even get that far =IF(D14<100,E56+(D14*F56)+J11-F56,IF(D14>100,E56+(D14*F56)+(D14*G14)-(F56*100)+J11-F56)) under 100 it works fine but the over 100 bit goes totally to pot.

I have been trying everything I can think of since about 3.30am so any advice or assistance will be appreciated[/quote]

I dont understand what exactly you are looking for but this may help u a bit to get idea.

[quote name='prasad' post='792674' date='09-Sep-2009 13:15']I dont know whether it will work for you. I have got results with some additional columns.[/quote]
Hi Prasad

I truly appreciate your help and patience but again I think that I am not explaining enough for you and I do not have enough knowledge to clearly demonstrate what I am after, if I may use layman terms:

The user selects a country from the dropdown box
A mileage is place in the mileage box (this is calculated in cell J11 @ £1.50 per kilo)
A (maybe a match or indirect formula?) would match the selected rates in columns E, F and G56 to match the country opf destination and the subsequent rates

As a result if I selected Japan from the Country dropdown I would have

E56 - 1st kilo £183, F56 each kilo from 2 to 100 would be £8.00 and for shipments above 101 kilos it would be £7.00 per kilo

So, for example, if I had a 1 kilo shipment the charge would be any mileage in J11 for example 50 miles + the 1st kilo rate of £183. So the answer would be £183+£75=£258

If I had say 51 kilos the calculation would be the first kilo charge of £183 +50kilos (because the 1st kilos is already included in the £183) x £8 (F56) + mileage example 50 miles the answer would be £183 + £400 (50*£8) + £75 (mileage) = total £658

If then I had 137 kilos the calculation would be the 1st kilo of £183 + 99 kilos x £8.00, 37 kilos x £7 (F56) + mileage ecample of 50 miles in J11. So the answer to this would be £183 + £792 (99 kilosx£8) + £259 (37 kilos at the +100 kilos rate of £7) + £75 mileage = total £1309

I hope now that I have made it a bit clearer but I fully understand if you cannot help any further

If you are not comfortable with additional column approach, you can paste the following formula directly in D15 to get the desired results, but it is somehow lengthy and complicated and need more concentration to undeerstand :