Forum Discussion
Help creating a complex formula to deduct a % from decreasing varying values
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.
Please get something done about this!
The current scheme would be
= LET(
cumulativeFactor, SCAN(1, 1-percentDiscount, LAMBDA(a,f, a*f)),
unitPrice, unitStartingPrice * (1+fixedCost) * cumulativeFactor,
productUnits * unitPrice
)Broken into two parts, these would be
= LET(
cumulativeFactor, SCAN(1, 1-percentDiscount, LAMBDA(a,f, a*f)),
unitPrice, unitStartingPrice * cumulativeFactor,
productUnits * unitPrice
)
= LET(
cumulativeFactor, SCAN(1, 1-percentDiscount, LAMBDA(a,f, a*f)),
unitCost, unitStartingPrice * fixedCost * cumulativeFactor,
productUnits * unitCost
)These figures assume that the discount on price is also applied to the fixed additional costs.
Hello!
I really much appreciate your time and support.
I have been trying to understand your solution, as I am not used to LET and LAMBDA functions yet, and I really like it. I had the opportunity to expand my knowledge on that. However, I have tried to add a new product row below each "product 1" row (except for the yearly total revenue, which is intended to sum all products) and I can see the formula does not rearrange to get, for instance, two products. Note: The table-like structure of the sample file I attached cannot be an actual Excel table. This is part of a huge document that cannot change easily.
Based on what I was intending to do before posting my discussion I thought functions like SUMPRODUCT would take into account if new rows (products) are inserted. It would be interesting to see how to rearrange the formula you provided me though.
Any further recommendations? It would be highly appreciated.
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.
Please get something done about this!
Hello PeterBartholomew1 ,
What an insightful reply of yours! Thank you so much for taking your time to reply with such a dedicated message and lesson.
Introducing myself to functions like LET, SCAN and LAMBDA by jumping into one of their major shortcomings makes it even more interesting! For sure I am going to expand my knowledge on these and start using them on a more frequent basis.
Again, I really appreciate your contributions here, which I am fully aware being of huge value. I hope sooner than later we could talk again once there is a straightforward implementation of this functionality.
Martin
Martin
I am pleased to have had the opportunity to promote your interest in LET/LAMBDA and the new methods! I have found that the challenge of 'forgetting' tried and tested legacy methods is almost as great as that of adopting the new.
