WPS Spreadsheet: Calculate Average Order Value for Each Product
Question details
The user needs to calculate the average total value of orders associated with each product, including all other items included in those orders.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An order table contains order IDs, products, quantities, and prices. The user wants a product-level table showing the average complete order value for every order that contains each product.
- Observed behavior
- The desired result is a dynamic product-and-average table where each product is linked to its related order IDs, the full value of those orders is calculated, and the result is divided by the number of unique orders.
Format the source range as a table and name it TableOrder. Confirm that the table includes ORDER ID, PRODUCT, QNT, and PRICE columns, and that QNT and PRICE contain numeric values.
Use a dynamic-array formula to calculate average order value by product
Use this solution when you want one formula that automatically generates a product list and calculates the average complete order value for each product.
The formula first finds every order containing a product, then filters all line items from those orders. It sums quantity multiplied by price for each complete order set and divides the result by the number of unique related orders.
This approach requires a WPS Spreadsheet version that supports LET, LAMBDA, FILTER, UNIQUE, REDUCE, HSTACK, VSTACK, XMATCH, and CHOOSECOLS.
Click an empty cell, such as F2, where the product-and-average table should begin.
Paste this formula into the selected cell: =LET(StackAvg,LAMBDA(seed,product,LET(orders,FILTER(TableOrder[ORDER ID],TableOrder[PRODUCT]=product),also,IFNA(XMATCH(TableOrder[ORDER ID],orders),0),scope,FILTER(TableOrder[[QNT]:[PRICE]],also),VSTACK(seed,ROUND(SUMPRODUCT(CHOOSECOLS(scope,1),CHOOSECOLS(scope,2))/COUNT(UNIQUE(orders)),2)))),Products,SORT(UNIQUE(TableOrder[PRODUCT])),Averages,REDUCE("AVERAGE",Products,StackAvg),HSTACK(VSTACK("PRODUCT",Products),Averages))
Press Enter and allow the formula to spill into the cells below and to the right. The first column displays each product and the second column displays its average complete order value.
Compare one product with its source orders: include every line item from each order containing that product, calculate each order total as QNT multiplied by PRICE, and verify the average shown in the result table.
Calculate Product-Based Average Order Values in WPS Spreadsheet
WPS Spreadsheet can create a dynamic product summary from an order table using Excel-compatible LET, FILTER, UNIQUE, REDUCE, and array functions. The result includes the full value of every order containing each product, rather than only the product’s individual line value.
- 1. Create an order table: Open WPS Spreadsheet, select the order data, choose Insert > Table, and name the table TableOrder.
- 2. Verify the required columns: Ensure the table contains ORDER ID, PRODUCT, QNT, and PRICE columns with consistent headers and numeric quantity and price values.
- 3. Insert the calculation: Click an empty output cell and paste the LET and REDUCE formula that filters related orders and calculates the average complete order value.
- 4. Review and format the output: Press Enter, review the spilled product summary, and format the average-value column as currency or number as needed.
Frequently Asked Questions
Does this calculation include the other products in the same order?
Yes. After identifying orders that contain a product, the formula filters all rows with those order IDs. Quantity multiplied by price is then summed across every item in those orders.
Why does the formula divide by unique order IDs?
A product may appear on multiple rows within the same order. COUNT(UNIQUE(orders)) ensures that the average is based on distinct orders rather than counting repeated product rows.
What table name does the formula require?
The formula uses the structured table name TableOrder. If your table has another name, replace every TableOrder reference with the actual table name.
What should I do if WPS Spreadsheet does not recognize REDUCE or HSTACK?
Update WPS Office to the latest version and check whether the required dynamic-array functions are supported. For older versions, create helper columns for order totals and use a PivotTable or separate summary formulas instead.
