Urgent help with formula: incremental phasing

[targus92]

Hi everyone,

Please need urgent help for the attached file.

I am trying to have phasing that is linked to a control table. The control table is in Row 14 and I am trying to have row 9 linked to it. THe objective is to allocate the incremental percentage that is built in the year. So in 2018 it should be 0. In 2020 it should be 10% and we will assume that the phasing is split between the previous 2 years.

Can someone help me develop a formula in D9-09 that automates everytime i change the inputs in rows 14.

Thank you

[ImranBhatti]

Would you please add some results manually in the workbook?

[targus92]

I have already completed row 9 with manual inputs. I need to automate it to link when row 14 changes

[AliGW]

How do the control values relate to the values in row 9? The logic you are using is not clear because you have not explained it.

[targus92]

Apologies, let me clarify

Basically in row 14 is the % that is completed of a project. So in 2018 we have 0% completed. in 2020 we have 10% completed. So we will assume in 2018 and 2019 in each year, 5 % was completed. The logic continues. in 2022 we have 20% completed. But the incremental 10% is split again between 2020 and 2021 (5% each year) in F9 and G9.

Does this help?

[targus92]

Can anyone help me with this please?

[Glenn Kennedy]

In D14, formatted as a %, and copied across:

=SUM(($C$9:INDEX($C$9:$AJ$9,MATCH(D$13,$D$2:$AK$2,0))))

the difference in the horizontal range in the iNDEX-MATCH is intentional.

[JohnTopley]

in D9

=IFERROR((INDEX($E$14:$J$14,,INT((COLUMNS($D$13:D13)-1)/2)+1)-INDEX($D$14:$J$14,,INT((COLUMNS($D$13:D13)-1)/2)+1))/(INDEX($E$13:$J$13,,INT((COLUMNS($D$13:D13)-1)/2)+1)-INDEX($D$13:$J$13,,INT((COLUMNS($D$13:D13)-1)/2)+1)),"")