Forum Discussion
Update prices based on list value
On the other hand, if all you're saying is you want to be able to select an academic year and a single annual percentage rate to apply to each of the intervening years, then a simple FV (Future Value) function will do it. Here's a reference that explains the use of the function. I use it based on a single annual period, count the number of periods (years) based on the row the year occupies in your data validation table, and use the rate specified in the drop down box.
The formula reads like this:
=FV($F$3,MATCH($F$2,Sheet1!$A$1:$A$6,0),,Table1[@[Int Price (No VAT)]],0)*-1Minus 1 at the end just to turn the number into a positive.
The basic formula could be very easy. However, the implementation of it will depend greatly on how your spreadsheet (workbook) is laid out, and maybe a more precise or comprehensive description of your goals.
Also a question: if you use this year's prices as the base, are you expecting that 5% per year increase to compound? Would you want to round prices to the nearest whole dollar (or some other generalizable figure, such as always ending in "$xxx.99"..... there are lots of ways to implement the general rule you've articulated.
Are you willing to share either the actual workbook (or a mockup that conceals any confidential or proprietary info)? Post it in your reply, dragging and dropping it to the area below the text box. OR post it on OneDrive or GoogleDrive (or equivalent) with a link pasted here that grants access.
