Help with Running Average/Sum of Last 8 Data Points (weeks)

[Tracy813]

I need help with a formula that will average the last 8 cells with non-blank values in the range e3:e20. (There will be blanks and there may be less that 8 cells with values, in which case, I need it to average what's available.) I also need do sum the entries in g3:g20 with the same possibilities.

I've tried tweaking everything I found on the site so far, and no luck!

Any ideas?[/b]

 

[Milo_Minderbinder]

Welcome to the board. Is the idea that you will but a formula in row 21 and it will work backwards from 20 to 3 and will take the average of the first (working backwards) 8 cells with numbers greater than zero?

You may need a UDF. I made one to sum backwards and someone else provide another option

see this tread for ideas

http://www.mrexcel.com/board2/viewtopic.php?t=200014&highlight=

 

[Milo_Minderbinder]

I modified that UDF to work for you

Essentially, you could put this formula anywhere in your file

=AVGBACK(E3:E20,8)

The UDF code is placed in a normal Module

Public Function AVGBACK(ByVal myRng As Range, CellCnt As Integer) As Double
Dim Fst As String, Lst As String, pos As Single


pos = InStr(myRng.Address(False, False), ":")
Lst = Mid(myRng.Address(False, False), pos + 1, _
    Len(myRng.Address(False, False)))
Fst = Left(myRng.Address(False, False), pos - 1)
ROSV = 0

Do Until CellCount = CellCnt
cl = Range(Lst).Offset(ROSV, 0)
If Len(cl) > 0 Then
SumVal = SumVal + cl
CellCount = CellCount + 1
End If
If Range(Lst).Offset(ROSV, 0).Address(False, False) = Fst Then
GoTo Done
End If
ROSV = ROSV - 1
Loop
Done:
AVGBACK = SumVal / CellCount

End Function

 

[Aladin Akyurek]

Re: Help with Running Average/Sum of Last 8 Data Points (wee

I need help with a formula that will average the last 8 cells with non-blank values in the range e3:e20. (There will be blanks and there may be less that 8 cells with values, in which case, I need it to average what's available.) I also need do sum the entries in g3:g20 with the same possibilities.

I've tried tweaking everything I found on the site so far, and no luck!

Any ideas?[/b]

Book3
	A	B	C	D	E	F	G
3		8			3		0.3
4		8			4		0.9
5	Avg of Last 8	4					1.2
6	Total of Last 8	32.3			5		3.7
7					6
8					7		3.4
9							7.7
10					9
11					0		2.9
12					8		2.6
13					7		1.8
14					4
15
16					3		3.1
17					2		4.3
18					5		4.8
19							5.1
20					3
Sheet1

B4:

=MIN(COUNT(E3:E20),B3)

A5:

="Avg of Last "&B3

A6:

="Total of Last "&B3

B5:

Control+shift+enter...

=AVERAGE(IF(ISNUMBER(MATCH(ROW(E3:E20),LARGE(IF(ISNUMBER(E3:E20),ROW(E3:E20)),ROW(INDIRECT("1:"&$B$4))),0)),E3:E20))

B6:

Control+shift+enter...

=SUM(IF(ISNUMBER(MATCH(ROW(G3:G20),LARGE(IF(ISNUMBER(G3:G20),ROW(G3:G20)),ROW(INDIRECT("1:"&$B$4))),0)),G3:G20))

 

[Tracy813]

Thank you! Thank you! for all your help, Milo & Aladin! It worked!!! I have been pulling my hear out over this stuff!