How to Calculate a Dollar Amount from Numeric Ranges in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula that returns a specific dollar amount when a numeric value falls within predefined ranges in an adjacent cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a rate sheet or pricing table and wants automatic dollar amounts based on the numeric value entered in an adjacent cell.
- Observed behavior
- A formula should evaluate the entered value and return the correct dollar amount, such as $1.50 for 50–100, $2.00 for 101–150, and $2.50 for 151–200.
Confirm the numeric input cell (for example C6) contains a number rather than text, and decide whether your range boundaries are inclusive. Keep the input cell reference consistent before copying the formula down.
Use a nested IF formula for a fixed set of ranges
Best when you have only a few dollar tiers and want a single-cell formula without creating an extra rate table.
This formula checks the value in C6 against each range and returns the matching dollar amount. It returns 0 when the value is outside the listed ranges.
Click cell D6, where the dollar amount should appear next to the numeric value in C6.
Type =IF(AND(C6>=50,C6<=100),1.5,IF(AND(C6>=101,C6<=150),2,IF(AND(C6>=151,C6<=200),2.5,0))) and press Enter.
Select D6, go to Home > Number Format > Currency, and choose two decimal places or press Ctrl+Shift+$.
Drag the fill handle from D6 over the adjacent rows where you want the same calculation.
Create a lookup table with approximate match for flexible rates
Better when ranges or dollar amounts may change, because you update the table instead of rewriting a long formula.
Assign Dollar Amounts from Numeric Ranges in WPS Spreadsheets
Use WPS Spreadsheets to create an Excel-compatible nested IF or VLOOKUP formula that returns $1.50, $2.00, or $2.50 based on an adjacent numeric value, then format the result as Currency and save it as .xlsx.
- 1. Download and install WPS Office: Go to the official WPS Office download page, download WPS Office, and open WPS Spreadsheets.
- 2. Open your Excel workbook: Click Menu > Open, select the .xlsx or .xls file, and go to the worksheet with the numeric values.
- 3. Enter the range-based formula: Click the target cell, type =IF(AND(C6>=50,C6<=100),1.5,IF(AND(C6>=101,C6<=150),2,IF(AND(C6>=151,C6<=200),2.5,0))), then press Enter.
- 4. Apply currency formatting: Select the formula cell, go to Home > Number Format > Currency, and choose two decimal places.
- 5. Save the Excel-compatible file: Click Menu > Save As, choose .xlsx, and save the workbook to preserve formulas and formatting.
Frequently Asked Questions
Can I use this formula in both Microsoft Excel and WPS Office?
Yes, the nested IF and VLOOKUP formulas work in both WPS Spreadsheets and Microsoft Excel. Save the file as .xlsx to maintain compatibility.
How do I handle values outside the listed ranges?
The nested IF example returns 0 for values below 50 or above 200. You can replace the final 0 with a custom rate or add more IF conditions for additional ranges.
Why should I use a lookup table instead of multiple IF statements?
A lookup table is easier to update when rates or range boundaries change. Edit the table cells instead of rewriting a long formula, and use VLOOKUP with the approximate match argument set to TRUE.
How do I apply currency formatting in WPS Spreadsheets?
Select the formula cell, go to Home > Number Format > Currency, or press Ctrl+Shift+$ to apply the default currency format, then adjust decimal places as needed.
What if my numeric cell contains text or is blank?
The formula may return 0 or an error. Ensure the input cell contains a number, or wrap the reference in IFERROR or use ISNUMBER if the data may be inconsistent.
