Cumulative Average Formula

[Mchgh9]

[TABLE="width: 390"]
<COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" span=3 width=72><TBODY>[TR]
[TD="class: xl71, width: 173, bgcolor: #75923c"]Activity[/TD]
[TD="class: xl72, width: 70, bgcolor: #75923c"]Unit[/TD]
[TD="class: xl73, width: 60, bgcolor: #75923c"]Agreed Volume[/TD]
[TD="class: xl74, width: 72, bgcolor: #75923c"]Jan-13[/TD]
[TD="class: xl74, width: 72, bgcolor: #75923c"]Feb-13[/TD]
[TD="class: xl76, width: 72, bgcolor: #75923c"]Mar-13[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 173, bgcolor: transparent"]Forecast[/TD]
[TD="class: xl75, bgcolor: transparent"]km[/TD]
[TD="class: xl79, bgcolor: #c2d69a"]35.25[/TD]
[TD="class: xl80, bgcolor: transparent"]2.00[/TD]
[TD="class: xl80, bgcolor: transparent"]10.00[/TD]
[TD="class: xl81, bgcolor: transparent"]5.00[/TD]
[/TR]
[TR]
[TD="class: xl77, width: 173, bgcolor: #f2dddc"]Actual[/TD]
[TD="class: xl78, bgcolor: #f2dddc"][/TD]
[TD="class: xl82, bgcolor: #c2d69a"][/TD]
[TD="class: xl83, bgcolor: #f2dddc"]1.00[/TD]
[TD="class: xl83, bgcolor: #f2dddc"]10.00[/TD]
[TD="class: xl84, bgcolor: #f2dddc"]54.00[/TD]
[/TR]
[TR]
[TD="class: xl85, width: 173, bgcolor: #00b0f0"]Variance (cumulative)[/TD]
[TD="class: xl86, bgcolor: #00b0f0"]%[/TD]
[TD="class: xl87, bgcolor: #00b0f0"] [/TD]
[TD="class: xl88"]-50.00[/TD]
[TD="class: xl88, bgcolor: #00b0f0"]0.00[/TD]
[TD="class: xl88"]-90.74[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 520"]
<TBODY>[TR]
[TD]Activity</SPAN>[/TD]
[TD]Unit</SPAN>[/TD]
[TD]Agreed Volume</SPAN>[/TD]
[TD]Jan-13</SPAN>[/TD]
[TD]Feb-13</SPAN>[/TD]
[TD]Mar-13</SPAN>[/TD]
[/TR]
[TR]
[TD]Forecast</SPAN>[/TD]
[TD]km</SPAN>[/TD]
[TD]35.25</SPAN>[/TD]
[TD]2.00</SPAN>[/TD]
[TD]10.00</SPAN>[/TD]
[TD]5.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Actual</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.00</SPAN>[/TD]
[TD]10.00</SPAN>[/TD]
[TD]54.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Variance (cumulative)</SPAN>[/TD]
[TD]%</SPAN>[/TD]
[TD] [/TD]
[TD]-50.00[/TD]
[TD]0.00</SPAN>[/TD]
[TD]-90.74[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=3></COLGROUP>[/TABLE]

Hi I am looking to calculate the variance beween forecast vs Actual and then as each month progresses, show the latest average. So in Feb the above average would be -25, then in March it would average -50,0, and -90.74.

I work out the variance using the following formula

=IF(D4/D3>=D3,SUM((D3/D4*100)-100),SUM((D4/D3)*100)-100)

I do not know how to calucalte the average in the variance row as it has to reference it's own value and this has me really confused.

Any help much appreciated.

Thanks

Dave

 

[par60056]

First, I am not sure that your variance equation makes sense. Column D the actual was below the forecast so the variance is negative. Column F the actual was almost 11 times forecast. I don't see how this can have a negative variance.

Second, should the cumulative variance be based on forecast amounts? In other words, since the forecast in E is 5 times the forecast in D should it's variance get 5 times the weight?

If the amount forecast effects the variance then the cumulative variance is simply the same formula as the single variance except using SUM(D$3:D3) for D3 and sum(D$4:D4) for D4 and copying across.

If the amount forecast does not effect the cumulative variance then you are just taking an average variance and that is Average(D$5:D5) copied across.