Help creating a complex formula to deduct a % from decreasing varying values

MAngosto 

Sadly, on what may be your first venture into LET/LAMBDA you have run into the major shortcoming in specifying the functionality of modern Excel.  The SCAN function accumulates the price discounts for a single product elegantly and efficiently.  Not unreasonably, you then wish to perform the calculation for a range of products.  Doing this in a straightforward manner using BYROW will take you straight into a #CALC! error, because the solution to your problem is an array of arrays, which Excel treats as an error.

More time has probably been spent (wasted) by expert users on developing workarounds for this defect than has been spent on straightforward exploitation of this otherwise superb functionality.

In this case, I have used the fact that SCAN will run through a 2D array in row-major order as if the entire array were converted to a single row.  Using EXPAND inserts an extra column to the right of the array, which is designed to throw an error.  That #VALUE! error is detected and used as a signal to reinitialise the calculation of discount rates for the following row.  I firmly believe that you should not be expected to make sense of such workarounds.  It is Microsoft's duty to sort the problem and not expose users to such issues.

= LET(
    expandedPercent,  EXPAND(percentDiscount, , 1+COLUMNS(percentDiscount)),
    cumulativeFactor, SCAN(1, 1 - expandedPercent, LAMBDA(a,f, IFERROR(a*f, 1))),
    (1 + fixedCost) * unitStartingPrices * DROP(cumulativeFactor,,-1)
  )

I can only apologise for the complexity of the solution.

JoeMcDaid 

Please get something done about this!