w0kkie
New Member
- Joined
- Mar 26, 2020
- Messages
- 7
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
- MacOS
- Mobile
Hello! I have an interesting situation I've been poking at for a few days now to no avail, so it would be great if anyone else has tackled a similar problem before and knows of an elegant solution.
Here's the situation:
I have a table, let's say 10row x 8col. The rows represent a list of products, and cols represent the sale year.
Each product(row) has a starting price (price it starts at). The list of starting prices is one cell over, in the same rows.
There are two cells I need to be able to set manually, which are: Reduction amount (percentage), and reduction length (number of years the reduction should continue for).
I've gotten halfway there by using a numerical index and comparing it to the reduction duration value, which gets the the behavior I want as far as applying to price reductions for the correct number of years, but it only works if I start all the prices on year 1. I'm stuck on how to get some products to start on different years, leaving preceding years blank, and still have the price reduction logic behave correctly.
One last caveat - I can't use macros, because some users of this sheet are not permitted to "enable macros" on a workbook while having certain other files open, which is common.
For visual reference, here's an example of how it would look if it were working properly:
And here's how it looks now, as far as I've been able to get:
You can see the cell references and the formula I'm using in the screenshot, but for convenience here it is in text as well. Cell F5 contains:
I appreciate any ideas!
Here's the situation:
I have a table, let's say 10row x 8col. The rows represent a list of products, and cols represent the sale year.
Each product(row) has a starting price (price it starts at). The list of starting prices is one cell over, in the same rows.
There are two cells I need to be able to set manually, which are: Reduction amount (percentage), and reduction length (number of years the reduction should continue for).
I've gotten halfway there by using a numerical index and comparing it to the reduction duration value, which gets the the behavior I want as far as applying to price reductions for the correct number of years, but it only works if I start all the prices on year 1. I'm stuck on how to get some products to start on different years, leaving preceding years blank, and still have the price reduction logic behave correctly.
One last caveat - I can't use macros, because some users of this sheet are not permitted to "enable macros" on a workbook while having certain other files open, which is common.
For visual reference, here's an example of how it would look if it were working properly:
And here's how it looks now, as far as I've been able to get:
You can see the cell references and the formula I'm using in the screenshot, but for convenience here it is in text as well. Cell F5 contains:
Code:
=IF($C$2-E$3>0,E5*(1-$C$3),E5)
I appreciate any ideas!