FedEx shipping rates

gimage

New Member
Joined
Apr 18, 2008
Messages
23
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

???
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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 />
 
Upvote 0
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)
 
Last edited:
Upvote 0
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
 
Upvote 0
Code:
       ---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)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top