Calculate cumulative increments

Question

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

Answer 1

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

Answer 2

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!

Answer 3

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

Answer 4

Hi Herbert!

Great!!
Thanks for sharing.

Answer 5

@jm saf

-------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 e-mail notifications if you want to receive the related email notification for this thread.

---