Average of cell range from string

Question

In Google Sheets I have a string like this: "G4;G15;G26;G37;G48".

How do I get the average of the values in those cells?

I'm sure there's some arrayformula and indirect to be used, but I can't seem to find the right combo.

Answer 1

Supposing that your string is exactly as show (i.e., G4;G15;G26;G37;G48) and resides in cell A2:

=AVERAGE(FILTER(G:G,ISNUMBER(FIND(ADDRESS(ROW(G:G),COLUMN(G1),4)&";",A$2&";"))))

Adjust A$2 to match the actual location of your string.

---

EXPLANATION:

First, the formula must "normalize" the string of target cells in Column G. It might seem like a small detail, but we needed a way to distinguish an entire cell reference like G15 from smaller viable cell-strings within that, such as G1 (which is the first two charaters of "G15"). Since all but the last cell reference in the composite string from A1 is followed by a semicolon, the easiest way to normalize where each full cell reference ends is to add a semicolon after the last one (which is the same as just tacking one onto the end of the whole string). That is why you see &";" added to both elements of the FIND.

Since we are trying to FIND things within a string, we need a way to look for "apples within apples" so to speak. So I used ADDRESS to create an array of strings from the cell references in COLUMN G. ADDRESS can create the cell-reference-string in four different literal ways: "$G$1" / "$G1" / "G$1" / "G1". I want the fourth version, since this is how the match string is set up. That is the ,4 that you see as the last element of ADDRESS.

Let me pause here. I said that the ADDRESS call will form an array of strings from all the cell addresses in Column G. Yet you don't see ArrayFormula in the formula. That's because FILTER always forms an array.

So the complete FIND says, in English, "Form a string version of every cell in Column G with an added semicolon tacked to the end of it; then look for every one of those strings within A1 with its added semicolon."

Now, some of those strings will be found, and some of them won't. The ones that do would return a number of the position in A1 where they are found (e.g., "G4;" would return 1, "G15;" would return 4, etc.). But any no-shows (e.g., "G3;") would return an ERROR. This is where ISNUMBER comes in. I only want the set of FIND results that return numbers. This will be all the matches found in A1.

Then FILTER takes these strings and matches them up against all the actual, live cell references in G:G. Any cells in the range G:G that, when turned into strings, can be FIND(ed)? within the composite string in A1 will be returned as an array.

We then simply AVERAGE those.