Excel 365 Pro Plus with Power Pivot and Power Query.
With PP stacked Queries.
No formulas, no VBA macro.
https://www.mediafire.com/file/51lgzdeo8eu0hmu/10_31_21.xlsx/file
https://www.mediafire.com/file/rz2ql0p7z55s8ao/10_31_21.pdf/file
Calculate cumulative increments
In the Cost_Act field, I need to update the amount of the Cost field to the value that it would have in the most recent year (in the example 2021) applying the increase percentages indicated for each concept and year, but from the year following the one indicated in the year field. The concepts not included in the table would remain with the same value of the starting year.
attached 2 example tables. Thanks in advance

Herbert Seidenberg 1,191 Reputation points
20211101T01:15:39.667+00:00
4 additional answers
Sort by: Most helpful

jm saf 166 Reputation points
20211019T12:08:32.397+00:00 Hi!
First of all, thank you for your interest in helping me.
I'm sorry I didn't explain myself correctly.
In the solution you propose, you update for each "Cod" the value of the "Cost" field by the percentage corresponding to the year indicated in the "year" field. The result I am looking for is to update the "cost" field but to the amount that would result from updating the amount to the value that it would have in the last year indicated (from the year indicated in the Year field), in my example 2021. As an example I will indicate you which should be the result of the "Cost_Act" field. I will indicate the update of the value per year, but the function I need should perform the calculation in just one.
Answering your question, the updated value is calculated as you indicate since the values in the table are%
Thanks for your time! 
jm saf 166 Reputation points
20211022T09:01:34.663+00:00 I really appreciate your proposed solution to my question, and for the time you have dedicated to it.
In your answer, as I understand it, i have to make a specific formula for each starting year.
I wonder if it could be adapted so that it can be used regardless of the start year.
Best regards 
jm saf 166 Reputation points
20211106T08:51:49.33+00:00 Hi Herbert!
Great!!
Thanks for sharing. 
Emily HuaMSFT 27,601 Reputation points
20211019T08:54:39.137+00:00 Update
Do you have Microsoft 365 apps?
If yes, you could us IFS function to judgment the year in formuals below.You could still use Sumproduct function (Do you have Microsoft 365 apps?
If yes, you could us IFS function to judgment the year in formuals below.You could still use Sumproduct function "SUMPRODUCT(($B2=$K$1:$P$1)*(D2+X=$J$2:$J$7),($K$2:$P$7))" to get the increase percentages of sepcific year and each concept.
"D2+X" represents the year you want to find the increase percentages of sepcific year and each concept.If the Year in Column D starts from 2017, under this case, for the values of 2021 for each concept:
=IFS(D2=2017,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+3=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+4=$J$2:$J$7),($K$2:$P$7))%+1),D2=2018,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+3=$J$2:$J$7),($K$2:$P$7))%+1),D2=2019,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1),D2=2021,C2)
If the Year in Column D starts from 2017, under this case, for the values of 2020 for each concept:
=IFS(D2=2017,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+3=$J$2:$J$7),($K$2:$P$7))%+1),D2=2018,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1),D2=2019,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1),D2=2020,C2)
If the Year in Column D starts from 2017, under this case, for the values of 2019 for each concept:
=IFS(D2=2017,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1)*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+2=$J$2:$J$7),($K$2:$P$7))%+1),D2=2018,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1),D2=2019,C2)
If the Year in Column D starts from 2017, under this case, for the values of 2018 for each concept:
=IFS(D2=2017,C2*(SUMPRODUCT(($B2=$K$1:$P$1)*(D2+1=$J$2:$J$7),($K$2:$P$7))%+1),D2=2018,C2)
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in [our documentation][2] to enable email notifications if you want to receive the related email notification for this thread.