How to Create an Excel Distance Matrix Between ZIP Codes in WPS Office
Question details
The user needs to calculate straight-line distances between many ZIP codes and organize the results into a two-dimensional Excel distance matrix using latitude and longitude data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a ZIP code list with latitude and longitude data and wants to compare every ZIP code pair without manually calculating each distance.
- Observed behavior
- A distance matrix is needed from coordinate pairs; the current worksheet does not yet display the full row-and-column comparison.
Prepare a ZIP code table with one row per ZIP code and separate columns for ZIP code, latitude, and longitude. Check whether your WPS Office version supports LAMBDA; if it does not, use the direct Haversine formula shown in the second solution.
Build the distance matrix with a reusable LAMBDA function
Use this method when you want a reusable, compact distance formula that you can apply across a large ZIP code matrix without repeating the Haversine calculation manually.
The LAMBDA function stores the Haversine formula under a single name. After you define it once, the same name can calculate any coordinate pair in the workbook. This formula calculates straight-line distance, not driving distance.
Create a sheet named ZIPData and enter ZIP codes in column A, latitude in column B, and longitude in column C starting at row 2.
In WPS Office Spreadsheets, select the Formulas tab and click Name Manager.
Click New, enter the name DistanceBetweenZips, and paste this formula into the Refers To box: =LAMBDA(lat1,lon1,lat2,lon2,ACOS(COS(RADIANS(90-lat1))*COS(RADIANS(90-lat2))+SIN(RADIANS(90-lat1))*SIN(RADIANS(90-lat2))*COS(RADIANS(lon1-lon2)))*3959)
On a new sheet, enter the ZIP code list vertically starting at B5 and the same ZIP codes horizontally starting at C4.
In cell C5 enter =DistanceBetweenZips(INDEX(ZIPData!$B$2:$B$200,MATCH($B5,ZIPData!$A$2:$A$200,0)),INDEX(ZIPData!$C$2:$C$200,MATCH($B5,ZIPData!$A$2:$A$200,0)),INDEX(ZIPData!$B$2:$B$200,MATCH(C$4,ZIPData!$A$2:$A$200,0)),INDEX(ZIPData!$C$2:$C$200,MATCH(C$4,ZIPData!$A$2:$A$200,0))). Press Enter, then drag the fill handle across the row and down the column to fill the complete matrix.
Use a direct Haversine formula without LAMBDA
Use this method if your WPS Office version does not support LAMBDA or if you prefer a cell formula that does not require a named function.
Create the ZIP code distance matrix directly in WPS Office
WPS Office Spreadsheets can complete the entire distance matrix workflow: define the LAMBDA function, build the row-and-column matrix, and fill the Haversine results without leaving the spreadsheet.
- 1. Open your ZIP code workbook: Open the workbook containing ZIP code, latitude, and longitude data in WPS Office Spreadsheets.
- 2. Define the distance function: Go to Formulas > Name Manager and define the DistanceBetweenZips LAMBDA formula.
- 3. Build the matrix layout: Create row and column headings with the ZIP codes you want to compare on a new worksheet.
- 4. Apply the matrix formula: Enter the matrix formula in the first result cell and drag the fill handle across and down to populate all ZIP code pairs.
- 5. Save the workbook: Save the workbook as .xlsx so it remains compatible with Microsoft Excel and other spreadsheet applications.
Frequently Asked Questions
Does WPS Office support the LAMBDA function for a distance matrix?
Yes, recent WPS Office Spreadsheets versions support LAMBDA. If your version returns #NAME?, open a blank spreadsheet and check for updates, or use the direct Haversine formula described in this article.
Which formula calculates straight-line distance between two ZIP codes?
Use the Haversine formula with latitude and longitude. In WPS Office you can store it as a LAMBDA name and multiply the result by 3959 for miles or 6371 for kilometers.
How do I fill a large ZIP code matrix without dragging every cell?
Enter the formula in the top-left result cell, then use the fill handle to drag across and down. You can also copy the cell, select the entire result range, and paste to fill the matrix.
Why does my matrix show the same values above and below the diagonal?
Straight-line distance is symmetric: the distance from ZIP A to ZIP B equals the distance from ZIP B to ZIP A. You can keep the full matrix or show only one triangular half to reduce duplicate values.
Can I display distances in kilometers instead of miles?
Yes, change the Earth radius multiplier in the formula from 3959 to 6371. In the LAMBDA definition or direct formula, replace 3959 with 6371 before filling the matrix.
