# Thread: Complicated (for me) formula assistance required

1. Good morning

I am after some help with a difficult formula please

1 have a Combo linked to cell D2 which takes its value from C56:C140

I have a cell D14 into which the weight is entered

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

2. [quote name='Danny Williams' post='792653' date='09-Sep-2009 12:38']Good morning

I am after some help with a difficult formula please

1 have a Combo linked to cell D2 which takes its value from C56:C140

I have a cell D14 into which the weight is entered

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.

3. [quote name='prasad' post='792659' date='09-Sep-2009 10:37']I dont understand what exactly you are looking for but this may help u a bit to get idea.[/quote]

Thank you for your help and advice. I cannot apply your example to my workbook but this is probably because I poorly explained my requirement.

I hope that the attachment will shed mor light on what is required

Thanks you

4. [quote name='Danny Williams' post='792663' date='09-Sep-2009 16:02']Hi Prasad

Thank you for your help and advice. I cannot apply your example to my workbook but this is probably because I poorly explained my requirement.

I hope that the attachment will shed mor light on what is required

Thanks you[/quote]

I dont know whether it will work for you. I have got results with some additional columns.

5. [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]

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

Thanks again

6. Hope this will fulfill the requirement.

I am truly gratefull for this, I will study it and learn how to do it for myself. One question, how do you get the - to appear in the columns H, I and J when they already contain a formula?

Many, many thanks

8. [quote name='Danny Williams' post='792959' date='11-Sep-2009 16:19']Hi Prasad

I am truly gratefull for this, I will study it and learn how to do it for myself. One question, how do you get the - to appear in the columns H, I and J when they already contain a formula?

Many, many thanks[/quote]
Format the cell in comma style will convert the "0" in to "-".

9. 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 :

=IF(D14=1,VLOOKUP(D10,FreightChart,2,0),IF(AND(D14 >1,D14<=100),VLOOKUP(D10,FreightChart,2,0)+(D14-1)*VLOOKUP(D10,FreightChart,3,0),((D14-100)*VLOOKUP(D10,FreightChart,4,0))+(99*VLOOKUP(D1 0,FreightChart,3,0))+VLOOKUP(D
10,FreightChart,2,0)))+J11

Pl note that FreightChart is defined name of Range D56:G140. You can replace defined name easily from formula with inserting range.

#### Posting Permissions

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