SOLVED

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

Brass Contributor

Hi there,

 

I am running Excel 365. I am attaching a sample document for better understanding of the problem, it is quite complex.

 

I have such a scheme as follows: For 6 years, I have got a product with a fixed initial price that is subject to discounts on each year, with a set number of units of that product and, therefore, a set amount of revenue per year.

Paralelly, I also have a fixed % of a cost (in my example, 1%) that is paid by the client inherent the product's final price.

 

My current Excel file computes the total yearly revenue taking into account the initial price for the client, the discount (if there exists) and the 1% additional cost paid by the client (as well its proper discount - which also has- computed with the same rates as the discount for the original price).

 

What I want to achieve is to separate the calculation of the annual revenue from the actual starting price & discount on one side, and then compute the annual revenue from the 1% charged to the client on the other side.

 

At the end, both totals should sum up my current total yearly revenue.

 

Important remarks:

- None of the other formulas or structure may change.

- In my sample file, there is only 1 row for 1 product, but this changes constantly. There may be 3 products for a moment, for which the rows for their respective prices, discount %, units and totals would appear (with created macros) beneath each "product 1" row. Therefore, it should be considered when creating the formula.

 

I hope I have explained my problem clearly enough. Any help would be much appreciated!

 

Martin

5 Replies

@MAngosto 

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.

 

@Peter Bartholomew 

 

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.

best response confirmed by MAngosto (Brass Contributor)
Solution

@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!

Hello @Peter Bartholomew ,

 

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. :smile:

1 best response

Accepted Solutions
best response confirmed by MAngosto (Brass Contributor)
Solution

@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!

View solution in original post