Results 1 to 9 of 9

20090909, 02:08 #1
 Join Date
 May 2005
 Location
 west drayton, Middlesex, United Kingdom
 Posts
 54
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)+J11F56,IF(D14>100,E56+(D14*F56)+(D14*G14)(F56*100)+J11F56)) 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

20090909, 04:37 #2
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Danny Williams' post='792653' date='09Sep2009 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)+J11F56,IF(D14>100,E56+(D14*F56)+(D14*G14)(F56*100)+J11F56)) 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.Regards
Prasad

20090909, 05:32 #3
 Join Date
 May 2005
 Location
 west drayton, Middlesex, United Kingdom
 Posts
 54
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='prasad' post='792659' date='09Sep2009 10:37']I dont understand what exactly you are looking for but this may help u a bit to get idea.[/quote]
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

20090909, 07:15 #4
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Danny Williams' post='792663' date='09Sep2009 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.Regards
Prasad

20090909, 10:30 #5
 Join Date
 May 2005
 Location
 west drayton, Middlesex, United Kingdom
 Posts
 54
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='prasad' post='792674' date='09Sep2009 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
Thanks again

20090911, 05:05 #6
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hope this will fulfill the requirement.
Regards
Prasad

20090911, 05:49 #7
 Join Date
 May 2005
 Location
 west drayton, Middlesex, United Kingdom
 Posts
 54
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20090911, 06:09 #8
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Danny Williams' post='792959' date='11Sep2009 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 "".Regards
Prasad

20090912, 05:29 #9
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)+(D141)*VLOOKUP(D10,FreightChart,3,0),((D14100)*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.Regards
Prasad