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.
Hi, mathetes ,
Apologies for the delay, it's been a busy week.
Ok, so in answer to your question I want to be able to have one document only that shows current prices and can do a forecast of price increase for each academic year. yes, the increase is compound. No, I don't want to round prices.
I would also like to have the option of changing the % increase, if needed, so I would be able to select for example prices for 2027/2028 with 5%, 10% or 15% increase.
Mock up attached.
Thanks in advance!
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.