Search code examples
excelexcel-formuladynamiccellcalculation

Excel dynamic YTD vs PYTD, based on actuals of this year

I have full year 2023 sales by months and I have Jan-Feb 2024 actual sales by months by categories I would like to have YTD vs PYTD difference % calculated automatically as I input actuals of 2024

example

For example, next time I enter March 2024 (green range), YTD vs PYTD % difference (yellow cell) should be calculated based on Jan-March 2024 vs Jan-March 2023

I need this to be calculated in one yellow column

I tried

IF(B35>0;SUM($B35:B35)/SUM($B17:B17)-1;0)

but the problem is I need this dynamic in one column, not 12 columns

I would appreciate any help here

Solution

  • Assuming there is no Excel Constraints and while writing this formula if the Beta Channel is enabled then one could use the following formula:

    enter image description here

    =BYROW(IF(B8:M10<>"",B8:M10,0),SUM)/BYROW(IF(B8:M10<>"",B2:M4,0),SUM)-1

    or, Using MMULT()

    =MMULT(IF(B8:M10<>"",B8:M10,0),SEQUENCE(12)^0)/
 MMULT(IF(B8:M10<>"",B2:M4,0),SEQUENCE(12)^0)-1