That's great! Thanks so much. One thing I forgot to mention is that some
cells may be blank in the column. I.e. Someone may not submit a score for
weeks 3 and 4 so when they submit a score on week 5, the average should be
calculated using week 2 and week 5, ignoring the blanks or zeros in weeks 3
and 4.
--
Thanks for your help.


"edvwvw via OfficeKB.com" wrote:

That's a very interesting formula - it can be further enhanced by adding a
colon rather than a comma between the two INDEX expressions so that you can
look at a range instead of 2 values.

=AVERAGE(INDEX(A:A,MATCH(99^99,A:A)) : INDEX(A:A,MATCH(99^99,A:A)-4))

(the space is for illustration only)

will average the last 5 entries

Very useful - thanks RagDyeR

edvwvw


RagDyeR wrote:
One way:

=AVERAGE(INDEX(A:A,MATCH(99^99,A:A)),INDEX(A:A,MA TCH(99^99,A:A)-1))

Adjust your ranges as needed.

Say you're using J15 to J100:

=AVERAGE(INDEX(J15:J100,MATCH(99^99,J15:J100)),IN DEX(J15:J100,MATCH(99^99,J15:J100)-1))
I have a column of numbers of which a new number is added each week. I'm
trying to calculate the average of the two most recently entered numbers.
I.e. Week 1 equals 4, week 2 equals 6, returning an average of 5. Then when
week 3 is inputed, lets say 10, the new average is calculated by using week
2
and 3, returning an aveage of 8. The next entry would average week 3 and 4.

Thanks for your help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1