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>
</tbody>
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?
MONTH | 2019 CUSTOMER ERROR % | 2018 CUSTOMER ERROR % | 2019 CUSTOMER ERROR $ | 2018 CUSTOMER ERROR $ |
JAN | 6.45% | 0.80% | $4,790.29 | $894.64 |
FEB | 6.90% | $5,419.38 | ||
MAR | 3.39% | $4,570.54 | ||
APR | 5.66% | $5,327.54 | ||
MAY | 6.07% | $6,927.48 | ||
JUN | 2.67% | $3,182.15 | ||
JUL | 12.47% | $9,069.43 | ||
AUG | 15.52% | $9,589.48 | ||
SEP | 9.56% | $7,370.95 | ||
OCT | 14.56% | $16,532.51 | ||
NOV | 16.27% | $10,321.32 | ||
DEC | 6.09% | $3,009.39 | ||
TOTAL | $4,790.29 | $82,214.81 | ||
YOY % CHANGE | -94.17% | |||
MIN | 6.45% | 0.80% | $4,790.29 | $894.64 |
MAX | 6.45% | 16.27% | $4,790.29 | $16,532.51 |
AVG | 6.45% | 8.33% | $4,790.29 | $6,851.23 |
<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>
</tbody>