[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,MAT CH(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)),IND EX(J15:J100,MATCH(99^99,J15:J100)-1))
--

HTH,

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


"Thomas" wrote in message
...
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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Thomas" wrote:
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.

[edvwvw via OfficeKB.com]

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

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

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


"Max" wrote:

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

[Ron Rosenfeld]

On Tue, 17 Jun 2008 09:53:01 -0700, Thomas
wrote:

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.

--
Thanks for your help.


"RagDyeR" wrote:

One way:

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

HTH,

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


"Thomas" wrote in message
...
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]

Am I missing something?

Original posting didn't say anything about blanks in-between.
This point was also implicit in my assumption Iine:
Assume numbers are added sequentially in B2 down

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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))+L OOKUP(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 !
-----------------------------------------------------------------------------------------------


"Thomas" wrote in message
...
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.

--
Thanks for your help.


"RagDyeR" wrote:

One way:

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

HTH,

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


"Thomas" wrote in message
...
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.

[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?
--
Thanks for your help.


"Max" wrote:

Am I missing something?

Original posting didn't say anything about blanks in-between.
This point was also implicit in my assumption Iine:
Assume numbers are added sequentially in B2 down

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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,R OWS($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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Thomas" wrote in message
...
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?
--
Thanks for your help.

[Thomas]

YOU ARE A GENIUS!!!! THANKS
--
Thanks for your help.


"Max" wrote:

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,R OWS($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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Thomas" wrote in message
...
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?
--
Thanks for your help.

[Max]

Welcome, glad that option worked out ok for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Thomas" wrote in message
...
YOU ARE A GENIUS!!!! THANKS