Increment a number within a formula by 2 each time

Question

As in this image:

I have the following formula in merged cellset F229:F231, which works correctly:

=OFFSET('Food Diary'!$A$2,31*(ROWS($F$18:F229)-3),)

This pulls data from another worksheet - in this case the value 73.0 is pulled (there are similar formulas in the cell range H229 to M231.

Essentially I want the -3 part at the end of above OFFSET formula to increment by two each time I copy and paste the three row set. So in the merged cellset of F232 - F234 it would be -5 and in the next one it would be -7, then -9 etc.

It's a bit of a hack but this would result in the correct data being pulled. This is not a work spreadsheet, just a personal log to record my food etc so doesn't have to be ideal.

Answer 1

Use one of the following formula for your counter starting at -3 and changing by -2 every three rows. You can either start using row A1 or you can reference your current cell and make some adjustments to the formula to achieve the same result.

=-3-2*(ROUNDUP(ROW(A1)/3,0)-1)

or

=-3-2*(ROUNDDOWN((ROW(G229)-ROW($G$229))/3,0))

both will work for generating the number you are looking for

Your final formula might look like:

=OFFSET('Food Diary'!$A$2,31*(ROWS($F$18:F229)+(-3-2*(ROUNDUP(ROW(A1)/3,0)-1))),)

Answer 2

Change -3 to +2*Row()/3 + c, where c is a modifier to ensure that your first row lines up

For example, if the first row is line 4, and you want the value to be 2:

- 2 * Row() / 3 + c
- 2 * 4 / 3 + c
- 8 / 3 + c
- 2.666 + c
c = - 1/3
- 2.666 - 1/3
- 3
- 2 * Row() / 3 - 1/3

Then, when you copy it down to Row 7:

- 2 * Row() / 3 - 1/3
- 2 * 7 / 3 - 1/3
- 14 / 3 - 1/3
- 4.666 / 3 - 1/3
- 5