3

I am trying to find a function that selects from a certain cell from all spreadsheets. For example, I want to calculate AVERAGE('Sheet1'!B3, 'Sheet2'!B3, 'Sheet3'!B3), but I don't want to explicitly write SheetX, I am rather looking for something like AVERAGE('allsheets':B3)

The reason I am doing this is that I would like the average to be recalculated whenever I add a new sheet.

I am using Google Spreadsheets, I am sure I can write a script to do that, but I think such a trivial problem might have a solution already.

migrated from superuser.com Jan 26 '15 at 9:08

This question came from our site for computer enthusiasts and power users.

1

Assuming you meant 'selecting from a certain cell from all sheets' (in one spreadsheet), custom function written in GAS that does that could look like this:

function avgCellAllSheets(cell) {
var arr = [];
SpreadsheetApp.getActive()
    .getSheets()
    .forEach(function (s) {
        arr.push(s.getRange(cell)
            .getValue());
    });
return arr.reduce(function (cur, prev, i) {
    return cur + (prev - cur) / (i + 1);
},0);
}

Fomula to be entered in the sheet is

=avgCellAllSheets(cell) 

example:

=avgCellAllSheets("A1")

However, as custom functions like that seem to suffer from memoization, it will probably not auto-update after adding a sheet. There are workarounds, e.g. adding a dummy parameter, let's say the content of a certain cell that when incremented 'forces' a recalculation of the formula)

  • Very nicely prepared. Is it OK nowadays to use the forEach method? – Jacob Jan Tuinstra Feb 21 '15 at 20:07
  • @JacobJanTuinstra. Can't see why not ? I guess it all boils down to the task at hand. Both have their advantages and disadvantages, as you probably know... ;-). Thanks for the feedback ! – JPV Feb 21 '15 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.