Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Problem (again, sorry) (E2000)

    Hi

    Thanks for everybodies help so far in helping me construct an interactive tariff for my collegues to use, however I have been thrown a curved ball and I cannot get my head around it:

    Scenario

    I have a list box to choose a destination which is used in a formula to calculate charges based on weight, unlike my previous tariffs the rates are fixed between weight breaks, for example

    Minimum =

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Problem (again, sorry) (E2000)

    Hi Stephen

    You can use a VLOOKUP formula to achieve this without any IF statements. See the attached example

    <code>=VLOOKUP(E1,A1:B24,2,TRUE)</code>


    By using TRUE if an exact match is not found, the next largest value that is less than lookup_value is returned. You can omit the lasat parameter as it defaults to TRUE.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Problem (again, sorry) (E2000)

    You can use a simple VLOOKUP for this:

    <table border=1><td>Weight</td><td>Tariff</td><td align=right>0</td><td align=right>500</td><td align=right>1100</td><td align=right>550</td><td align=right>1200</td><td align=right>600</td><td align=right>1300</td><td align=right>650</td><td align=right>1400</td><td align=right>700</td><td align=right>1500</td><td align=right>750</td><td align=right>2000</td><td align=right>800</td><td align=right>2500</td><td align=right>850</td><td align=right>...</td><td align=right>...</td><td align=right>10000</td><td align=right>1600</td><td align=right>10500</td><td align=right>1650</td></table>
    Say that this is A2:B25. With the weight in D39, the formula becomes

    =VLOOKUP(D39,A2:B25,2,TRUE)

    If the progression is completely regular, you could also use this formula, without needing a lookup table:

    =IF(D39<1000,500,IF(D39<1500,FLOOR(D39,100)/2,FLOOR(D39,500)/10+600))

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (again, sorry) (E2000)

    Thank you both for your answers, as usual I am probably not concise in explaining what I am trying to achieve, I have made a dummy workbook to try and show what I am doing rather than trying to explain. An added problem that has just been explained to me is that

    1100 kilos = 1.1 cubic metres, 1200 = 1.2, 1300 = 1.3 etc. therefore if a shipment weighed 1100 kilos but the volume calculator showed that there were 3 cubic metres the rate for +3000 would apply.

    Thanks for your help

    Stephen

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Problem (again, sorry) (E2000)

    How about:
    =INDEX($C$44:$Y$62,MATCH(B14,$A$44:$A$62),MATCH(MA X(F37*1000,E37),$C$43:$X$43)+1)

    FYI:
    =E17+E18+E19+E20+E21+E22+E23+E24+E25+E26+E27+E28+E 29+E30+E31+E32+E33+E34+E35
    =F17+F18+F19+F20+F21+F22+F23+F24+F25+F26+F27+F28+F 29+F30+F31+F32+F33+F34+F35

    are easier written as
    =sum(E17:E35)
    =sum(F17:F35)

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Problem (again, sorry) (E2000)

    Hi Stephen

    If I understand the problem then the attached example should work

    I have changed Cell D39 to <code>=MAX(E36,F36*1000)</code> to make it look up the maximum of the actual weight and cubic metres*1000
    The lookup formula becomes
    <code>=INDEX(B44:Y62,MATCH(B14,A44:A62,0),MATCH(D3 9,B43:Y43,1))</code>
    This combines the INDEX and MATCH functions to perform a 2 way lookup
    I also changed cell B43 to 0 to make the formula work.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Problem (again, sorry) (E2000)

    Steve. That is awesome, thank you so much for the spot on and speedy response. One more question though if you can spare the time, there are 2 other charges in column AA43 and AD44 that need to be added onto the freight rate, I tried adding to the end of your formula +$AA$44+$AD$44 but it did not do the trick.

    Thanks for everyones help

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Problem (again, sorry) (E2000)

    Add:
    .... + INDEX($AA$44:$AA$62,MATCH(B14,$A$44:$A$62)) + INDEX($AD$44:$AD$62,MATCH(B14,$A$44:$A$62))

    to the current formula. These lookup the values from those columns in the selected row.

    Steve

Posting Permissions

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