Thread: Average last 6
View Single Post
  #3   Report Post  
Kwanjangnim
 
Posts: n/a
Default

thanks for the code, it worked well when i tested it in a blank worksheet,
however for some reason it doesn't work when placed in my worksheet, i keep
getting a 'divide by zero error' but all i changed from your formula was the
col range and the start row and end row no.s
=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the
empty cells that are awaiting results from other cells have been formulated
to display blank but i keep getting 'DIV/0' can you sort this for me?


"Bob Phillips" wrote:

=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in message
...
hi all
i've had a look through all the other threads but haven't bee able to find
the solution i'm looking for, basically i have a column which has a

numeric
value entered (in each row) according to results in other cells. see

example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7), this
column will be updated as new results are added, therefore the range that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will need

to
be ignored. can anyone help?