Treat a row as an array inside an array formulation
Hello there,
Running Excel 365. Attaching a sample document for better understanding.
I am trying to compute the final price of a product on each year of its project. The final price is based on an initial price and percentage discounts that are cumulative year over year. For this purpose, I use SCAN function and returns the desired output beautifully.
Now, I need a second consideration for the computation of the price. There are some price components that expire before the end of the whole project. That is, I need to substract some amount from the price at a given date, since that concept is no longer computing to the final price and it has already been amortized. For this other purpose, I use some references that allows the final user only to enter the value that is going to be substracted and during how many years is this particular value going to be amortized. Then, the formulation “drops” that amount once the years of amortization are completed.
These two functionalities (% discount and dropping price component) work when I formulate them apart. However, I need them to be together. The issue is that one of the ranges in the formulation for the latter functionality is not treated as an array and, therefore, it does not dynamically “move” within the range and everything breaks. How can I rearrange the formulation and make this range be a functional array (with minimum alteration of the concept behind this formualtion)?
Thank you so much for any kind support.
Martin
Hello there, Running Excel 365. Attaching a sample document for better understanding. I am trying to compute the final price of a product on each year of its project. The final price is based on an initial price and percentage discounts that are cumulative year over year. For this purpose, I use SCAN function and returns the desired output beautifully. Now, I need a second consideration for the computation of the price. There are some price components that expire before the end of the whole project. That is, I need to substract some amount from the price at a given date, since that concept is no longer computing to the final price and it has already been amortized. For this other purpose, I use some references that allows the final user only to enter the value that is going to be substracted and during how many years is this particular value going to be amortized. Then, the formulation “drops” that amount once the years of amortization are completed. These two functionalities (% discount and dropping price component) work when I formulate them apart. However, I need them to be together. The issue is that one of the ranges in the formulation for the latter functionality is not treated as an array and, therefore, it does not dynamically “move” within the range and everything breaks. How can I rearrange the formulation and make this range be a functional array (with minimum alteration of the concept behind this formualtion)? Thank you so much for any kind support. Martin Read More