Excel question re AVERAGE function

  24dragon 10:17 23 Jul 2004

If I have column A listing a weekly date and col B with a number in, is there any way I can get an average for a set number of rows?

For example, I enter dates on 20 rows and then average the numbers, but on week 21, I want to 'lose' week 1 but include week 21, on week 22, lose rows 1 & 2 and 21 but include 22 and so on. Hopefully this would always give me an average of the last set of 20 rows without having to change the formula each time.


  Simsy 17:17 23 Jul 2004

the average will go in Col C, and there will be nothing in C1:C19, because you want the average for the last 20 dates; until the 20th date there aren't 20 dates to average?

Assuming that to be so, enter the following in C20

=Average(B1:B20) and hit return

Then select C20 and hover the mouse over the bottom right of the cell, until it turns to a black cross. Left click and drag down the cells below and it will copy the formula into the cells you select.

Because Excel uses relative cell references, (by default), the formulas in each of the new cells will have the formula adjusted acordingly, i.e. cell C21 will contain the formula


C22 will have


and so on and so on....

Does that help?



  exdragon 09:05 25 Jul 2004

I posted the question at work and then went home early!

The average will go at the bottom of col C as there's more data in cols D - M, so it's like this:

R1 1.7.04 7

R2 9.7.04 3

R3 19.7 04 4

R4 27.7.04 10 etc

So, in the example above, on 19/7, I want the average of rows 1 to 3, then when I put in 27/7, I insert a row and put in the date and number. This then shows the average of R1 - R4, but can I make it do from R2 - R4? And then the following week, from R3 - R5? I don't know if it can be done automatically, or if I need to highlight the new cells every time I add a new date and number.

As well as averages at the bottom of the column, I've also got Best and Worst, which I assume would use the same formula, and the figures carry on across the spreadsheet.


  Simsy 18:11 25 Jul 2004

I think I understand what you want to do a bit better now...

It can be done without VBA, using just the formula method, BUT, you'll have to be disciplined in the way you make the entry...

lets say you have the formula in cell C21, (=AVERAGE(B1:B20)), the process is to copy the formula from C21, (which will have the formula averaging cells B1:B20), to cell C22, (which will then have the formula averagng cells B2:B21), and THEN enter you data into ROW 21.

The entry in Cell C21 will now be whatever you enter, overwriting the formula that was there, but because you copied the formula down first there should be no problem.

Does that make sense?

Alternatively, it can be done with VBA, but I'd have to think about that a bit longer... if VoG or whisperer are about I'm sure they can sort this quicker than I...

I probably can get it, but I'm afraid VBA take a while for me, and I haven't got the time right now!

If no joy with it in a few days I'll get onto it with VBA for you.

Good luck,



I have not had too long to check your changed requirements but I think that the following code placed in a module of the VBE will provide you with a manual answer, changing the reference to Sheet1 above to whatever the name of your worksheet is:


Sub AverageTwentyRows()

Dim StartRow As Integer, EndRow As Integer

Dim MyRange As Range

EndRow = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

If EndRow < 20 Then Exit Sub

StartRow = EndRow - 19

Set MyRange = Range("B" & StartRow, "B" & EndRow)

Range("C" & EndRow).Value = WorksheetFunction.Average(MyRange)

End Sub


If you want an automatic solution then place the following on the code sheet for the worksheet itself.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 2 Then Call AverageTwentyRows

End Sub


If you wish to contact me using the envelope then I will send you a copy of my workbook which shows the running average of the last 20 entries and also displays the best and worst cases in the same period.

The code is not the same as above


  Simsy 08:03 27 Jul 2004

I think I understand all this, with the exception of the "2" in the following line;

EndRow = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

Can you explain what this is doing? No doubt the penny will drop with me just before I read an explanation!


(with apologies for hijacking the thread!)



It is simply the column that one is looking for the last entry in.

I would for instance pre-load a whole string of dates in Column A therefore it would be no use looking for the last row there.

Regards Gordon

  Simsy 10:31 28 Jul 2004

"No doubt the penny will drop with me just before I read an explanation!"

I was right!




This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires