Excel - 'dynamic' average

  pauldonovan 15:39 04 Jan 2006

(I reckon VOG will crack this, just a guess!)

A colleague would like an average in an excel spreadsheet. There is a table, and there will be an average on each row, which should average a value on that row in every 3 columns. Clearly this can be done when the sheet is first set up, but the repeating set of 3 columns will be added to in the future and I'd prefer the average formula not to have to be updated.

Jan Feb
Avg A B C D E F
Row 1
Row 2
Row 3

Avg in each row should be average of A,D,G column for that row but if someone then adds March I want it to include that in the average. This is hypothetical so it isn't actually months on their spreadsheet so please don't suggest just setting up the 12 months from the start! Dynamic average is the key!

  pauldonovan 15:53 04 Jan 2006

Don't you just love the reformatting that removes all your lovely spaces.

Click here for a rough screenshot:

click here

  VoG II 21:29 04 Jan 2006

I can't think of a way of doing this using worksheet functions - it would be extremely convoluted. What is needed is to find the last cell in a row and that is somewhat controversial as it involves using Excel functions to produce results that they were not intended to do.

The following macro will, I think, do what you want. It assumes that there are no headers other than in Row 1 and that the sheet is layed out as per your screenshot.

Sub varavg()
Dim LastRow, LastCol, iRow, iCol, zum, i
LastRow = Cells(Cells.Rows.Count, 3).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For iRow = 2 To LastRow
zum = 0
i = 0
For iCol = 3 To LastCol Step 3
zum = zum + Cells(iRow, iCol).Value
i = i + 1
Next iCol
Cells(iRow, 2).Value = zum / i
Next iRow
End Sub

To use this, open the workbook, ALT+F11 to open the Visual Vasic Editor, Insert|Module and paste in the code. Close the VBE. Tools|Macro|Macros, click on varavg and click Run. Or, preferably, put a button on the workbook and assign it to varavg.

The disadvantage is that the user will need to remember to run the macro or click the button to update the sheet.

Sorry for the slow response - I was trying to give the others a chance ;o)

  pauldonovan 21:42 04 Jan 2006

..I could prob macro it but was hoping there'd be some fancy way without!

I think i've used something before where you can run a macro when the s/sheet is updated? A bit of overkill processing-wise but avoids the problem of relying on the user pressing a button or remembering to run the macro?

  VoG II 21:50 04 Jan 2006

You could use

Private Sub Worksheet_Change(ByVal Target As Range)
Call varavg
End Sub

but I suspect that would be really slow.

A better way might be to ensure that the sheet is updated whenever the workbook is closed:

Sub Auto_Close
Call varavg
End Sub

  VoG II 21:52 04 Jan 2006

Just spotted a typo - the following is the correction for a sheet where the data starts on row 2

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

  pauldonovan 22:00 04 Jan 2006

I salute you...will try it at work tomorrow but I know I can get it to work with this as a basis! Cheers.

  VoG II 22:07 04 Jan 2006

Just a thought. If you go down the

Private Sub Worksheet_Change(ByVal Target As Range)
Call varavg

route then you could use

iRow = Target.Row

and take out the For iRow = 2 To LastRow loop.

However, note that the macro assumes that every row has the same number of columns as row2.

  pauldonovan 22:10 04 Jan 2006

You had your shreddies today! The ideas are coming thick and fast now! Good one...i'll give it a go and let you know.

  mammak 22:14 04 Jan 2006

Good stuff VoG™ might need you soon as I am about to take my Advanced ECDL Excel Module.
pauldonovan, VoG™ is the best is he not.
PS. creep creep lol

  VoG II 22:21 04 Jan 2006

You can creep all you like but you must know by now that I hate Excel ;o)

I'm going to send you a private message as it doesn't belong on this thread - I hope you won't mind.

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

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?