FedEx shipping rates

[gimage]

How would I calculate shipping rates for ground & Express Saver based on the first 3 digits of the zip code and the weight? FedEx has the rates and zones in Excel which I have combined into 3 sheets, zone, ground and esp.
I'd like to enter a 3 digit zip code and the weight with the result showing both ground & Express Saver rates. Some of the zip codes have a range, 010-046 for zone 5 for example. Some do not have a zone which may mean that zip code does not exist.

 

[Gerald Higgins]

For one thing, ranges may be a problem.

If you've got ranges like this
010-014
018-020

are you able to restructure the data to something like this
010
011
012
013
014
018
019
020

???

 

[gimage]

For one thing, ranges may be a problem.

If you've got ranges like this
010-014
018-020

are you able to restructure the data to something like this
010
011
012
013
014
018
019
020

???

The file from FedEx is setup that way but I can change it if it will make it easier.

 

[Gerald Higgins]

OK, so if you can re-structure it in the way I described, then it should be fairly straightforward to set up either a LOOKUP table, or a combination of MATCH and INDEX to look up the rates.

It's difficult for me to be precise at the moment without seeing your data.

Can you tell me a little more about how your data is laid out ?

 

[gimage]

http://fedex.com/ratetools/RateToolsMain.do
is where I downloaded the Excel files.
"Get Rates by FedEx Service" I downloaded FedEx Ground rates and "Get FedEx Zone Locators" I downloaded from zip code origin 60101. I combined them into 3 tabs and went from there.
Your help is greatly appreciated.
Thanks, Greg

 

[Gerald Higgins]

Sorry Greg I'm not looking at that link. Can you describe in a post please, how the data in your worksheet is laid out ?

 

[gimage]

I have 3 tabs. zone, ground & esp. The zip-code is selected to determine the zone the package is shipping to. Ground and esp are used to determine the rate for shipment and are in a grid.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Destination Zip</td><td style=";">Zone</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">000-004</td><td style=";">NA</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">010-046</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">47</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">048-129</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">130-132</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">133-139</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">140-179</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">180-199</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">200-212</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">213</td><td style=";">NA</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">214</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">215</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">216</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">217</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">218-219</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style=";">220-229</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style=";">230-238</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style=";">239-268</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">zone</p><br /><br />
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Zone 2</td><td style=";">Zone 3</td><td style=";">Zone 4</td><td style=";">Zone 5</td><td style=";">Zone 6</td><td style=";">Zone 7</td><td style=";">Zone 8</td><td style=";">Zone 9</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">$5.17 </td><td style="text-align: right;;">$5.40 </td><td style="text-align: right;;">$5.51 </td><td style="text-align: right;;">$5.75 </td><td style="text-align: right;;">$6.04 </td><td style="text-align: right;;">$6.12 </td><td style="text-align: right;;">$6.22 </td><td style="text-align: right;;">$19.18 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">$5.37 </td><td style="text-align: right;;">$5.72 </td><td style="text-align: right;;">$6.22 </td><td style="text-align: right;;">$6.34 </td><td style="text-align: right;;">$6.75 </td><td style="text-align: right;;">$6.89 </td><td style="text-align: right;;">$7.13 </td><td style="text-align: right;;">$21.30 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">3</td><td style="text-align: right;;">$5.45 </td><td style="text-align: right;;">$5.97 </td><td style="text-align: right;;">$6.53 </td><td style="text-align: right;;">$6.73 </td><td style="text-align: right;;">$7.15 </td><td style="text-align: right;;">$7.36 </td><td style="text-align: right;;">$7.88 </td><td style="text-align: right;;">$23.21 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">$5.58 </td><td style="text-align: right;;">$6.16 </td><td style="text-align: right;;">$6.86 </td><td style="text-align: right;;">$7.17 </td><td style="text-align: right;;">$7.53 </td><td style="text-align: right;;">$7.85 </td><td style="text-align: right;;">$8.44 </td><td style="text-align: right;;">$25.37 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5</td><td style="text-align: right;;">$5.79 </td><td style="text-align: right;;">$6.25 </td><td style="text-align: right;;">$7.15 </td><td style="text-align: right;;">$7.46 </td><td style="text-align: right;;">$7.83 </td><td style="text-align: right;;">$8.20 </td><td style="text-align: right;;">$8.91 </td><td style="text-align: right;;">$27.63 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">6</td><td style="text-align: right;;">$5.96 </td><td style="text-align: right;;">$6.44 </td><td style="text-align: right;;">$7.27 </td><td style="text-align: right;;">$7.65 </td><td style="text-align: right;;">$7.97 </td><td style="text-align: right;;">$8.44 </td><td style="text-align: right;;">$9.10 </td><td style="text-align: right;;">$30.01 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">7</td><td style="text-align: right;;">$6.26 </td><td style="text-align: right;;">$6.63 </td><td style="text-align: right;;">$7.41 </td><td style="text-align: right;;">$7.85 </td><td style="text-align: right;;">$8.20 </td><td style="text-align: right;;">$8.66 </td><td style="text-align: right;;">$9.40 </td><td style="text-align: right;;">$32.06 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8</td><td style="text-align: right;;">$6.51 </td><td style="text-align: right;;">$6.82 </td><td style="text-align: right;;">$7.61 </td><td style="text-align: right;;">$8.00 </td><td style="text-align: right;;">$8.46 </td><td style="text-align: right;;">$9.04 </td><td style="text-align: right;;">$9.96 </td><td style="text-align: right;;">$33.97 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;">$6.65 </td><td style="text-align: right;;">$7.01 </td><td style="text-align: right;;">$7.73 </td><td style="text-align: right;;">$8.17 </td><td style="text-align: right;;">$8.63 </td><td style="text-align: right;;">$9.51 </td><td style="text-align: right;;">$10.58 </td><td style="text-align: right;;">$36.36 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;">$6.86 </td><td style="text-align: right;;">$7.05 </td><td style="text-align: right;;">$7.87 </td><td style="text-align: right;;">$8.40 </td><td style="text-align: right;;">$9.00 </td><td style="text-align: right;;">$10.17 </td><td style="text-align: right;;">$11.28 </td><td style="text-align: right;;">$38.61 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Ground</p><br /><br />
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Weight</td><td style=";">Zone 2</td><td style=";">Zone 3</td><td style=";">Zone 4</td><td style=";">Zone 5</td><td style=";">Zone 6</td><td style=";">Zone 7</td><td style=";">Zone 8</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">FedEx® Envelope up to 8 oz.</td><td style="text-align: right;;">$10.50 </td><td style="text-align: right;;">$10.55 </td><td style="text-align: right;;">$10.60 </td><td style="text-align: right;;">$10.65 </td><td style="text-align: right;;">$12.50 </td><td style="text-align: right;;">$13.05 </td><td style="text-align: right;;">$14.60 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">$10.50 </td><td style="text-align: right;;">$10.55 </td><td style="text-align: right;;">$10.60 </td><td style="text-align: right;;">$10.65 </td><td style="text-align: right;;">$12.50 </td><td style="text-align: right;;">$13.05 </td><td style="text-align: right;;">$14.60 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">$10.55 </td><td style="text-align: right;;">$10.70 </td><td style="text-align: right;;">$10.80 </td><td style="text-align: right;;">$10.85 </td><td style="text-align: right;;">$13.40 </td><td style="text-align: right;;">$14.50 </td><td style="text-align: right;;">$16.95 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">3</td><td style="text-align: right;;">$10.60 </td><td style="text-align: right;;">$10.80 </td><td style="text-align: right;;">$11.75 </td><td style="text-align: right;;">$12.00 </td><td style="text-align: right;;">$15.50 </td><td style="text-align: right;;">$16.25 </td><td style="text-align: right;;">$19.20 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">$11.00 </td><td style="text-align: right;;">$11.15 </td><td style="text-align: right;;">$12.30 </td><td style="text-align: right;;">$13.55 </td><td style="text-align: right;;">$17.20 </td><td style="text-align: right;;">$18.55 </td><td style="text-align: right;;">$21.90 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5</td><td style="text-align: right;;">$11.15 </td><td style="text-align: right;;">$11.65 </td><td style="text-align: right;;">$13.00 </td><td style="text-align: right;;">$14.80 </td><td style="text-align: right;;">$19.50 </td><td style="text-align: right;;">$20.45 </td><td style="text-align: right;;">$24.60 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">6</td><td style="text-align: right;;">$11.80 </td><td style="text-align: right;;">$12.90 </td><td style="text-align: right;;">$14.80 </td><td style="text-align: right;;">$16.60 </td><td style="text-align: right;;">$21.70 </td><td style="text-align: right;;">$22.75 </td><td style="text-align: right;;">$27.85 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">7</td><td style="text-align: right;;">$11.85 </td><td style="text-align: right;;">$13.95 </td><td style="text-align: right;;">$15.95 </td><td style="text-align: right;;">$17.80 </td><td style="text-align: right;;">$24.10 </td><td style="text-align: right;;">$25.70 </td><td style="text-align: right;;">$30.60 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8</td><td style="text-align: right;;">$12.40 </td><td style="text-align: right;;">$15.20 </td><td style="text-align: right;;">$17.25 </td><td style="text-align: right;;">$19.15 </td><td style="text-align: right;;">$26.50 </td><td style="text-align: right;;">$28.15 </td><td style="text-align: right;;">$33.40 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;">$12.95 </td><td style="text-align: right;;">$16.05 </td><td style="text-align: right;;">$18.35 </td><td style="text-align: right;;">$20.65 </td><td style="text-align: right;;">$28.40 </td><td style="text-align: right;;">$30.85 </td><td style="text-align: right;;">$36.00 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;">$13.80 </td><td style="text-align: right;;">$17.10 </td><td style="text-align: right;;">$19.35 </td><td style="text-align: right;;">$22.20 </td><td style="text-align: right;;">$30.15 </td><td style="text-align: right;;">$33.20 </td><td style="text-align: right;;">$38.80 </td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ESP</p><br /><br />

 

[Asator]

If you structure the zone lookup as a list of zip code prefixes (first 3 digits) with the associated zone number, you could do a vlookup on the left 3 characters of the zip code to determine the zone number, which you could use to determine the vlookup offset on your weight/zone table. You'd probably want to roundup() the lookup weight, and enable range lookup on vlookup.

=vlookup(weight,weight_zone_table,lookup(vlookup(left(zip,3),zip_zone_table,2,0),{"Zone 1","Zone 2"...."Zone 5},{1,2...5}),1)

 

[gimage]

If you structure the zone lookup as a list of zip code prefixes (first 3 digits) with the associated zone number, you could do a vlookup on the left 3 characters of the zip code to determine the zone number, which you could use to determine the vlookup offset on your weight/zone table. You'd probably want to roundup() the lookup weight, and enable range lookup on vlookup.

=vlookup(weight,weight_zone_table,lookup(vlookup(left(zip,3),zip_zone_table,2,0),{"Zone 1","Zone 2"...."Zone 5},{1,2...5}),1)

I figured it would be a vlookup formula, I just don't know enough to create it. I'd like to know what formula I need for cells A5 & A6. It will be incorporated into a separate "quotes" spreadsheet. The user would only input the weight in A5 & the 3 digit zip code.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>10 lbs</TD><TD>Weight</TD><TD>Package Weight</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">970</TD><TD>Zip Code</TD><TD>Zone 7 from zone tab</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">$10.17 </TD><TD>Ground</TD><TD>from ground tab</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">$33.20 </TD><TD>Express Saver</TD><TD>from esp tab</TD></TR></TBODY></TABLE>

Sheet1​

 

[shg]

       ---A---- --B--- --C--- --D--- --E--- --F--- ---G--- ---H--- ---I--- J ---K---- -L--
   1   Dest Zip  76203                                                       Dest Zip Zone
   2   Wgt           3                                                              0 #N/A
   3   Cost     $6.73                                                               5    5
   4                                                                               10    5
   5                                                                               47    6
   6   Wgt      Zone 2 Zone 3 Zone 4 Zone 5 Zone 6 Zone 7  Zone 8  Zone 9          48    5
   7          1 $5.17  $5.40  $5.51  $5.75  $6.04  $6.12   $6.22   $19.18         130    4
   8          2 $5.37  $5.72  $6.22  $6.34  $6.75  $6.89   $7.13   $21.30         133    5
   9          3 $5.45  $5.97  $6.53  $6.73  $7.15  $7.36   $7.88   $23.21         140    4
  10          4 $5.58  $6.16  $6.86  $7.17  $7.53  $7.85   $8.44   $25.37         180    5
  11          5 $5.79  $6.25  $7.15  $7.46  $7.83  $8.20   $8.91   $27.63         200    4
  12          6 $5.96  $6.44  $7.27  $7.65  $7.97  $8.44   $9.10   $30.01         213 #N/A
  13          7 $6.26  $6.63  $7.41  $7.85  $8.20  $8.66   $9.40   $32.06         214    5
  14          8 $6.51  $6.82  $7.61  $8.00  $8.46  $9.04   $9.96   $33.97         215    4
  15          9 $6.65  $7.01  $7.73  $8.17  $8.63  $9.51   $10.58  $36.36         216    5
  16         10 $6.86  $7.05  $7.87  $8.40  $9.00  $10.17  $11.28  $38.61         217    4
  17                                                                              218    5
  18                                                                              220    4
  19                                                                              230    5
  20                                                                              239    4
  21                                                                              269 #N/A
  22                                                                              270    4

The data in cols K:L is part of the zone table from FedEx. The hyphenated ranges in col K were eliminated by selecting the column and replacing -* with nothing, which leaves simple numbers.

The formula in B3 is

=INDEX($B$7:$I$16, B2, VLOOKUP(--LEFT(B1, 3), $K$2:$L$223, 2) - 1)