How can I ignore blank cell when using the OFFSET function when working out a 3 month Average

[BOB12345]

Hi

So I would like to work out a 3 month rolling average of data using an OFFSET function.

The only issue is how can I amend my formula so that if I miss a month's data the cell is ignored an the previous month's data is used?

The formula that I am using is =AVERAGE(OFFSET(B3,0,COUNTA(C3:N3),1,-3)).

Many Thanks

BOB

 

[arthurbr]

A possible solution http://www.xl-central.com/average-last-3-nonzero-values.html

 

[DanteAmor]

Another way

{=SUM(IF(COLUMN(C3:N3)>=LARGE(IF(C3:N3>0,COLUMN(C3:N3)),3),C3:N3))/3}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Aladin Akyurek]

Moving average of 3 numbers...

Horizontal work out:

In B5 control+shift+enter, not just enter, and copy across:

=AVERAGE(IF(COLUMN(B$3:$N$3) <= SMALL(IF(ISNUMBER(1/B$3:$N$3),COLUMN(B$3:$N$3)),MIN(3,SUM(IF(ISNUMBER(1/B$3:$N$3),1)))),IF(ISNUMBER(1/B$3:$N$3),B$3:$N$3)))

Vertical work out:

In B5 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(COLUMN(INDEX($B$3:$N$3,ROWS($1:1)):$N$3) <= SMALL(IF(ISNUMBER(1/INDEX($B$3:$N$3,ROWS($1:1)):$N$3),COLUMN(INDEX($B$3:$N$3,ROWS($1:1)):$N$3)),MIN(3,SUM(IF(ISNUMBER(1/INDEX($B$3:$N$3,ROWS($1:1)):$N$3),1)))),IF(ISNUMBER(1/INDEX($B$3:$N$3,ROWS($1:1)):$N$3),INDEX($B$3:$N$3,ROWS($1:1)):$N$3)))