WPS Spreadsheet: Return the Top 10% of Sold Items to Another Sheet
Question details
The user needs to return the highest-volume 10% of inventory items, along with their descriptions, quantities, bin locations, and item codes, to a separate printable worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The source inventory records are stored on one worksheet, and the user wants a separate sheet that automatically displays items meeting the top-10-percent sales threshold.
- Observed behavior
- The desired result is a separate worksheet containing complete source rows for items whose quantity sold is at or above the 90th percentile.
Confirm which column contains Quantity Sold and identify the complete source range, including descriptions, bin locations, and item codes. Create or select a blank worksheet for the filtered results.
Use a 90th-percentile FILTER formula to return complete item rows
Use this solution in WPS Spreadsheet when dynamic array formulas are available and the output should refresh as the source data changes.
A 90th-percentile threshold identifies the quantity level at or above which the top 10 percent of sales volumes fall. If several items share the threshold value, the result may contain more than exactly 10 percent of the rows because all tied items are included.
On the destination worksheet, enter a label such as 90th Percentile in cell G1. In G2, enter =PERCENTILE.INC(RawData!C2:C100,90%) and press Enter. Replace RawData!C2:C100 with the actual Quantity Sold range.
In the top-left cell of the destination output area, enter =FILTER(RawData!A2:E100,RawData!C2:C100>=PERCENTILE.INC(RawData!C2:C100,90%)) and press Enter. Adjust A2:E100 to include the descriptions, quantities, bin locations, and item codes you want to display.
Select the returned results, use the Home tab to apply borders or number formats, and use the Page Layout tab to set the print area and page orientation.
Update the source records in the RawData sheet. The FILTER result recalculates automatically when the referenced source range changes.
Filter a helper column when FILTER is unavailable
Use this fallback when your WPS Spreadsheet version does not support dynamic array formulas such as FILTER.
Build a dynamic top-10-percent inventory report in WPS Spreadsheet
WPS Spreadsheet can calculate the 90th-percentile sales threshold, return complete matching rows with FILTER, and format the results on a separate printable worksheet. The familiar spreadsheet interface makes it easy to adjust ranges and maintain the report as inventory data changes.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the inventory workbook containing the RawData sheet.
- 2. Create the destination sheet: Click the plus button beside the sheet tabs to add a blank worksheet for the top-selling items.
- 3. Enter the percentile formula: In the destination sheet, enter =FILTER(RawData!A2:E100,RawData!C2:C100>=PERCENTILE.INC(RawData!C2:C100,90%)) in the first output cell, then adjust the ranges to match your data.
- 4. Set the print layout: Open the Page Layout tab, set the print area around the returned results, and choose the desired orientation and scaling.
Frequently Asked Questions
Why does the result sometimes contain more than exactly 10% of the items?
The formula includes every item whose quantity sold is greater than or equal to the 90th-percentile threshold. If multiple items are tied at that threshold, all tied rows are returned.
Which range should replace RawData!A2:E100?
Use the full contiguous range containing the fields you want to return, such as item description, quantity sold, bin location, and item code. Include the same row span used by the Quantity Sold range.
Can the report update when new inventory rows are added?
Yes, if the formula references an expanded range or an Excel-compatible table. For a fixed range such as A2:E100, extend the references when new rows fall outside row 100.
What should I do if WPS does not recognize FILTER?
Use the helper-column method: calculate the 90th-percentile threshold, mark rows where Quantity Sold is greater than or equal to that value, then use Data > AutoFilter to show TRUE rows and copy them to the report sheet.
