How to Use Excel Formulas in WPS Spreadsheets to Show Current Purchases and Previous Prices
Question details
The user needs a dynamic WPS Spreadsheets formula that filters stock codes purchased during a selected date period, then shows the latest purchase date, quantity, current price, most recent previous price, and the percentage difference between current and previous price.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a purchase report from a table containing stock codes, purchase dates, quantities, and prices, and wants to compare current purchasing activity with previous prices for each stock code.
- Observed behavior
- The intended report should dynamically list each eligible stock code and calculate the current purchase details, previous price, and percentage change without manual filtering.
Arrange the purchase table with clear columns for stock code, purchase date, quantity, and price, and ensure the date column contains real date values rather than text. Confirm that your WPS Spreadsheets version supports dynamic array functions such as FILTER, SORT, LET, and REDUCE before using the recommended formula.
Build a Dynamic Report with LET, FILTER, SORT, and REDUCE
Use this method when you want WPS Spreadsheets to automatically refresh the entire stock code report whenever the selected start or end date changes.
This approach mirrors Excel 365 dynamic array patterns. It filters the purchase table by the selected period, identifies each unique stock code, finds the latest purchase row, then searches earlier purchases for the most recent previous price.
Enter the selected start date in cell G1 and the selected end date in cell G2, then apply a Date format from the Home tab.
Click an empty cell and enter =FILTER(A2:D1000,(B2:B1000>=G1)*(B2:B1000<=G2),"No purchases") to isolate the rows purchased within the selected period.
In cell E1, enter a LET/REDUCE formula that uses UNIQUE on the stock-code column, SORT to order purchases by date descending, FILTER to find earlier purchases before each latest purchase, and VSTACK/HSTACK to output the report.
In the Percentage Difference column, use =(CurrentPrice-PreviousPrice)/PreviousPrice and format the result as a percentage from the Home tab.
Use Helper Columns for Older WPS Versions Without Dynamic Arrays
Choose this manual method if FILTER, LET, or REDUCE are not available in your WPS Spreadsheets version.
Create a Current Purchase vs Previous Price Report in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible dynamic formulas such as FILTER, SORT, LET, and REDUCE, so you can build a self-updating purchase report without switching to Microsoft Excel and without expensive add-ins.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open the workbook that contains the purchase data, and click the worksheet with the stock code, date, quantity, and price table.
- 2. Enter the date filter cells: Type the selected start date and end date into two empty cells such as G1 and G2, and format them as dates using the Home tab.
- 3. Insert the FILTER formula: Click an empty cell, type =FILTER(A2:D1000,(B2:B1000>=G1)*(B2:B1000<=G2),"No purchases"), and press Enter to isolate the selected period rows.
- 4. Build the dynamic report formula: Enter the LET/REDUCE formula in a report cell to calculate each stock code's latest purchase date, quantity, current price, previous price, and percentage difference.
- 5. Format and save as Excel: Set the Percentage Difference column to Percentage format, then click Menu > Save As and choose .xlsx to keep Excel compatibility.
Frequently Asked Questions
Does WPS Office support Excel dynamic array functions like FILTER and LET?
Yes, recent WPS Spreadsheets versions support many Excel dynamic array functions, including FILTER, SORT, UNIQUE, LET, and REDUCE. If a function is not recognized, update WPS Office or use the helper-column method instead.
What should I do if the formula returns #SPILL! or #CALC!?
Make sure the output area below and to the right of the formula cell is empty so the dynamic array can spill. If no previous price exists for a stock code, wrap the previous-price lookup in IFERROR or an empty-string check to avoid #CALC! errors.
Why is my date filter returning no purchases?
Check that the purchase date column is formatted as real dates and that the start and end date cells are also dates. If dates are stored as text, use Data > Text to Columns or apply a date format to fix them.
Can I save this WPS Spreadsheets report as a Microsoft Excel file?
Yes. Click Menu > Save As, choose the .xlsx format, and the workbook will keep Excel-compatible formulas for anyone opening it in Microsoft Excel.
How do I calculate the percentage difference between current price and previous price?
Use =(CurrentPrice-PreviousPrice)/PreviousPrice. Then select the result cells and click Percent Style on the Home tab to display the difference as a percentage.
