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

 

[Aladin Akyurek]

...Do you mean the % column should be empty ?

No. What follows treats the data as if it starts in column G. Keep G empty although it won't do any harm if G houses anything unrelated, except error values.

aaAverages gaftalik ext.xls
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
20																MonthAverageOf::		2
21																		5
22																		12
23		October				November				December				January
24		U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%
25		9	8	17	0.078341	19	28	47	0.188755	14	3	17	0.091398					32
26		48	10	58	0.267281	37	37	74	0.297189	23	19	42	0.225806					58
27		21	50	71	0.327189	12	42	54	0.216867	11	49	60	0.322581					57
28		32	39	71	0.327189	44	30	74	0.297189	49	18	67	0.360215					70.5
29				217				249				186
Sheet3

Formulas...

X20 houses 2, a last N parameter value.

X21:

=(X22-4*X20)+1

X22:

=MATCH(9.99999999999999E+307,G25:W25)-1

X25:

=AVERAGE(IF((MOD(COLUMN(INDEX($G25:$W25,$X$21):INDEX($G25:$W25,$X$22))-CELL("Col",INDEX($G25:$W25,$X$21))+1,4)=0),INDEX($G25:$W25,$X$21):INDEX($G25:$W25,$X$22)))

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

 

[gaftalik]

Champ,i am very grateful to you, so smoothly done!

Dear Gordon i am getting close to work out your solution but please check that when i put 2 months it shows 3 "y" where is my error?

question abt average.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
42															AverageLast		2	Months
43							y				y				y
44	October				November				December				January
45	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%
46	9	8	17	8%	19	28	47	22%	14	3	17	8%	9	8	17	8%
47	48	10	58	27%	37	37	74	34%	23	19	42	19%	48	10	58	27%
48	21	50	71	33%	12	42	54	25%	11	49	60	28%	21	50	71	33%
49	32	39	71	33%	44	30	74	34%	49	18	67	31%	32	39	71	33%
50			217				249				186				217
Aladdin

 

[GorD]

I suspect it is because the match part of the formula is looking out to far

)>=(MATCH(9,99999999999999E+307;G46:Z46)


the range should be set to one cell short of where your averages are housed. I suspect that range includes your average?

I take it you have now got the average part working?

Aladins solution is probably much better, but I wanted to see if I could come up with my own solution.

 

[gaftalik]

I am very sorry Gordon your formula worked also perfect after i took some more time to apply it , here it is :

question abt average.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
42											AverageLast			2	Months
43											y				y
44	October				November				December				January
45	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%
46	9	8	17	8%	19	28	47	22%	14	3	17	8%	9	8	17	8%
47	48	10	58	27%	37	37	74	34%	23	19	42	19%	48	10	58	27%
48	21	50	71	33%	12	42	54	25%	11	49	60	28%	21	50	71	33%
49	32	39	71	33%	44	30	74	34%	49	18	67	31%	32	39	71	33%
50			217				249				186				217
Sheet

Thanks very much Gordon .

 

[GorD]

Don't appologise - if it works i'm happy