# Calculate cumulative increments

166 Reputation points
2021-10-18T10:39:33.77+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,693 questions

1. 166 Reputation points
2021-10-19T12: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%

2. 166 Reputation points
2021-10-22T09: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

3. 166 Reputation points
2021-11-06T08:51:49.33+00:00

Hi Herbert!

Great!!
Thanks for sharing.

4. 27,601 Reputation points
2021-10-19T08: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)`