# Thread: Formula Problem (again, sorry) (E2000)

1. ## 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. ## 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. ## 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. ## 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.

Stephen

5. ## Re: Formula Problem (again, sorry) (E2000)

=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. ## 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. ## 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