Function Problems

Separate Excel Revenue into Base Price and Percentage-Based Cost

Phi Hung VoPhi Hung Vo Nov 12, 2026 869 views

Question details

Separate revenue calculated with cumulative percentage discounts into a base price amount and a percentage-based cost amount.

Separate Excel Revenue into Base Price and Percentage-Based Cost
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.
Before you start

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.

Solution 1Recommended

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.

1
Calculate the discounted base revenue

Enter this formula in the result cell: =LET(cumulativeFactor,SCAN(1,1-percentDiscount,LAMBDA(a,f,a*f)),unitPrice,unitStartingPrice*cumulativeFactor,productUnits*unitPrice)

2
Calculate the discounted percentage-based cost

In a separate result cell, enter: =LET(cumulativeFactor,SCAN(1,1-percentDiscount,LAMBDA(a,f,a*f)),unitCost,unitStartingPrice*fixedCost*cumulativeFactor,productUnits*unitCost)

3
Verify the combined total

Add the two result ranges, or use =BaseRevenueCell+CostRevenueCell, to confirm that the total matches the original calculation using unitStartingPrice*(1+fixedCost).

Use separate SCAN formulas for base revenue and percentage-based cost
Discount assumption: These formulas discount both the base price and the fixed cost. If fixedCost should not receive discounts, remove *cumulativeFactor from the unitCost calculation.
WPS Spreadsheets formulas

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. 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. 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. 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. 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.
Calculate base revenue and percentage-based cost in separate worksheet cells.Use formula-driven calculations for cumulative discount scenarios.Open and edit Microsoft Excel .xlsx workbooks with compatible spreadsheet formatting.Work in a lightweight, familiar spreadsheet interface for revenue analysis.
microsoft office alternative - wps office

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.