software

I need to do a running average of the last five numbers, when some numbers is
a sequence can be changed. For example, for days 1 thru 6, I have numbers in
all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

Try something like this:

For numbers, or blanks, listed in Col_A, beginning in Cell A1

B5:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Copy B5 and copy from B6 down as far as you need.

If there are less than 5 numbers, that formula averages as many as there are.Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Troy Hquot; wrote:

gt; I need to do a running average of the last five numbers, when some numbers is
gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

That works partially, thanks. However, I need to be able to add another
cell, so that I can have 5 numbers to average. say I'm lookingat numbers in
a3 thru a10, but a9 is blank. I need to be able to average A10, A8, A7, A6,
and A5. And if posible, to throw another wrinkle, if inthe last sequence, A7
is gone along with A9, then I would need to get A4. If possible!

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; For numbers, or blanks, listed in Col_A, beginning in Cell A1
gt;
gt; B5:
gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt;
gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; press [Enter].
gt;
gt; Copy B5 and copy from B6 down as far as you need.
gt;
gt; If there are less than 5 numbers, that formula averages as many as there are.
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Troy Hquot; wrote:
gt;
gt; gt; I need to do a running average of the last five numbers, when some numbers is
gt; gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

Using
B5:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)

After that entering formula in B5 and pressing [Ctrl][Shift][Enter]:
Select B5
Editgt;Copy
Select B6:B100
Press [Enter]

The formulas will average up to the last 5 numeric values in A1:A100,
depending on the cell the formula is in.
B5 will look for the last 5 values in A1:A5
B10 will look for the last 5 values in A1:A10
etc
(You did say you wanted a *running* average, right?)

An alternative would be to use only this formula:
B100:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A100),LARGE(($ A$1:A100gt;0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A 100)/MIN(COUNT($A$1:A100),5)

Again: commit that formula with [Ctrl][Shift][Enter]

That will return the average of the last 5 numeric values entered in cells
A1:A100

Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Troy Hquot; wrote:

gt; That works partially, thanks. However, I need to be able to add another
gt; cell, so that I can have 5 numbers to average. say I'm lookingat numbers in
gt; a3 thru a10, but a9 is blank. I need to be able to average A10, A8, A7, A6,
gt; and A5. And if posible, to throw another wrinkle, if inthe last sequence, A7
gt; is gone along with A9, then I would need to get A4. If possible!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; For numbers, or blanks, listed in Col_A, beginning in Cell A1
gt; gt;
gt; gt; B5:
gt; gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt; gt;
gt; gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; press [Enter].
gt; gt;
gt; gt; Copy B5 and copy from B6 down as far as you need.
gt; gt;
gt; gt; If there are less than 5 numbers, that formula averages as many as there are.
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Troy Hquot; wrote:
gt; gt;
gt; gt; gt; I need to do a running average of the last five numbers, when some numbers is
gt; gt; gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; gt; gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

Thank you very much. Er, I'm gonna show my lack of some understanding, but
why are you using [Ctrl][Shift][Enter]: to enter the fomula?? Otherwise,
thank you again, that should help a lot!

quot;Ron Coderrequot; wrote:

gt; Using
gt; B5:
gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt;
gt; After that entering formula in B5 and pressing [Ctrl][Shift][Enter]:
gt; Select B5
gt; Editgt;Copy
gt; Select B6:B100
gt; Press [Enter]
gt;
gt; The formulas will average up to the last 5 numeric values in A1:A100,
gt; depending on the cell the formula is in.
gt; B5 will look for the last 5 values in A1:A5
gt; B10 will look for the last 5 values in A1:A10
gt; etc
gt; (You did say you wanted a *running* average, right?)
gt;
gt; An alternative would be to use only this formula:
gt; B100:
gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A100),LARGE(($ A$1:A100gt;0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A 100)/MIN(COUNT($A$1:A100),5)
gt;
gt; Again: commit that formula with [Ctrl][Shift][Enter]
gt;
gt; That will return the average of the last 5 numeric values entered in cells
gt; A1:A100
gt;
gt; Does either of those help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Troy Hquot; wrote:
gt;
gt; gt; That works partially, thanks. However, I need to be able to add another
gt; gt; cell, so that I can have 5 numbers to average. say I'm lookingat numbers in
gt; gt; a3 thru a10, but a9 is blank. I need to be able to average A10, A8, A7, A6,
gt; gt; and A5. And if posible, to throw another wrinkle, if inthe last sequence, A7
gt; gt; is gone along with A9, then I would need to get A4. If possible!
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; For numbers, or blanks, listed in Col_A, beginning in Cell A1
gt; gt; gt;
gt; gt; gt; B5:
gt; gt; gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt; gt; gt;
gt; gt; gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; gt; press [Enter].
gt; gt; gt;
gt; gt; gt; Copy B5 and copy from B6 down as far as you need.
gt; gt; gt;
gt; gt; gt; If there are less than 5 numbers, that formula averages as many as there are.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Troy Hquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I need to do a running average of the last five numbers, when some numbers is
gt; gt; gt; gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; gt; gt; gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

Your question is the reason I'm not a big fan of [Ctrl][Shift][Enter] and
only use them if I can't think of another way or their alternative is
oppressively complex..

Many Excel functions have an alter ego that can handle arrays of data in
ways that their primary character cannot.

It's easiest to explain by example:
A1:A
A2:(blank)
A3:A
A4:(blank)
A5:A

B1:20
B2:10
B3:20
B4:10
B5:20

C1: =AVERAGE(IF(A1:A5=quot;Aquot;,B1:B5))
That function returns 16.
According to the intent of the formula, that answer is wrong.

Edit cell C1, change nothing, and commit the formula with
[Ctrl][Shift][Enter].
Now, cell C1 returns 20, the correct answer.

My general experience has been this:
If you think Excel should be able to do something, it probably can. The
method just isn't particularly obvious sometimes.

I hope that helps.
If not....search Google for array formulas. There are many approaches to
explaining them.

***********
Regards,
Ron

XL2002, WinXP-Proquot;Troy Hquot; wrote:

gt; Thank you very much. Er, I'm gonna show my lack of some understanding, but
gt; why are you using [Ctrl][Shift][Enter]: to enter the fomula?? Otherwise,
gt; thank you again, that should help a lot!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Using
gt; gt; B5:
gt; gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt; gt;
gt; gt; After that entering formula in B5 and pressing [Ctrl][Shift][Enter]:
gt; gt; Select B5
gt; gt; Editgt;Copy
gt; gt; Select B6:B100
gt; gt; Press [Enter]
gt; gt;
gt; gt; The formulas will average up to the last 5 numeric values in A1:A100,
gt; gt; depending on the cell the formula is in.
gt; gt; B5 will look for the last 5 values in A1:A5
gt; gt; B10 will look for the last 5 values in A1:A10
gt; gt; etc
gt; gt; (You did say you wanted a *running* average, right?)
gt; gt;
gt; gt; An alternative would be to use only this formula:
gt; gt; B100:
gt; gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A100),LARGE(($ A$1:A100gt;0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A 100)/MIN(COUNT($A$1:A100),5)
gt; gt;
gt; gt; Again: commit that formula with [Ctrl][Shift][Enter]
gt; gt;
gt; gt; That will return the average of the last 5 numeric values entered in cells
gt; gt; A1:A100
gt; gt;
gt; gt; Does either of those help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Troy Hquot; wrote:
gt; gt;
gt; gt; gt; That works partially, thanks. However, I need to be able to add another
gt; gt; gt; cell, so that I can have 5 numbers to average. say I'm lookingat numbers in
gt; gt; gt; a3 thru a10, but a9 is blank. I need to be able to average A10, A8, A7, A6,
gt; gt; gt; and A5. And if posible, to throw another wrinkle, if inthe last sequence, A7
gt; gt; gt; is gone along with A9, then I would need to get A4. If possible!
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; For numbers, or blanks, listed in Col_A, beginning in Cell A1
gt; gt; gt; gt;
gt; gt; gt; gt; B5:
gt; gt; gt; gt; =SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$ 1:A5gt;0)*ROW($A$1:A5),{1,2,3,4,5}),0))*$A$1:A5)/MIN(COUNT($A$1:A5),5)
gt; gt; gt; gt;
gt; gt; gt; gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; gt; gt; press [Enter].
gt; gt; gt; gt;
gt; gt; gt; gt; Copy B5 and copy from B6 down as far as you need.
gt; gt; gt; gt;
gt; gt; gt; gt; If there are less than 5 numbers, that formula averages as many as there are.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Troy Hquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need to do a running average of the last five numbers, when some numbers is
gt; gt; gt; gt; gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; gt; gt; gt; gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.

If you want to avoid array formulae and use quot;normalquot; ones instead, this
one also gives 20:

=AVERAGE(SUMIF(A1:A5,quot;=Aquot;,B1:B5)/COUNTIF(A1:A5,quot;=Aquot;))

HansHere's another way to average the last five non-blanks...

=AVERAGE(INDEX(A1:A100,LARGE(IF(A1:A100lt;gt;quot;quot;,ROW(A1 :A100)-ROW(A1) 1),5)):I
NDEX(A1:A100,MATCH(9.99999999999999E 307,A1:A100)) )

or

=AVERAGE(INDEX(A1:A100,LARGE(IF(A1:A100lt;gt;quot;quot;,ROW(A1 :A100)-ROW(A1) 1),MIN(C
OUNT(A1:A100),5))):INDEX(A1:A100,MATCH(9.999999999 99999E 307,A1:A100)))

The first formula will return an average only when there's at least 5
numbers available.

The second will return an average even when there are less than 5
numbers available.

Note that both formulas need to be confirmed with CONTROL SHIFT ENTER.

Hope this helps!

In article gt;,
Troy H lt;Troy gt; wrote:

gt; I need to do a running average of the last five numbers, when some numbers is
gt; a sequence can be changed. For example, for days 1 thru 6, I have numbers in
gt; all days except for the 5th day, I need the average for days, 1,2,3,4,and 6.