Fix WPS Spreadsheet IF and OR Formulas Returning TRUE Instead of Numbers
Question details
The user needs to correct sales-tax formulas that return TRUE or FALSE instead of a numeric tax amount.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Sales-tax calculations depend on a location and a sales amount, with different rates for the first 500 and the amount above 500.
- Observed behavior
- Formulas work for some locations but return logical values when the sales amount is entered in another column because OR is used inside arithmetic expressions.
Identify the cells containing the location and sales amount, then create a small rate table with one row per location and separate columns for the first-500 rate and the above-500 rate. Confirm that each location name in the source data exactly matches a location in the rate table.
Use a location lookup with a tiered numeric tax formula
Use this maintainable approach when each location has tax rates for the first 500 and for the amount above 500.
OR evaluates conditions and returns the logical value TRUE or FALSE; it does not select one of several numeric tax rates. Store rates in a table instead, retrieve them by location, and use MIN and MAX to calculate the two sales tiers.
For example, if A2 contains the location, B2 contains the sales amount, and H2:J10 contains Location, First500Rate, and Above500Rate columns, use the following formula and adjust the references to match your sheet.
In WPS Spreadsheet, enter the location names in H2:H10, the first-500 rates in I2:I10, and the above-500 rates in J2:J10. Add headers such as Location, First500Rate, and Above500Rate.
Select the tax result cell and enter =MIN(B2,500)*XLOOKUP(A2,$H$2:$H$10,$I$2:$I$10)+MAX(B2-500,0)*XLOOKUP(A2,$H$2:$H$10,$J$2:$J$10), then press Enter.
Use the fill handle on the result cell to copy the formula to other rows. The relative A2 and B2 references update for each record while the rate-table references remain fixed.
Change the location or sales amount in a test row and confirm that the result is numeric. If a location is not found, check spelling and spacing against the rate table.
Use VLOOKUP when XLOOKUP is unavailable
Choose this option in an older WPS Spreadsheet workbook or when the location rate table is arranged with the lookup column first.
Calculate location-based tax amounts in WPS Spreadsheet
WPS Spreadsheet can replace logical OR expressions with XLOOKUP or VLOOKUP formulas and calculate each sales tier as a numeric tax amount. Use a structured rate table to make locations and rates easier to update.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the tax formula.
- 2. Build the rate table: Add a location table with separate columns for the first-500 rate and the above-500 rate.
- 3. Replace the formula: Select the tax result cell and enter an XLOOKUP- or VLOOKUP-based formula that uses MIN and MAX for the two sales tiers.
- 4. Fill and verify: Copy the formula down with the fill handle, then test several locations and sales amounts.
Frequently Asked Questions
Why does OR return TRUE instead of a tax amount in WPS Spreadsheet?
OR only evaluates whether one or more conditions are true and returns TRUE or FALSE. It does not return a numeric rate, so multiplying OR directly in a tax calculation produces a logical result rather than selecting a tax amount.
How can I calculate tax for the first 500 and the amount above 500?
Use MIN for the first tier and MAX for the excess: MIN(sales,500)*first-tier-rate+MAX(sales-500,0)*above-500-rate. Retrieve each rate with XLOOKUP or VLOOKUP based on the location.
What should I do when XLOOKUP cannot find a location?
Check that the location in the source row matches the rate table exactly, including spaces and spelling. You can also use IFERROR around XLOOKUP or VLOOKUP to display a message such as "Location not found".
Can I use VLOOKUP instead of XLOOKUP in WPS Spreadsheet?
Yes. Put the location in the first column of the rate table and use VLOOKUP with FALSE for an exact match, such as VLOOKUP(A2,$H$2:$J$10,2,FALSE) for the first-tier rate.
