# question

## 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  imagen.png (6.5 KiB)
imagen.png (5.2 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@jmsaf-4508

-------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)`

Note: Please follow the steps in [our documentation] to enable e-mail notifications if you want to receive the related email notification for this thread.

capture40.jpg (137.1 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!  imagen.png (13.9 KiB)
imagen.png (24.3 KiB)
· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@jmsaf-4508
Thanks for your detailed informtion.
I would modify and update my previous reply.

0 Votes 0 ·

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

· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jmsaf-4508,

Sorry for the delay.
For your needs, I think it's better to use an automated program, such as a macro, to achieve incremental years from the start year to the target year and find these years' corresponding increase percentages.
To better help you, I would add a tag ”office-vba-dev“.

0 Votes 0 ·