I have the following values in my excel:
shift 2
data 131 12 55 23 45 11 23 45 119 77I would like to define a formula which refers to the n-th cell to the left in the data row, where n is given by the user in a cell (the shift in my case).
For example, if shift is 2, the output row should be:
shift 2
data 131 12 55 23 45 11 23 45 119 77
output 0 0 131 12 55 23 45 11 23 45If the user changes the number in the shift cell to 3, it automatically should change to:
shift 3
data 131 12 55 23 45 11 23 45 119 77
output 0 0 0 131 12 55 23 45 11 23Is it possible?
I'm trying to do it with INDIRECT, but seems I cannot do math with columns or rows: In my case n is not a index, but a costant I need to add to an index cell.
2 Answers
Try this for the output formula in B4 and copy to the right.
=IF(COLUMN()-1<=$B$1,0,INDEX($B$2:$K$2,COLUMN()-1-$B$1))Index takes a number as the second argument and will return the cell that is in that position in the range. You can create any formula to return a number nested in the Index function. In this case it is the column number with the shift number subtracted.
I would use a formula based on INDEX, like teylyn suggests, but you can also do it easily with INDIRECT, as you're pursuing. I'll show you how to do it for educational purposes, but be aware that INDIRECT has a number of shortcomings, which you can read about here: INDIRECT – Excel’s Most Evil Function
INDIRECT has the option to use R1C1 addressing in a formula without changing the default for Excel, which comes in handy for handling offsets. You can read about R1C1 addressing, for example here: Excel – R1C1 Reference Style vs. A1.
In a nutshell, you use R to identify row information and C to identify column information. If you specify no number, it assumes the current row or column. If you specify a number, it refers to that row or column number. If you put a number in square brackets, it refers to that offset from the current row or column. You turn on R1C1 format in INDIRECT with an optional last parameter of FALSE or 0.
The formula in the first output cell, B3, is:
=IF(COLUMN()<$B$1+2,0, INDIRECT("R2C[-" & $B$1 & "]",0))The IF test for the column ensures that you zero fill any output cell prior to the first offset value.
The INDIRECT expression builds the R1C1-style address to refer to row 2 (the data), with a negative offset for the column of the value in the Shift cell, B1.
