Adding columns

[jjcsjc]

I have a column of 2 digit numbers. I want to average the last three cells. sometimes there are blank cell in the column.
A1 42
A2 43
A3 44
A4
A5 41
A6 46

=Average($A1:$A3)-36
=Average($A3:$A6)-36

 

[PaddyD]

see the method outlined here:

http://www.mrexcel.com/board/viewtopic.php?topic=12570&forum=2

If you change the formula entered in a1 to:

=IF(A4,average(N(INDIRECT(ADDRESS(LARGE(ISNUMBER(INDIRECT(A5))*ROW(INDIRECT(A5)),
ROW(INDIRECT("1:"&A4))),COLUMN(INDIRECT(A5)))))),"")

(which still needs to be array entered) you can find the average of the last n non-blank cells. (See
http://www.mrexcel.com/tip011.shtml for info on array formulas).

An example follows:

Book6
	A	B	C	D
1	Result:	34.6666667	44.5	34.4
2	Lasthow-many?	3	4	5
3	Hidethis	13	13	13
4	Hidethis	3	4	5
5	Hidethis	$B$7:$B$13	$C$7:$C$13	$D$7:$D$13
6
7	Data:	13	48	35
8		11	44	41
9		25		45
10		32	49	11
11		44
12			46
13		28	39	40
14
15
Sheet1

Paddy

 

[jjcsjc]

After alot of work on my part (Iam not an Excel expert)figuring our the other formulas required, I finaly got it to work. I thank you very much, I could not have done it without your help.