question

jmsaf-4508 avatar image
0 Votes"
jmsaf-4508 asked jmsaf-4508 answered

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

141360-imagen.png


141392-imagen.png


office-vba-devoffice-excel-itpro
imagen.png (6.5 KiB)
imagen.png (5.2 KiB)
5 |1600 characters needed characters left characters exceeded

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

HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered

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

5 |1600 characters needed characters left characters exceeded

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

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft edited

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

142133-capture40.jpg

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.



capture40.jpg (137.1 KiB)
5 |1600 characters needed characters left characters exceeded

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

jmsaf-4508 avatar image
0 Votes"
jmsaf-4508 answered emilyhua-msft commented

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!

141697-imagen.png


141738-imagen.png



imagen.png (13.9 KiB)
imagen.png (24.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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 ·
jmsaf-4508 avatar image
0 Votes"
jmsaf-4508 answered emilyhua-msft commented

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
5 |1600 characters needed characters left characters exceeded

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 ·
jmsaf-4508 avatar image
0 Votes"
jmsaf-4508 answered

Hi Herbert!

Great!!
Thanks for sharing.

5 |1600 characters needed characters left characters exceeded

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