extraordinary average formula !!

[gaftalik]

Hello everybody!

1)Is there any formula to put in K6 which may calculates the average of the last 3 months ! should it be a macro or a formula ?
I need also a macro to calculate it automatically each time i add a month data column!

2) what is the right formula to average only the months of the year 2004 ?

Thank you very much .

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3
4
5	nov.03	dec.03	jan.04	feb.04	mar.04	apr.04	mai.04	jun.04	Averagein2004	Averagelast3months
6	30	21	38	40	11	26	24	14	#VALUE!	??
7	8	6	37	23	26	37	27	10
8	1	15	38	31	32	27	31	19
9	6	34	10	12	29	5	2	14
10	18	9	7	4	28	8	19	24
11
12
13
Sheet1

 

[Juan Pablo González]

For your first question:

http://www.mrexcel.com/board2/viewtopic.php?t=62282&highlight=average+last+values

For your second question:

Are the dates in B5:I5 real dates ? I mean, if you put this in an empty cell

=ISNUMBER(B5)

what is the result ?

 

[gaftalik]

Yeah the dates are true dates ,Jan 04, feb 04 etc...

 

[Aladin Akyurek]

Book4
	A	B	C	D	E	F	G	H	I	J
4									AvgOf	AvgOfLast
5	Nov-03	Dec-03	Jan-04	Feb-04	Mar-04	Apr-04	May-04	Jun-04	2004	3
6	30	21	38	40	11	26	24	14	25.5	21.33333333
7	8	6	37	23	26	37	27	10	26.66666667	24.66666667
8	1	15	38	31	32	27	31	19	29.66666667	25.66666667
9	6	34	10	12	29	5	2	14	12	7
10	18	9	7	4	28	8	19	24	15	17
Sheet1

Formulas...

I6:

=AVERAGE(IF(YEAR($A$5:$H$5)=$I$5,A6:H6))

which must be confirmed with control+shift+enter instead of just with enter.

J6:

=AVERAGE(H6:INDEX(A6:H6,MATCH(9.99999999999999E+307,A6:H6)-($J$5-1)))

 

[GorD]

I think this works for both instances, or at least until the end of 2004. It relies on always keeping one blank column of data. So as you add a month, insert a new column to keep the blank.

You can seem to see the formula in K3 which was

=AVERAGE(OFFSET(J3,0,-4,1,3)).

However, probably better sticking with Aladdins anyway.

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	nov.03	dec.03	jan.04	feb.04	mar.04	apr.04	mai.04	jun.04		Averagein2004	Averagelast3months
3	6	30	21	38	40	11	26	24		26.66666667	20.3333333
4	7	8	6	37	23	26	37	27		10
5	8	1	15	38	31	32	27	31		19
6	9	6	34	10	12	29	5	2		14
7	10	18	9	7	4	28	8	19		24
Sheet1

 

[gaftalik]

Dear Mr.Juan, Thank you very much for your guidance , Dear Mr.Aladin! my good Genie !you always knock me down from the first 5 seconds
your formulas worked excelently, milions of thanks.
Mr.Gord i couldnt find your formulas ?!

 

[gaftalik]

Dear Gord,

I am very sorry for omitting something in your formula, i found that it is workable too !

One more time thanks all of you for your time

 

[gaftalik]

Hello Again,
I encountered a problem which i thought i could solve myself but that was impossible

What is the formula in Z8 to return the average of the "total"columns of the last 3 months , even if each time i add a new data column .

The answer in this case is 41

Thank you a lot

question abt average.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
5																averagelast			3
6	mar.04			apr.04			mai.04			iun.04			iul.04			aug.04
7	mar.04	mar.04	mar.04	apr.04	apr.04	apr.04	mai.04	mai.04	mai.04	iun.04	iun.04	iun.04	iul.04	iul.04	iul.04	aug.04	aug.04	aug.04
8	T	U	total	T	U	total	T	U	total	T	U	total	T	U	total	T	U	total	??
9	9	8	17	19	28	47	45	14	59	14	3	17
10	48	10	58	37	37	74	29	9	38	23	19	42
11	21	50	71	12	42	54	16	36	52	11	49	60
12	32	39	71	44	30	74	12	38	50	49	18	67
13	47	19	66	15	43	58	28	23	51	2	2	4
14
2nd

 

[gaftalik]

Sorry i made a mistake the formula should be in T8 not Z8 !

I appreciate anyone's help , thank you.

 

[Aladin Akyurek]

Sorry i made a mistake the formula should be in T8 not Z8 !

I appreciate anyone's help , thank you.

I'm putting them all in T. You can drag and drop them in Z...

aaAverages gaftalik.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4																averagelast			3
5																			4
6	mar.04			apr.04			mai.04			iun.04			iul.04			aug.04			12
7	mar.04	mar.04	mar.04	apr.04	apr.04	apr.04	mai.04	mai.04	mai.04	iun.04	iun.04	iun.04	iul.04	iul.04	iul.04	aug.04	aug.04	aug.04
8	T	U	total	T	U	total	T	U	total	T	U	total	T	U	total	T	U	total
9	9	8		19	28		45	14		14	3								41
10	48	10		37	37		29	9		23	19								51.33333
11	21	50		12	42		16	36		11	49								55.33333
12	32	39		44	30		12	38		49	18								63.66667
13	47	19		15	43		28	23		2	2								37.66667
14
Sheet2

T4 houses the last N param, which is here 3.

Formulas...

T5:

=(T6-3*T4)+1

Computes the pos of the start cell of the first block of 3.

T6:

=MATCH(9.99999999999999E+307,B9:S9)

Computes the pos of the last numeric value.

T9, which is copied down...

=AVERAGE(IF((MOD(COLUMN(INDEX($B9:$S9,$T$5):INDEX($B9:$S9,$T$6))-CELL("Col",INDEX($B9:$S9,$T$5))+1,3)=0),INDEX($B9:$S9,$T$5):INDEX($B9:$S9,$T$6)))

which must be confirmed with control+shift+enter instead of just enter.