Averaging the two most recent entries in a column

  • Thread starter Thread starter Thomas
  • Start date Start date
T

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.
 
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.
 
Assume numbers are added sequentially in B2 down
In say, C2: =AVERAGE(OFFSET(INDIRECT("B"&COUNT(B2:B10)+1),,,-2))
 
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.
 
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.
 
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.
--
Click to expand...

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

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

---
 
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.
 
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?
 
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 C2:D2, 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:D)+1),,,-2))
 
Back
Top