Forum Discussion
Averaging the sum of a specific cell across multiple sheets
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 Thanks for the explanation. Makes sense why it is not returning the value I want now.
I followed what you laid out, but the AVERAGEIF is presenting a #DIV/0! error. The values for AC23 and AC27 (had to add a couple cells, but its updated across all sheets) are returning correctly, so not sure what error I've made now.
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?
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!
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) )
