3 month rolling - dynamic

**Armitage2k** · 07-04-2014, 11:28 PM

Hi,

I have one row with data from july last year to july this year with some sales data. this data is generated via some complex formulas which in case of error display "0", but now because of this seem to interfere with my 3 months rolling formula.

Basically what I need is a formula that calculates an average of the last (rightmost) 3 cells and ignores the cells with "0".

Data starts from C54 to N54.

Any help is much appreciated,
thanks!
A2k

**Punnam** · 07-04-2014, 11:51 PM

Hi,

Sample workbook.

Punnam

**Tony Valko** · 07-05-2014, 09:26 AM

Try this...

Data Range

	A	B	C	D	E	F	G	H	I	J
1	Average	------	------	------	------	------	------	------	------	------
2	18	75	62	0	4	0	28	0	22	0

This array formula** entered in A2:

=AVERAGE(IF(B2:J2>0,IF(COLUMN(B2:J2)>=LARGE(IF(B2:J2>0,COLUMN(B2:J2)),3),B2:J2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes there will always be at least 3 non zero entries.