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

 

[GorD]

I'm pretty sure that that I have managed to get this to work for up to a years worth of data. Having some trouble posting it as isze is too big.
Here it is anyway:


formula in AE1 which can be copied across is

=IF(AND(AE3="total", COLUMN(AE:AE)>=(MATCH(9.99999999999999E+307,$A4:$AZ4)-(5+(($AU$9-2)*4)))),"y","")

Hope it is good for you

 

[GorD]

Book1.xls
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1					y				y				y
2				jul				aug				sep			blank
3	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%						Average
4	16	8%	9	8	31	8%	9	8	42	8%	9	8	55	8%						42.667
5	58	27%	48	10	58	27%	48	10	58	27%	48	10	58	27%						58
6	71	33%	21	50	71	33%	21	50	71	33%	21	50	71	33%						71
7	71	33%	32	39	71	33%	32	39	71	33%	32	39	71	33%						71
8
9								Noofmonthstoaverage					3
Sheet1

 

[Aladin Akyurek]

...I need to return the average of the "total" columns of the last xx months , but in this case i have one more column after "total"...

The aerlier formula was depending on the layout of your data with blocks of 3 columns. Now one more column after "total" makes the layout of your data "irregular". Can you manage to keep empty the column before the column where your new layout starts?

 

[gaftalik]

Hi champ,

Do you mean the % column should be empty ?

 

[GorD]

Gaftalik,

Have you tried the soln I offered. If it dosen't work could you let me know whats wrong with it.

 

[gaftalik]

Hi GorD,

I am trying to work it on .. will definitely answer you , many thanks ..

 

[gaftalik]

Well , GorD !

I got zero values as result of your first step formula , where is the error ?

 

[gaftalik]

Sorry here is the formulas i used as you gave me

question abt average.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	October				November				December				January
3	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%
4	9	8	17	8%	19	28	47	19%	14	3	17	7%	9	8	17	8%						0
5	48	10	58	27%	37	37	74	30%	23	19	42	17%	48	10	58	27%						0
6	21	50	71	33%	12	42	54	22%	11	49	60	24%	21	50	71	33%						0
7	32	39	71	33%	44	30	74	30%	49	18	67	27%	32	39	71	33%						0
8			217				249				186				217	100%
2nd

 

[GorD]

formula in AE1 which can be copied across is

=IF(AND(AE3="total", COLUMN(AE:AE)>=(MATCH(9.99999999999999E+307,$A4:$AZ4)-(5+(($AU$9-2)*4)))),"y","")

Hope it is good for you[/quote]

Gaftalik,

I can't see from your post what you have in row 1 . You need the formula above in all cells in row 1. This references another cell in mu case AU9 to set how many weeks average you want.

If the PM your email address I'll e-mail the workbook I created, if it helps

Regds Gordon D

 

[gaftalik]

Gordon,

I tried to take it step by step but it gave me 0 as result , although i used average if formula in other worksheet , this one is not working

question abt average.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1											y				y
2	October				November				December				January
3	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%
4	9	8	17	8%	19	28	47	19%	14	3	17	7%	9	8	17	8%						0
5	48	10	58	27%	37	37	74	30%	23	19	42	17%	48	10	58	27%						0
6	21	50	71	33%	12	42	54	22%	11	49	60	24%	21	50	71	33%						0
7	32	39	71	33%	44	30	74	30%	49	18	67	27%	32	39	71	33%						0
8			217				249				186				217	100%
2nd