Separate Excel Revenue into Base Price and Percentage-Based Cost
Question details
Separate revenue calculated with cumulative percentage discounts into a base price amount and a percentage-based cost amount.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs formulas that calculate discounted base revenue and discounted fixed-cost revenue separately, including calculations across multiple product rows.
- Observed behavior
- A standard SCAN calculation works for one series, while a two-dimensional product range can produce an array-of-arrays error unless the input range is expanded.
Confirm that percentDiscount contains decimal discount rates, such as 10% or 0.1, and that unitStartingPrice, fixedCost, and productUnits refer to matching periods or rows. The formulas below assume the same discount also applies to the percentage-based fixed cost.
Use separate SCAN formulas for base revenue and percentage-based cost
Use this method when one product or one compatible array of periods needs separate base price and fixed-cost revenue results.
The cumulative factor multiplies each period's remaining percentage after discount. The first formula returns base price revenue, and the second returns the fixed-cost portion calculated as a percentage of the starting price.
Enter this formula in the result cell: =LET(cumulativeFactor,SCAN(1,1-percentDiscount,LAMBDA(a,f,a*f)),unitPrice,unitStartingPrice*cumulativeFactor,productUnits*unitPrice)
In a separate result cell, enter: =LET(cumulativeFactor,SCAN(1,1-percentDiscount,LAMBDA(a,f,a*f)),unitCost,unitStartingPrice*fixedCost*cumulativeFactor,productUnits*unitCost)
Add the two result ranges, or use =BaseRevenueCell+CostRevenueCell, to confirm that the total matches the original calculation using unitStartingPrice*(1+fixedCost).

Use EXPAND and SCAN for cumulative discounts across multiple products
Use this workaround when percentDiscount is a two-dimensional range for multiple products and SCAN returns an array-of-arrays error.
Calculate Discounted Base Revenue and Cost in WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet environment for entering named-range formulas and calculating base revenue and percentage-based cost in separate columns. Use formula cells to keep the cumulative discount logic visible and compare each component with the combined revenue total.
- 1. Open the revenue workbook: Open the Excel workbook in WPS Spreadsheets and locate the ranges for starting price, discount percentage, fixed cost, and units.
- 2. Add a base revenue result column: Select an empty result cell, type the base revenue formula using your worksheet range names or cell references, and press Enter.
- 3. Add a fixed-cost result column: Select the adjacent result cell and enter the percentage-based cost formula so it can be reviewed independently from base revenue.
- 4. Compare the results with total revenue: In a check cell, add the base revenue and cost results, then compare that value with the existing combined revenue calculation.

Frequently Asked Questions
What does the SCAN function do in a cumulative discount formula?
SCAN processes each discount value in sequence and carries the prior result forward. In this case, SCAN starts at 1 and repeatedly multiplies by 1-percentDiscount to create a cumulative remaining-price factor.
Why are base revenue and fixed cost calculated in separate formulas?
Separate formulas let you report the discounted product price and the discounted percentage-based cost independently. Adding the two results produces the total when both components receive the same discount.
How do I prevent an array-of-arrays error when calculating multiple products?
Use EXPAND to append a separator column to percentDiscount, run SCAN with IFERROR(a*f,1) so the calculation resets after each row, and use DROP to remove the separator column from the final array.
Should the fixed cost be multiplied by the cumulative discount factor?
Multiply fixedCost by the cumulative factor only when the discount applies to that cost as well as the base price. If the cost is not discounted, calculate it from unitStartingPrice*fixedCost*productUnits without the cumulative factor.




