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

 

[gaftalik]

That's magic i cant believe it
No words can express my thanks for your everything you do,with you excel problems dont exist !

Dear Aladin ,vessels of thanks ...

 

[gaftalik]

Dear Champ,

Your last formula is one of the best i ever had and saved a lot of problems.
Trying to apply it on another workbook i couldnt succeed bymyself

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"

Can you help me please ?
Always grateful champ ! Thank you a lot

question abt average.xls
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
20															Averagelast		2	Months
21																	??
22																	12
23	October				November				December				January
24	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%			Answershouldbe
25	9	8	17	8%	19	28	47	19%	14	3	17	7%							32
26	48	10	58	27%	37	37	74	30%	23	19	42	17%							58
27	21	50	71	33%	12	42	54	22%	11	49	60	24%							57
28	32	39	71	33%	44	30	74	30%	49	18	67	27%							70,5
29			217				249				186
2nd

 

[gaftalik]

The champ is maybe busy , any one can help please ?

 

[GorD]

Sorry I'm not the champ, but in the meantime perhaps you might be able to make do with this. I'm afraid as it stands its not fully automated.

It relies on the cells you want included in the average to be identified with a y. I am sure it must be quite easy to automate the "y" but I can't think how to do it at the moment.
Any way if you can live with that the rest should work.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1							y				y
2	October				November				December				January
3	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%						Average
4	9	8	17	8%	19	28	47	19%	14	3	17	7%						32
5	48	10	58	27%	37	37	74	30%	23	19	42	17%						58
6	21	50	71	33%	12	42	54	22%	11	49	60	24%						57
7	32	39	71	33%	44	30	74	30%	49	18	67	27%						70.5
Sheet1

Also, can i ask what do you do when you add a new months data? Do you insert 4 columns?

Actually On thinking about this after initial post, if you identify the cells to average with the Y then the part of the array formula looking for "total" is excess to requirements and I haven't really answered your query - sorry

 

[gaftalik]

Dear GorD,

Thank you for your answer, that is very clear but since i need to change the number of months from time to time it means i have to add the "y" over each column needed.
Re columns , i already have the columns from Oct until Dec 04 on my worksheet each having same column header inside .
Once again i appreciate your concern and many thanks .

 

[GorD]

I had a further play with trying to automate the addition of the Y to cells in the count range and the following should work (I think). I set it to count the last two months but the subtraction at the end of the formula could be adjusted to count a different number and could also ref another cell to allow you more flexibility.

Hope it is of some help

Book1
	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			blank
3	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%	U	T	TOTAL	%						Average
4	9	8	17	8%	19	28	47	19%	14	3	17	7%	9	8	17	8%						17
5	48	10	58	27%	37	37	74	30%	23	19	42	17%	48	10	58	27%						50
6	21	50	71	33%	12	42	54	22%	11	49	60	24%	21	50	71	33%						65.5
7	32	39	71	33%	44	30	74	30%	49	18	67	27%	32	39	71	33%						69
Sheet1

Formula in G1 is

=IF(AND(G3="total", COLUMN(G:G)>=(MATCH(9.99999999999999E+307,$A4:$T4)-6)),"y","")

 

[gaftalik]

It could be a solution if i keep the remaining columns blank, i think you can figure what Aladdin did in the previous formula better than me and maybe we will succeed to apply it on this worksheet. Frankly,i got dizzy looking for hours trying to decode that formula piece by piece

GorD, thank you for being so patient with me

 

[GorD]

if you want to add a new month select columns q,r,s,t, and right click insert. The formulas should adjust- just always keep one group of 4 as blanks at the end.

Does that not work?

 

[gaftalik]

It can work but what about if i want the average to be 3 , 4 or 5 upon my request , how to set that simply by editing the needed number in a cell ?

 

[GorD]

I haven't tested this but the thinking was that in the formula

=IF(AND(G3="total", COLUMN(G:G)>=(MATCH(9.99999999999999E+307,$A4:$T4)-6)),"y","")

Insted of having a hard wired value (red above) you could change this to ref another cell which would be related to how many values you want in the average. You /we would have to work out that relationship The example I have used used 2 values. If you wanted more values included inn the range the 6 being subtracted would be a lesser number.

If I get some time later I'll try and see if I can get it to work, but in principal I think it should work.