Calculating Actual Progress vs Planned Progress base on Today's Month & Year

Vivien Chong

With reference to the following screenshot, I am trying to capture the Actual Progress & Planned Progress on row 1 of the respective column base on Today's Month and Year.

For example, today is January 2024, row 1 for Planned Progress 2 % should show 0%, and row 1 for Actual Progress 2 % should show 0%.

If today is June 2024, row 1 for Planned Progress 2 % should show 30%, and row 1 for Actual Progress 2 % should show 50%.

If today is August 2024, row 1 for Planned Progress 2 % should show 40%, and row 1 for Actual Progress 2 % should show 50%.

I am trying to index match the children rows with Today's Month and Year for Actual Progress 2 %:

=INDEX(children([Actual Progress 2 %], MATCH(AND(Month(@cell) = MONTH(Today()), Month:Month, Year(@cell) = Year(Today()))), 0))

I get an error message: #UNPARSEABLE

I believe the formula to be used in Planned Progress 2 % and Actual Progress 2 % should be similar.

Any assistance would be very much appreciated.

Paul Newcome

Try an INDEX/COLLECT:

=INDEX(COLLECT(CHILDREN(), CHILDREN(Month@row), @cell = MONTH(TODAY()), CHILDREN(Year@row), @cell = YEAR(TODAY())), 1)

Should be the same exact formula for both columns.

Paul Newcome

Try an INDEX/COLLECT:

=INDEX(COLLECT(CHILDREN(), CHILDREN(Month@row), @cell = MONTH(TODAY()), CHILDREN(Year@row), @cell = YEAR(TODAY())), 1)

Should be the same exact formula for both columns.

Vivien Chong

Thanks @Paul Newcome ! That works for both columns.

Paul Newcome

Happy to help. 👍️