1

I want to be able to reference a cell whose address I will calculate using a formula that depends on other cells (i.e. "the cell that is X rows before this one, where X is read in D2"). How to do this ?

Here an example where I have a series of numbers, and I want to calculate the delta every 7. I can write "=A8-A2" manually but I would want to write something like "=A8-A[8-D2]". How to do this ?

(Of course the first 7 deltas are meaningless and therefore return errors, but that's no matter for now.)

enter image description here

2 Answers 2

1

OFFSET

Syntax: OFFSET(cell_reference, offset_rows, offset_columns)

cell that is X rows before this one, where X is read in D2

=IFERROR(OFFSET(A2,D2,0)) //  Assumes -7 in D2

Using it in a calculation

=IFERROR(A2-(OFFSET(A2,D2,0)))
4
  • 1
    Ha ! Much better than that other one !
    – Charles
    May 25, 2023 at 13:22
  • @Charles, per your comment, INDIRECT is a volatile function that can seriously affect performance because Excel assumed it can change from one moment to the next even if none of its arguments has changed, and reevaluates it together with all dependents, every time it recalculates. Microsoft Excel Recalculation Also colorful but with some useful notes: INDIRECT – Excel’s Most Evil Function
    – Blindspots
    May 25, 2023 at 18:19
  • Does this apply to Google Spreadsheet though ?
    – Charles
    May 30, 2023 at 9:30
  • @doubleunary probably knows. I assumed so, but now that you asked, and I try to confirm, I'm getting very few but unreliable results that conflict.
    – Blindspots
    May 30, 2023 at 23:46
0

One possible solution is to use INDIRECT which will return a reference to a cell, which can then be read by Google Spreadsheets like any other reference to a cell that we input manually or by clicking on said cell. This function takes a string which is the cell reference itself, but as text - which we can then construct.

=A2-INDIRECT("A"&TEXT(ROW(A2)-$D$1,"0"))

-> ROW(A2) returns the number value 2 (in general it returns the row of a given cell)

-> Therefore ROW(A2)-$D$1 applies the desired offset to the row

-> TEXT(...,"0") converts that to a string by formatting the number in the most straight forward way (all the digits of the integer, and nothing else).

-> Then we concatenate that with "A" using & to get the desired reference as a string "A[this row minus offset]"

-> Then we apply INDIRECT to read this string as a cell reference and use it as such in the formula.

7
  • Welcome to Web Applications Stack Exchange. Your formula is fine, but there is no need to use the text() function. Just concatenate "A" & row(A2) - D$1. If cell D1 may contain a fractional number, use "A" & int(row(A2) - D$1). Apr 27, 2022 at 10:06
  • Indeed it works without TEXT like you say ! So what as the rules of precedence concerning & then ?
    – Charles
    Apr 27, 2022 at 10:08
  • Google Sheets uses these operator precedence, roughly: ()^[/*][-+]&. Division and multiplication have the same precedence, as do subtraction and addition. Apr 27, 2022 at 10:11
  • Yes okay, good to know, thanks. So you can put almost anything next to & then eh ?
    – Charles
    Apr 27, 2022 at 10:12
  • Use parentheses when in doubt. Apr 27, 2022 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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