Average cells from multiple sheets (excel2000)

Topic

New Reply

I have a workbook where the user can generate and name as many worksheets as they want.
I want to roll up the values from all the sheets to a results sheet that shows various calculations from values in all the sheets.
Eg. Average the value form A1 in all the sheets.
How do I loop through all the sheets that might be created to average the cell values.
Scott

Reply | Quote

Replies

Use a 3d reference:
=Average(FirstSheet:LastSheet!A1)

Steve

Reply | Quote

Steve,
Thanks
The problem with your solution is that I won’t know what the sheet names are going to be.
As I understand 3D references need the beginning and ending sheet names.
Scott

Reply | Quote

Insert a blank sheet FirstSheet to the left of all existing sheets and a blank sheet LastSheet to the right of all existing sheets.
Instruct users that they should only add new sheets in between.
Since aggregate functions such as SUM, MAX and AVERAGE ignore blanks, a formula such as =AVERAGE(FirstSheet:LastSheet!A1) will return the correct answer.

Reply | Quote

In addition to Hans’ suggestion you could create a UDF which loops thru all the sheets:

Option Explicit
Function AvgAll(sCell As String)
  Dim wks As Worksheet
  Dim dSum As Double
  Dim iCount As Integer
  Application.Volatile
  iCount = 0
  dSum = 0
  For Each wks In Worksheets
    iCount = iCount + Application.Count(wks.Range(sCell))
    dSum = dSum + Application.Sum(wks.Range(sCell))
  Next
  If iCount = 0 Then
    AvgAll = CVErr(xlErrDiv0)
  Else
    AvgAll = dSum / iCount
  End If
  Set wks = Nothing
End Function

Use it like:
=AvgAll(“A1”)

or even:
=AvgAll(“A1:A10”)

Steve

Reply | Quote

Thanks all,
I like Steves solution since the workbook will be a template type of application.
This way I won’t have to have the user see any blank sheets before or after the sheets they add.
Scott

Reply | Quote