WPS Spreadsheets: Calculate Task Costs Automatically with Excel Lookup Formulas
Question details
The user needs a spreadsheet formula that identifies a task's unit rate and calculates its total cost when a task name and acreage or quantity are entered.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A cost worksheet contains task names, unit rates, and quantities such as acreage. The user wants entering a task such as Plow to automatically retrieve its rate and calculate the total.
- Observed behavior
- The task name, rate, and quantity are not yet connected by a reliable automatic formula; the desired result is a calculated total cost for each entered task.
Create a clear rate table with one column for task names and another for unit rates, using unique task names such as Plow. Set up separate entry columns for the selected task and its quantity or acreage so the lookup formula can reference consistent cells.
Use XLOOKUP to retrieve the task rate and calculate the total cost
Use this modern lookup method when your WPS Spreadsheets version supports XLOOKUP and your task and rate data are arranged in separate columns.
Assume A2 contains the task name, B2 contains the acreage or quantity, and the rate table is formatted as TaskTable with Task and Rate columns. XLOOKUP finds the matching rate, and multiplication applies it to the entered quantity.
Enter task names in one column and their unit rates in the next, select the range, and choose Insert > Table. Name the table TaskTable if needed from the Table tools.
Type a task such as Plow in cell A2 and its acreage or quantity in cell B2.
Select the total-cost cell and enter =XLOOKUP(A2,TaskTable[Task],TaskTable[Rate])*B2, then press Enter.
Drag the fill handle from the formula cell down the total-cost column to calculate costs for additional task rows.
Use VLOOKUP for broader spreadsheet compatibility
Use VLOOKUP when XLOOKUP is unavailable or when the task names and rates are stored in a traditional two-column lookup range.
Build Automatic Task Cost Calculations in WPS Spreadsheets
WPS Spreadsheets lets you organize task rates, look up the correct unit price, and multiply it by acreage or quantity in one formula. Its familiar spreadsheet interface makes it easy to create the table, enter the formula, and extend the calculation to additional rows.
- 1. Open WPS Spreadsheets: Launch WPS Office and choose Spreadsheet to create a new workbook or open an existing cost worksheet.
- 2. Build the task-rate table: Enter task names and unit rates in two columns, select the range, and use Insert > Table to create a structured lookup table.
- 3. Add task and quantity fields: Create columns for the entered task, acreage or quantity, and total cost, then type the task and quantity into the first data row.
- 4. Insert the lookup formula: In the total-cost column, enter =XLOOKUP(A2,TaskTable[Task],TaskTable[Rate])*B2 or use the VLOOKUP alternative when needed.
- 5. Apply the calculation to more rows: Drag the fill handle down the total-cost column and test a few task names and quantities to confirm the rates are correct.
Frequently Asked Questions
How do I calculate the cost when the task name is entered in A2?
Put the quantity or acreage in B2 and use =XLOOKUP(A2,TaskTable[Task],TaskTable[Rate])*B2 in the total-cost cell. Replace the table and column names if your rate table uses different names.
What should I use if WPS Spreadsheets does not support XLOOKUP?
Use VLOOKUP with the task names in the first column of the lookup range. For example, =VLOOKUP(A2,$E$2:$F$20,2,FALSE)*B2 retrieves the rate from column F and multiplies it by B2.
Why does the lookup formula return an error for a task such as Plow?
Check that the task name matches the lookup table exactly, including spaces and spelling. Also confirm that the task column is the XLOOKUP return range or the first column of the VLOOKUP range.
Can the total cost update when I change the acreage?
Yes. Keep the quantity or acreage in a referenced cell such as B2. When B2 changes, the multiplication portion of the formula recalculates the total automatically.
How can I prevent an error when no task has been entered yet?
Wrap the lookup formula in IFERROR, such as =IFERROR(XLOOKUP(A2,TaskTable[Task],TaskTable[Rate])*B2,""). This leaves the total-cost cell blank until a valid task is entered.
