How to Create an Excel Pricing Calculator Based on Location, Delivery Type, and Weight
Question details
The user needs to build a dynamic letter-price calculator in Excel that determines the correct price using three criteria: location, delivery type, and weight.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a multi-criteria lookup calculator for pricing mail or packages based on a specific pricing table.
- Observed behavior
- The pricing table contains ambiguous or overlapping weight bands (duplicate maximum weights), preventing standard lookup formulas from returning the correct price.
Review your pricing table to ensure there are no overlapping weight bands. Define clear lower and upper limits for each location and delivery type to prevent inaccurate formula results.
Clean Up Weight Bands and Use a Lookup Formula
The most reliable method is to standardize your pricing table's weight limits and apply an XLOOKUP or FILTER function to extract the exact price.
If your reference table has duplicate values in the 'Maximum Weight' column, lookup formulas will fail or return incorrect results. You must first ensure each weight tier is mutually exclusive for a given location and delivery type.
Modify your pricing table so each tier has a distinct 'Min Weight' and 'Max Weight' column instead of overlapping limits.
Create dedicated input cells for the user to select 'Location' (e.g., Europe), 'Delivery Type' (e.g., Priority), and enter the 'Weight'.
In the result cell, enter a formula like =FILTER(PriceColumn, (LocationColumn=InputLocation) * (TypeColumn=InputType) * (MaxWeightColumn>=InputWeight) * (MinWeightColumn<=InputWeight)). This extracts the exact price matching all criteria.
Build Advanced Pricing Calculators Easily with WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced lookup functions like XLOOKUP, FILTER, and array formulas natively. You can effortlessly manage complex pricing tables and perform multi-criteria lookups while ensuring complete compatibility with Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing pricing table document.
- 2. Organize your data: Format your pricing table with clear columns for Location, Delivery Type, Min Weight, Max Weight, and Price.
- 3. Insert the lookup formula: Use the built-in Formula tab to insert XLOOKUP or FILTER functions just like you would in Excel to generate your final price.
Frequently Asked Questions
Why does my lookup formula return an #N/A error when matching weight?
An #N/A error usually occurs if the exact weight isn't found in the reference table. Ensure you are using an approximate match setup (e.g., XLOOKUP with match mode 1 or -1) or a logical check (>= and <=) if using FILTER.
Can I use VLOOKUP for multiple criteria like location and delivery type?
Standard VLOOKUP only handles one lookup value. To use VLOOKUP for multiple criteria, you must create a helper column in your pricing table that concatenates Location, Delivery Type, and Weight, and do the same in your lookup formula.
How do I create a drop-down list for selecting Location and Delivery Type?
Select the input cell, go to the Data tab, click Data Validation, choose 'List' from the Allow menu, and enter your options (e.g., Europe, Other) or select the range containing them.
What is the best formula for a tiered pricing calculator?
XLOOKUP and FILTER are the most robust functions for tiered pricing involving multiple conditions. SUMIFS can also be used if the price output is purely numeric and only one row matches the conditions.
