How can I use the OFFSET() formula with a range of values?

6
votes

EDIT: Disclaimer about the XY problem: The actual, concrete problem I'm trying to solve is: How can I make "recursive" (is that the right term here?) formulas that use infinite ranges in Google Spreadsheets/Excel? The solution I'm working with involves the OFFSET() formula. I'm asking this question because I'd like to get an extensible understanding of the way formulas use and implement ranges, especially infinite ranges.

I'm working in Google Spreadsheets and trying to create ArrayFormulas that will automatically expand without needing to use the fill handle as new data is added in the requisite columns.

To do this, I want to use infinite ranges such as A2:A when I do calculations so that no matter how much data is added, I'll never have to drag-fill any data or formulas and I can just let the spreadsheet iterate and do the work.

I'm running into a problem, though, when I try to use these infinite ranges with the OFFSET() formula. What I'm trying to do is have each cell in the range pull values from a couple of the cells next to it (thus the offset), do a simple calculation with those values, and make that the new cell value. But because the formula interprets the instruction as attempting to offset an entire (infinite) range of values, it returns a #REF! error. Here's a shared example sheet that demonstrates what I'm trying to do.

https://docs.google.com/spreadsheets/d/1V3ldSBoCrzyVWcn66wFBkDOmxt6iuYgtiU_H4KQ6J14/

If for some reason you can't see the formulas, the formulas I'm using are:

C3 =ArrayFormula(Offset(C3:C, 0, -1) - Offset(C3:C, -1, -1))

F4 =Arrayformula(Offset(F3:F, 0, -1) + Offset(F3:F, -1, 0))

Both of these return a #REF! error. C3's alt-text reads: "Error: Result was not automatically expanded, please insert more rows (1)." F3's alt-text reads: "Error: Circular dependency detected."

I'm decently confident why these don't work - you're apparently not allowed to use and refer to ranges this way. I don't, however, know how to fix this.

The two use-cases in the spreadsheet are essentially the same thing, backwards. Chances are if I can figure out one I can reverse-engineer it to work for the other one, but I've had no luck so far.

I've Googled around a lot and while I've never found anything that solves this problem, a lot of similar problems seem to be solved by using the INDIRECT() function. I can't understand how this would apply here, though, that function seems to be strictly for parsing values dynamically from cells with variable input.

(I should probably mention that this data is on a back end sheet and it's getting pulled on another sheet to display some charts on the front end. I wouldn't ask a question here if this data was the only thing involved.)

Any help or a step in the right direction would be appreciated.

1
google-sheetsarray-formulas
I can't actually see the formulas on the worksheet, but it could be my work computer's IE is horrible. But if you're using the the reference "A2:A" that would be the error. You should refer to the range as "A:A" to have the entire column range selected. – amoy

1 Answers

2
votes

If I'm understanding your question, one way to make an offset function expand automatically is to attach a count function, eg. in excel

=offset(C2,,,counta(C:C))

However, this will give you a circular reference error. To address this issue, this is one solution that I have used:

"OFFSET(C3,,,MATCH(9.99999999999999E+307,OFFSET(C3,,,ROWS(C:C)-ROW(C3))))"

This function passes an array based on the last number cell in the column. And for it to be used properly, it needs to be wrapped in a function that can handle arrays, like SUM().