Calculate UPS Time In Transit Based on Zip Code

ngarnold

New Member
Joined
Feb 24, 2016
Messages
1
I'm trying to construct a formula that will calculate time in transit for UPS. I have a sheet "Sheet1"with tab that has a column that displays the five digit zip code "zip", and I have another tab "Sheet2" in that spreadsheet with a column for "Ground" (days in transit) and "Dest. ZIP"

The destination zip codes on Sheet2 are given as the first three digits of the zip code and (sometimes) in ranges, i.e 150-153 would represent zip codes 15000 to 15399. These are not in order, ex: 407-409 = 3, 260-261 = 2, 036 = 4

The columns on Sheet2 look like this (for example):
WGJJ9ge.jpg


I want to make a formula that queries the cell "zip" on Sheet1, and returns a value "Ground" from Sheet2.

So if the zip code on Sheet1 in cell A2 is 46777, the formula would display 2 for the number of days in transit. If the zip code in Sheet1 A3 were 21525, the formula would display 3, if the zip is 14200 in Sheet1 A4 then 3...

Not sure if leading zeros on zip codes will cause an error? Presently those cells are set to display as plain text.

You can get the zone chart from UPS website here: https://www.ups.com/content/us/en/s...r+Shipping+Within+the+U.S.+and+to+Puerto+Rico
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
I hope I understood your objective.

The Dest_ZIP characters in column B are text and must be entered so (for example, the 215 is actually entered as '215); they also must be either 3 characters long or 7 characters long. The look-up values in column D are numbers and must be entered so.

Try this formula which relies on an array operation:

ABCDE
1GroundDest_ZIPlook-upGround
22464-473467772
33140-143215253
43147142003
53154-16836254
63215
73407-409
82260-261
94036

<tbody>
</tbody>
Sheet5

Array Formulas
CellFormula
E2{=INDEX($A$2:$A$9,MATCH(TRUE,($D2>=100*LEFT($B$2:$B$9,3))=($D2<=IF(LEN($B$2:$B$9)=3,100*LEFT($B$2:$B$9,3)+99,RIGHT($B$2:$B$9,3)*100)),0))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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
Top