averaging

[ndello]

I have 12 cells (say a1 to a12) all with values in them. I want to get the average of the cells but only from jan to sep as that is where we are now. Each month the range changes to where we are.
If I use =AVERAGE and range it through the 12 cells, it will give me an average on all twelve, even though oct to dec has zero.
I cannot remove the zero as there is a cell reference in it.
Any ideas
Thanks
Neil
ps. hope this makes sense.

 

[paliman]

Take a look at

http://www.mrexcel.com/tip011.shtml

 

[IML]

maybe something along the lines of
=AVERAGE(OFFSET(A1,0,0,MONTH(NOW())))

or
=AVERAGE(OFFSET(A1,0,0,b2))
where b2 hold the number of months you want

 

[Aladin Akyurek]

On 2002-10-18 13:43, ndello wrote:
I have 12 cells (say a1 to a12) all with values in them. I want to get the average of the cells but only from jan to sep as that is where we are now. Each month the range changes to where we are.
If I use =AVERAGE and range it through the 12 cells, it will give me an average on all twelve, even though oct to dec has zero.
I cannot remove the zero as there is a cell reference in it.
Any ideas
Thanks
Neil
ps. hope this makes sense.

See...

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													Oct
2	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	5	7	5	4	7	9	8	9	8	7	0	0	6.9
4
Sheet1

The essential formula is:

=AVERAGE(INDEX(A3:L3,1,1):INDEX(A3:L3,1,MATCH(M1,A2:L2,0)))

where A3:L3 is monthly value range and A2:L2 is the range housing the month names. M1 holds the name of the current month.