How do I get an average for 5 when I need to skip cells?

[Troy H]

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.

 

[Ron Coderre]

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:A5>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-Pro

 

[Troy H]

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!

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:A5>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-Pro

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.

 

[Ron Coderre]

Using
B5:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$1:A5>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
Edit>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:A100>0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A100)/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-Pro

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!

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:A5>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-Pro

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.

 

[Troy H]

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!

Using
B5:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$1:A5>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
Edit>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:A100>0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A100)/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-Pro

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!

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:A5>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-Pro


:

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.

 

[Ron Coderre]

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="A",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-Pro

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!

Using
B5:
=SUMPRODUCT(ISNUMBER(MATCH(ROW($A$1:A5),LARGE(($A$1:A5>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
Edit>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:A100>0)*ROW($A$1:A100),{1,2,3,4,5}),0))*$A$1:A100)/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-Pro

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!

:

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:A5>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-Pro


:

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.

 

[flummi]

If you want to avoid array formulae and use "normal" ones instead, this
one also gives 20:

=AVERAGE(SUMIF(A1:A5,"=A",B1:B5)/COUNTIF(A1:A5,"=A"))

Hans

 

[Domenic]

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

=AVERAGE(INDEX(A1:A100,LARGE(IF(A1:A100<>"",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:A100<>"",ROW(A1:A100)-ROW(A1)+1),MIN(C
OUNT(A1:A100),5))):INDEX(A1:A100,MATCH(9.99999999999999E+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!