Average of Same Cell Across Multiple Sheets

user_18328150 0
Viewed 142

I tried

=AVERAGE(Jeff!C2,Blaze!C2,Hunter!C2,Alexia!C2)

=AVERAGE(Jeff:Alexia!C2)

Those names are sheet names and the formula is in cell C2 in the master sheet called 'FINAL SCORE'.

Error is

This value doesn't match the data validation restrictions defined for
this cell.

I need a formula or macro that to take the value in the same cell 'C2' in each sheet, and give me the average. C2 can only have numbers 0 through 10 (I locked this on each sheet).

This would then be replicated for each cell contiguously so same formula for D2, E2, F2 etc. (to aggregate scoring in a final column which I already have a working formula for.

Screenshot of Final Score sheet layout - every sheet looks identical except the other sheets have numbers ranging from 0-10 in C2
enter image description here

I am working on a project to rank anime that a group of friends watch to provide ranking/recommendations to each other.

excel-formula vba excel average
1 Answers

Range.Consolidate Method

Option Explicit

Sub ConsolAverage()
    With ThisWorkbook.Worksheets("FINAL SCORE").Range("C2:L2")
        Dim cell As Range
        For Each cell In .Cells
            cell.Consolidate Array( _
                "Jeff!RC", "Blaze!RC", "Hunter!RC", "Alexia!RC"), xlAverage
        Next cell
    End With
End Sub

EDIT:

  • Maybe try the following UDF by using =ConAVG() in each cell:
Function ConAVG() As Double
    Application.Volatile
    
    Const wsNamesList As String = "Jeff,Blaze,Hunter,Alexia"

    Dim wsNames() As String: wsNames = Split(wsNamesList, ",")
    Dim CellAddress As String: CellAddress = Application.Caller.Address
    
    Dim ws As Worksheet
    Dim cValue As Variant
    Dim Total As Double
    Dim tCount As Long
    
    For Each ws In ThisWorkbook.Worksheets(wsNames)
        cValue = ws.Range(CellAddress)
        If IsNumeric(cValue) Then
            If Len(cValue) > 0 Then ' exclude blanks
                Total = Total + cValue
                tCount = tCount + 1
            End If
        End If
    Next ws
    If tCount = 0 Then Exit Function
    
    ConAVG = Round(Total / tCount, 2)

End Function
user_98140690
Related