Sep 30 2023 11:55 PM
Sep 30 2023 11:55 PM
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 formula in it to provide the number that I want to average (Again not sure if this will cause an issue?)
The formula that I am using is =AVERAGE('SHEET NAME 1':'SHEET NAME 2'AC25!) but this is clearly the wrong way to go about it, as I keep getting a #NAME? error. I took a look at some of the suggested/previous posts that were similar, but haven't been able to get any of them to work properly.
Oct 01 2023 12:27 AMSolution
=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.
Oct 02 2023 05:06 PM
@Hans Vogelaar I have been trying to play around with this, to see if I can have the formula omit any pages that have a No listed in cell AC23, so that it automatically filters out the disqualified sheets.
=AVERAGEIF(AC23,"No",'SHEET NAME 1:SHEET NAME 2'!AC25)
Any chance you could explain where I'm going wrong with this one?
Oct 03 2023 12:38 AM
In the first place, you refer to AC23 on the active sheet, not on the list of sheets.
In the second place, unlike AVERAGE, the function AVERAGEIF does not support a multi-sheet range, at least not directly.
As a workaround, create a list of the sheet names from SHEET NAME 1 to SHEET NAME 2, and use formulas to retrieve the values of AC23 and AC25. You can then use AVERAGEIF:
Oct 06 2023 04:51 PM
@Hans Vogelaar 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.
Oct 07 2023 02:29 AM
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?
Oct 07 2023 10:28 PM - edited Oct 07 2023 10:54 PM
@Hans Vogelaar 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!
Oct 08 2023 01:49 AM
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) )