Averaging the two most recent entries in a column

[Thomas]

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.

 

[RagDyeR]

One way:

=AVERAGE(INDEX(A:A,MATCH(99^99,A:A)),INDEX(A:A,MATCH(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)),INDEX(J15:J100,MATCH(99^99,J15:J100)-1))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


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.

 

[Max]

Assume numbers are added sequentially in B2 down
In say, C2: =AVERAGE(OFFSET(INDIRECT("B"&COUNT(B2:B10)+1),,,-2))

 

[Thomas]

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.

 

[Dave]

Hi Max,
Jumping in, I am also interested in how to get a rolling average when the
data contains blanks. I tried your formula, but with the following data (for
example), I got a #DIV/0! error.

B2 5
B3 7
B4 3
B5 5
B6 (Blank)
B7 (Blank)
B8 6
B9 Blank)
B10 8

Am I missing something?

Regards - Dave.

 

[Ron Rosenfeld]

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.
--

Probably simpler ways, but this works -- entered as an **array** formula with
<ctrl><shift><enter> (Excel will place braces {...} around the formula if you
entered it correctly):

=(INDEX(A:A,LARGE(ISNUMBER(rng)*ROW(rng),2))+
INDEX(A:A,MAX(ISNUMBER(rng)*ROW(rng))))/2

Note that for versions of Excel prior to 2007, the named range "rng" cannot
refer to an entire column.
--ron

 

[Thomas]

Awesome!!! Thanks!!!! One thing I forgot to mention, can the formula ignore
blank cells as certain weeks won't inlcude points for certain people. 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.

 

[Max]

Am I missing something?

Original posting didn't say anything about blanks in-between.
This point was also implicit in my assumption Iine:
If there are possible blanks in-between data entered down in col B, then in
C2:
=AVERAGE(OFFSET(INDIRECT("B"&MATCH(99^99,B:B)),,,-2))

---

 

[Max]

Ah, pl dismiss that 2nd suggestion. It doesn't work on closer review (forgot
about the -2 height param bit, ugh).
One idea imo, would be to use simple helper cols to "float up" that col of
numbers according to whatever criteria/possibilities to look out for in it
(it's much simpler to just focus attending to these criteria, then use a
standard "float up" non -array), and then just deploy the original
expression suggested on that float-up col to get the required average.

---

 

[RagDyeR]

Have you tried Ron's formula?

It does exactly what you're asking.

Working off his formula, this formula is slightly shorter:

=(INDEX(A:A,LARGE(ISNUMBER(A1:A50)*ROW(1:50),2))+LOOKUP(99^99,A:A))/2
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

The array portions of the formula cannot reference total columns unless
you're using XL07!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Awesome!!! Thanks!!!! One thing I forgot to mention, can the formula ignore
blank cells as certain weeks won't inlcude points for certain people. 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.

 

[Thomas]

Hello Max,
Sorry about missing that major detail of blank cells. I believe the
floating up of the cells would work but exactly how would that be done?

 

[Max]

Assume data in B2 down, with possibilities of intervening blank cells or
cells containing zeros that's to be excluded (this is the assumed criteria)

In C2: =IF(OR(B2={"",0}),"",ROW())
Leave C1 blank

In D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROWS($1:1))))
Select C22, copy down to cover the max expected extent of data in col B,
eg down to D200?

Col D will dynamically "float up" what's in col B based on the criteria

Then you could use in say, E2:
=AVERAGE(OFFSET(INDIRECT("D"&COUNT(D)+1),,,-2))

 

[Thomas]

YOU ARE A GENIUS!!!! THANKS

 

[Max]

Welcome, glad that option worked out ok for you