Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #625 Jul 14, 2012 www.energyefficientbuild.com.
user DLejeune said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/eq6Sj011u5k)
Iterating an indirect reference within an array formula.
I've been searching around and I haven't been able to find quite the right answer for this.
Basically what I want is to create a formula that will (as an example) add the two columns of preceding data within the same row and iterate itself automatically down as with an arrayformula expression. I've come very close with =sum(indirect("A"&row()), indirect("B"&row())), but when I throw that into an arrayformula, the continues will evaluate for the first row instead of the row that they're in.
The idea is this:
[A] [B} [C]
1 2 =arrayformula(sum(indirect("A"
3 4
C1 would return 3, and C2 would be a continue and return 7, etc.
If I have a spreadsheet pulling data out of a web table, and the number of rows in the table changes frequently I don't want to have to worry about dragging the formula down, and I don't want to have to create a huge number of rows and just have the formula evaluating blank cells until the table gets there.
Is there any way to do this?
---------------------------------------------------------------------------------------------
following is a solution to a generalized problem with open_ended ranges
user DLejeune said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/eq6Sj011u5k)
Iterating an indirect reference within an array formula.
I've been searching around and I haven't been able to find quite the right answer for this.
Basically what I want is to create a formula that will (as an example) add the two columns of preceding data within the same row and iterate itself automatically down as with an arrayformula expression. I've come very close with =sum(indirect("A"&row()), indirect("B"&row())), but when I throw that into an arrayformula, the continues will evaluate for the first row instead of the row that they're in.
The idea is this:
[A] [B} [C]
1 2 =arrayformula(sum(indirect("A"
3 4
C1 would return 3, and C2 would be a continue and return 7, etc.
If I have a spreadsheet pulling data out of a web table, and the number of rows in the table changes frequently I don't want to have to worry about dragging the formula down, and I don't want to have to create a huge number of rows and just have the formula evaluating blank cells until the table gets there.
Is there any way to do this?
---------------------------------------------------------------------------------------------
following is a solution to a generalized problem with open_ended ranges
No comments:
Post a Comment