formula help

A

Aoife

Two rows: 1 and 3
Row 1 contains w/c dates
Row 2 contains £ deposits during those weeks; some weeks are blank

How can i get Excel to return the value of the average of the five most
recent deposits (excluding those left blank)?
I don't know which formula to use.

thanks in advance.

Aoife.
 
B

Bryan Hessey

In A4 put
=IF(COUNTIF(B4:$IV4,">0")<5,IF(A3>0,A3,""),"")
and formula-drag that to the right to cover your entries.
This will give you the 5 rightmost entries, which you can total and
divide by 5 to get your average.

--
 
B

Bob Phillips

=AVERAGE(T2:INDEX(2:2,LARGE(IF(2:2<>"",COLUMN(2:2)-MIN(COLUMN(2:2))+1),MIN(5
,COUNT(2:2)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top