WPS Spreadsheet: Return Unit Price by Volume Threshold
Question details
The user needs to return the correct unit price based on a requested quantity and a set of volume thresholds.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Volume thresholds are arranged across a row, with the corresponding unit prices directly below them. The requested quantity is entered in a separate cell.
- Observed behavior
- The goal is to return the price associated with the largest threshold that is less than or equal to the requested quantity.
Confirm that the threshold values and their corresponding prices occupy matching ranges. Sort the threshold row from smallest to largest before using an approximate lookup.
Use LOOKUP to return the price for the applicable volume threshold
Use this solution when thresholds and prices are arranged horizontally in matching rows and the threshold row is sorted in ascending order.
The LOOKUP function finds the largest threshold that does not exceed the requested quantity and returns the value from the corresponding position in the price row. For example, if thresholds are in B1:G1, prices are in B2:G2, and the quantity is in C10, the formula returns the matching unit price.
In WPS Spreadsheet, click the cell where you want the unit price to appear.
Type =LOOKUP(C10,B1:G1,B2:G2) in the selected cell, then press Enter.
For a quantity of 35000 in C10, confirm that the result is 3 when 35000 is the largest threshold not exceeding the requested quantity.
Select the threshold range B1:G1 and verify that its values increase from left to right; rearrange the corresponding prices in B2:G2 if necessary.
Calculate Volume-Based Unit Prices in WPS Spreadsheet
WPS Spreadsheet lets you use LOOKUP formulas to match a requested quantity with the highest applicable volume threshold and return its corresponding unit price. The familiar spreadsheet interface makes it easy to edit ranges, verify sorted thresholds, and reuse the formula.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open your workbook or create a blank sheet.
- 2. Arrange the lookup data: Place ascending volume thresholds in one row and their corresponding unit prices in the row directly below.
- 3. Enter the formula: Click the result cell and enter =LOOKUP(C10,B1:G1,B2:G2), adjusting the cell references to match your worksheet.
- 4. Test the quantity: Enter a requested quantity in C10 and press Enter to display the price for the largest threshold that does not exceed it.
Frequently Asked Questions
Why must the volume thresholds be sorted in ascending order?
LOOKUP uses approximate matching and expects the lookup row to be ordered from smallest to largest. If the thresholds are not sorted, the returned price may be incorrect.
What formula should I use for horizontal thresholds and prices?
If thresholds are in B1:G1, prices are in B2:G2, and the requested quantity is in C10, use =LOOKUP(C10,B1:G1,B2:G2).
What happens when the quantity is below the smallest threshold?
LOOKUP may return an error because there is no threshold that is less than or equal to the requested quantity. Add a minimum threshold or handle the result with an IFERROR formula if needed.
Can I use this formula when the thresholds are vertical?
Yes. Place the thresholds in an ascending column and the corresponding prices in the adjacent column, then use a matching LOOKUP range such as =LOOKUP(C10,A2:A7,B2:B7).
