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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))
 

Tracy813

New Member
Joined
Jul 25, 2007
Messages
2
Thank you! Thank you! for all your help, Milo & Aladin! It worked!!! I have been pulling my hear out over this stuff!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,064
Members
425,451
Latest member
JohnBrooksBiddle

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
Top