Dynamic % Change Calculation

[SEANK123]

I'm wondering if it is possible to have a dynamic percent change calculation done? I have values from last year and values that will populate for the current year throughout the year.

Is it possible to to have a formula look at how many cells are populated in a range and then make a calculation based only on the populated cells? Meaning it would only calculate YOY % change based on JAN 2019 and 2018 values, then based on JAN-FEB, and so on as the 2019 data is populated.

The current YOY % calculation is being done by the totals which obviously skews it greatly and doesn't truly reflect the change through out the year.

Or should I just setup an additional column that calculates it each month?

<colgroup><col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:6107; width:125pt" width="167" span="2"> <col style="mso-width-source:userset;mso-width-alt:5961; width:122pt" width="163" span="2"> </colgroup><tbody>
[TD="class: xl74, width: 101"]MONTH
[/TD]
[TD="class: xl75, width: 167"]2019 CUSTOMER ERROR %[/TD]
[TD="class: xl75, width: 167"]2018 CUSTOMER ERROR %[/TD]
[TD="class: xl75, width: 163"]2019 CUSTOMER ERROR $[/TD]
[TD="class: xl76, width: 163"]2018 CUSTOMER ERROR $
[/TD]

[TD="class: xl77"]JAN[/TD]
[TD="class: xl63, align: right"]6.45%[/TD]
[TD="class: xl63, align: right"]0.80%[/TD]
[TD="class: xl64, align: right"]$4,790.29[/TD]
[TD="class: xl65, align: right"]$894.64[/TD]

[TD="class: xl77"]FEB[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]6.90%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$5,419.38[/TD]

[TD="class: xl77"]MAR[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]3.39%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$4,570.54[/TD]

[TD="class: xl77"]APR[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]5.66%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$5,327.54
[/TD]

[TD="class: xl77"]MAY[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]6.07%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$6,927.48[/TD]

[TD="class: xl77"]JUN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]2.67%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$3,182.15[/TD]

[TD="class: xl77"]JUL[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]12.47%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$9,069.43[/TD]

[TD="class: xl77"]AUG[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]15.52%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$9,589.48[/TD]

[TD="class: xl77"]SEP[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]9.56%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$7,370.95[/TD]

[TD="class: xl77"]OCT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]14.56%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$16,532.51[/TD]

[TD="class: xl77"]NOV[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]16.27%[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65, align: right"]$10,321.32[/TD]

[TD="class: xl78"]DEC[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71, align: right"]6.09%[/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl73, align: right"]$3,009.39[/TD]

[TD="class: xl79"]TOTAL[/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl66, align: right"]$4,790.29[/TD]
[TD="class: xl81, align: right"]$82,214.81[/TD]

[TD="class: xl79"]YOY % CHANGE[/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl67, colspan: 2"]-94.17%[/TD]

[TD="class: xl74"]MIN[/TD]
[TD="class: xl68, align: right"]6.45%[/TD]
[TD="class: xl68, align: right"]0.80%[/TD]
[TD="class: xl69, align: right"]$4,790.29[/TD]
[TD="class: xl70, align: right"]$894.64[/TD]

[TD="class: xl77"]MAX[/TD]
[TD="class: xl63, align: right"]6.45%[/TD]
[TD="class: xl63, align: right"]16.27%[/TD]
[TD="class: xl64, align: right"]$4,790.29[/TD]
[TD="class: xl65, align: right"]$16,532.51[/TD]

[TD="class: xl78"]AVG[/TD]
[TD="class: xl71, align: right"]6.45%[/TD]
[TD="class: xl71, align: right"]8.33%[/TD]
[TD="class: xl72, align: right"]$4,790.29[/TD]
[TD="class: xl73, align: right"]$6,851.23[/TD]

</tbody>

 

[SEANK123]

This is a bit clunky but seems to be working. If anyone has any more elegant or efficient solutions I'm open to suggestions still.

=IFS(COUNT(D2:D13)=1,(D2-E2)/E2,COUNT(D2:D13)=2,(SUM(D2:D3)-SUM(E2:E3))/SUM(E2:E3),COUNT($D$2:$D$13)=3,(SUM(D2:D4)-SUM(E2:E4))/SUM(E2:E4),COUNT($D$2:$D$13)=4,(SUM(D2:D5)-SUM(E2:E5))/SUM(E2:E5))