CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 33

Excel VBA Average of each cell on each worksheet

I have a workbook with multiple worksheets and the worksheets are layed out exactly the same. For example C5 could be cycle time, and I want to know the average cycle time over all the sheets. The only issue is that I have a lot of data, I have data from row 5 through 125004, and columns A through J. I am unsure of how to average the data onto one sheet at the end that still has the 125004 rows and columns A through J, just displaying the average from all the worksheets.

Upvotes: 0

Views: 549

Answers (2)

Johni Michels
Johni Michels

Reputation: 145

Create a module in VBA and write the following

Public Function GetAllSheetsAverage(makeUpdate As Boolean) As Double
If makeUpdate Then
    Application.Volatile
End If
Dim rng As Range
Dim sht As Worksheet
Set rng = Application.Caller
Dim sum As Double
sum = 0
Dim count As Integer
count = 0
For Each sht In ThisWorkbook.Worksheets
    If sht.Name = rng.Worksheet.Name Then GoTo SkipOwnSheet

    Dim rng2 As Range
    Set rng2 = sht.Range(rng.Address)

    sum = sum + rng2.Value

    count = count + 1

SkipOwnSheet:


Next sht

GetAllSheetsAverage = sum / count

End Function

The use the function =GetAllSheetsAverage(TRUE()) on the cells. The TRUE will make the function listen to every change in the worksheets, so it'll probably be slow on such a huge amount of data. You can change to false, but the you'll have to update the values manually whenever there is a change on the values from the other sheets.

Upvotes: 0

pnuts
pnuts

Reputation: 59442

A 3D formula would appear to be adequate, such as:

=AVERAGE(Sheet1:Sheet3!C5)

copied around to suit.

Upvotes: 1

Related Questions