Dynamic % Change Calculation

SEANK123

New Member
Joined
Jul 21, 2017
Messages
11
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?

MONTH
2019 CUSTOMER ERROR %2018 CUSTOMER ERROR %2019 CUSTOMER ERROR $2018 CUSTOMER ERROR $
JAN6.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%
MIN6.45%0.80%$4,790.29$894.64
MAX6.45%16.27%$4,790.29$16,532.51
AVG6.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>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

SEANK123

New Member
Joined
Jul 21, 2017
Messages
11
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,043
Messages
5,526,426
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top