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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,484
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,148
Messages
5,835,680
Members
430,374
Latest member
PeteBrown

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