Forum Discussion
Update prices based on list value
Hello,
I would like to create a dynamic document that increases prices based on the year, the year being selected from a drop down list.
I want to use this year as the starting point prices and apply an increase of 5% going forward so that when I change the year on the drop down list the prices go up by 5% per cent based on the previous year.
All I have so far is a table with prices for various item A4:D40 and the drop down list with the years in G2.
I have been looking around for solutions but I can't find and answer.
Thanks.
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.
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!
- In return, I now am away from home and involved in meetings until the middle of next week, so won't be able to give any kind of reply beyond this one saying it'll be delayed. Sorry, but please be patient. And if somebody else wants to chime in, please be my guest.
