Forum Discussion

SeanHaggerty's avatar
SeanHaggerty
Copper Contributor
Sep 30, 2023
Solved

Averaging the sum of a specific cell across multiple sheets

Hello,    I am trying to average a specific cell across a range of sheets where they have all been named with text (Not sure it matters?). The fixed cell that I am trying to average does have a for...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 01, 2023

    SeanHaggerty 

    Try

     

    =AVERAGE('SHEET NAME 1:SHEET NAME 2'!AC25)

     

    I.e. single quotes before the name of the first sheet and after the name of the last sheet, but not in between. And ! between the second single quotes and the cell address.

HansVogelaar's avatar
HansVogelaar
MVP
Oct 07, 2023

SeanHaggerty 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

SeanHaggerty's avatar
SeanHaggerty
Copper Contributor
Oct 07, 2023

HansVogelaar Actually, I figured it out. I had the formula looking for No, when it should have been looking for Yes. Made the switch and its all good now! Thanks so much for all the help! 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 08, 2023

    SeanHaggerty 

    If its any consolation, I found the triple negative pretty confusing too!

    Exclude? <> "No"

    My solution to the problem probably won't be any use to you (unless you feel like purchasing 365) because I avoid much in the way of traditional spreadsheet techniques.

    My formula for the Group Average is

    = LET(
    include?, TOCOL(Exclude?)="No",
    shtTotal, TOCOL(sheetTotal),
    filtered, FILTER(shtTotal, include?),
    AVERAGE(filtered)
  )