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

Tracy813

New Member
Joined
Jul 25, 2007
Messages
2
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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I modified that UDF to work for you

Essentially, you could put this formula anywhere in your file

Code:
=AVGBACK(E3:E20,8)

The UDF code is placed in a normal Module

Code:
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
 
Upvote 0
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
ABCDEFG
3830.3
4840.9
5Avg of Last 841.2
6Total of Last 832.353.7
76
873.4
97.7
109
1102.9
1282.6
1371.8
144
15
1633.1
1724.3
1854.8
195.1
203
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))
 
Upvote 0
Thank you! Thank you! for all your help, Milo & Aladin! It worked!!! I have been pulling my hear out over this stuff!
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top