Results 1 to 8 of 8

20041022, 07:46 #1
 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 =

20041022, 07:53 #2
 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.

20041022, 08:09 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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))

20041022, 08:49 #4
 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

20041022, 09:19 #5
 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

20041022, 09:23 #6
 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.

20041022, 10:06 #7
 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

20041022, 10:40 #8
 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