Results 1 to 9 of 9
  1. #1
    Star Lounger
    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)+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. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Attached Files Attached Files
    Regards
    Prasad

  3. #3
    Star Lounger
    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='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]

    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
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Attached Files Attached Files
    Regards
    Prasad

  5. #5
    Star Lounger
    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='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

    Thanks again

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hope this will fulfill the requirement.
    Attached Files Attached Files
    Regards
    Prasad

  7. #7
    Star Lounger
    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

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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 "-".
    Regards
    Prasad

  9. #9
    3 Star Lounger
    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)+(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.
    Regards
    Prasad

Posting Permissions

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