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

#### Tracy813

##### New Member
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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Milo_Minderbinder

##### Well-known Member
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

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

#### Milo_Minderbinder

##### Well-known Member
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

Lst = Mid(myRng.Address(False, False), pos + 1, _
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``````

##### MrExcel MVP
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
Thank you! Thank you! for all your help, Milo & Aladin! It worked!!! I have been pulling my hear out over this stuff!

Replies
19
Views
411
Replies
22
Views
414
Replies
11
Views
351
Replies
3
Views
777
Replies
6
Views
326

1,186,924
Messages
5,960,584
Members
438,486
Latest member
ncc84330

### 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.

### Which adblocker are you using?

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

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