Average Over Multiple Sheet Tabs with Multiple Values

[Karen]

Is there a limit to how many values you can average in a cell? Because I
don't understand what is happening. I'm trying to average about 70 different
values in 31 sheet tabs. What I do is click in the cell I want to place the
average. Then I choose the AVG function. I then click on each sheet tab and
choose the cell with the value I want to include in the average. I place a
comma between each value and then click the next one and so on. I keep
getting the infamous message that there is an error in my formula. Then I
chose a lot less values to average, using my same method, and it works fine.
HELP! This is driving me crazy. I don't want to calculate all these manually.
ANY help would be greatly appreciated, Karen

 

[Bernie Deitrick]

Karen,

If all the cells are the same on each sheet, you could use a 3D range reference. For example

=AVERAGE('First Sheet:Last Sheet'!B7)

Otherwise, if they are different cells, then select a cell that is free on every sheet, and use that
cell to link to the cell on the sheet that you need, like

=B7

Then use the above formula to average that cell.

HTH,
Bernie
MS Excel MVP

 

[Karen]

Thank you for your help Bernie. Actually, I'm aware of the little trick of
creating the "Start" and "Finish" tab, but only if all the cells are the same
on each sheet. I'm not sure how to do it when each sheet tab is different.

For example: The sheets are titled for each day of the week (1, 2, 3, etc.)
Then on sheet 1, the values I want to average are in cell G4, G20 & G21. Then
on sheet 2, the values are on cell G5, G6 & G20. Across all 31 sheets, there
are about 70 values I need to average. Would you be so kind as to let me know
how to do this.
Thank you very much for your time and your help, Karen

 

[Bernie Deitrick]

Karen,

How do you know what values to select? Is there a pattern, are there labels,
formulas in certain cells? Always Column G? At the top, at the bottom, ???

Bernie

 

[Karen]

Thanks again for your help. I have to average values that will always be in
column F and average more values separately that will always be in column G.
The rows could range from row 4 to row 27. In column G, I want to average all
the values that are equal to or greater than 3.0. I have some conditional
formatting set up with the criteria of <3.0 that fills the cells with yellow
when it meets that criteria. So, all I’m doing is choosing the cells that are
not yellow. Does this help? Sorry for the confusion.
Thanks again, Karen

 

[Bernie Deitrick]

Karen,

For column F, just use

=AVERAGE(Sheet1:SheetX!F:F)

For the Column G issues, you could create a list of the sheet names down
column A, starting in A2, and use this formula in B2:

=SUMIF(INDIRECT("'" & A2 & "'!G:G"),">3")

and this in C2
=COUNTIF(INDIRECT("'" & A2 & "'!G:G"),">3")

and then copy down.

And then the average depends on if you want a straight average or the
average of averages....

Here's a macro to place the sheet names:

Sub PlaceNames()
Dim ws As Worksheet
Dim i As Integer
i = 2
For Each ws In Worksheets
If ws.Name <> Activesheet.Name Then
Cells(i,1).Value = ws.Name
i = i + 1
End If
Next ws
End Sub

HTH,
Bernie
MS Excel MVP

 

[Karen]

Thank you - I'll check this out and get back to you.
Thanks again, Karen