I have looked into the Excel Offset Formula but I am not quite sure if it is the correct formula to use for my specific problem.
I have an excel source table that looks like the following:
| Month 0 | Month 1 | Month 2 | Month 3 | |
|---|---|---|---|---|
| January | 100 | 120 | 150 | 180 |
| February | 200 | 220 | 250 | |
| March | 300 | 320 | ||
| April | 400 |
However, what I want to achieve is to get a second table, where each row is offset by one additional cell when compared to the row above. The result should look like this:
| Month 0 | Month 1 | Month 2 | Month 3 | |
|---|---|---|---|---|
| January | 100 | 120 | 150 | 180 |
| February | 200 | 220 | 250 | |
| March | 300 | 320 | ||
| April | 400 |
Is there a way in Excel to achieve this with a formula that references to the source table?
Thank you!
CodePudding user response：
Working on the assumption that your cell January is cell A2, try the formula:
=IFERROR(N(OFFSET(B2, 0, -ROW(B2) 2)), "")
It's not perfect as it puts zeroes at the start of offset rows (without the N(...) formula in the middle there it would have put the month names there) but it might hopefully be sufficient for your purpose?
If your source table isn't at the top of your screen, you'll have to adjust the 2 to compensate, have a play around with it.
EDIT just realised that replacing the 'N' with NUMBERVALUE gets rid of the zeroes: =IFERROR(NUMBERVALUE(OFFSET(B2, 0, -ROW(B2) 2)), "")