Share via

Excel - incrementing cell reference by 5

Anonymous
2016-07-11T11:45:45+00:00

Hi,

I have an issue regarding incrementing formulas, hope my explanation's clear.

I use two sheets - a main one for displaying data and another named "RAW" for the raw data that is constantly updated.

On the main sheet, I have a formula that calculates the average between two values. Simplifying it, it looks like this :

=SUM(RAW!K3+RAW!K6)/(RAW!C3+RAW!C6)

Now on the raw data sheet, I need to get the values from the formula above, incrementing it by 5 down.

So the next formula should contain the values from :

=SUM(RAW!K8+RAW!K11)/(!RAWC8+RAW!C11)

Simply dragging these two formulas doesn't work, it only increments the values by 2.

Of course, changing each value individually will work, but there are a LOT of raw entries that I need to use. I have been googling answers about this and found about OFFSET, but I'm struggling to figure out how it works.

Can someone explain to me how OFFSET works, as well as a proper formula on how to do what I want? That would be really helpful.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2016-07-11T12:44:12+00:00

    OFFSET has several forms, but the simplest is OFFSET(mycell, number-of-rows, number-of-columns). This refers to the cell number-of-rows down and number-of-columns to the right of mycell.

    (Remark: if number-of-rows is -5, it refers to 5 rows up from mycell. If number-of-columns is -3, it refers to 3 columns to the left of mycell).

    Let's say the first formula is in row 2. You can then use

    =(OFFSET(RAW!$K$3, 5*(ROW()-2), 0)+OFFSET(RAW!$K$6, 5*(ROW()-2), 0)) / (OFFSET(RAW!$C$3, 5*(ROW()-2), 0)+OFFSET(RAW!$C$6, 5*(ROW()2), 0))

    ROW()-2 is 0 in row 2, 1 in row 3, 2 in row 4 etc.

    5*(ROW()-2) is 0 in row 2, 5 in row 3, 10 in row 4 etc.

    2 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2016-07-11T14:05:26+00:00

    My approach is similar to Hans

    =(OFFSET(RAW!K$3,5*(ROW(A1)-1),0)+OFFSET(RAW!K$6,5*(ROW(A1)-1),0))/(OFFSET(RAW!C$3,5*(ROW(A1)-1),0)+OFFSET(RAW!C$6,5*(ROW(A1)-1),0))

    best wishes

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-07-12T09:42:59+00:00

    Two points:

    1. ROWS is a much better choice than ROW in this type of construction. Not only is it unaffected by row insertions within the range (unlike ROW), but, perhaps more importantly, is not dependent upon the row in which the formula lies, and hence does not necessitate any manual user input to determine the constant to be added/subtracted from the ROW portion at any given time.

    The point being that:

    ROWS($1:1)

    (or

    ROWS($A1:A1)

    if you prefer), is always equal to 1, no matter in which row you place the formula containing this construction, which of course is not at all the case with ROW.

    https://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/

    1. OFFSET is a volatile function, and should be avoided if possible. In this type of construction, we can always employ INDEX in place of OFFSET, viz:

    =(INDEX(RAW!$K:$K,5*(ROWS($1:1)-1)+3)+INDEX(RAW!$K:$K,5*(ROWS($1:1)-1)+6))/(INDEX(RAW!$C:$C,5*(ROWS($1:1)-1)+3)+INDEX(RAW!$C:$C,5*(ROWS($1:1)-1)+6))

    which is equivalent to:

    =SUM(RAW!K3+RAW!K6)/(RAW!C3+RAW!C6)

    and, when copied down, gives results equivalent to:

    =SUM(RAW!K8+RAW!K11)/(RAW!C8+RAW!C11)

    =SUM(RAW!K13+RAW!K16)/(RAW!C13+RAW!C16)

    ...

    etc. , etc.

    Regards

    0 comments
  2. Anonymous
    2016-07-12T08:25:44+00:00

    Thanks so much for the explanation! At first, I simply input your formula onto the sheet, and dragged it across all the cells. At first the results are fine but as I went further down, the results skewed over and didn't work. I tried Bernard's suggestion and the same thing happened - results differ the further went down.

    Turns out that when I drag the formula down to all the cells, the -2 gets added, changing the formula completely. Tried manually placing down onto all cells, and it works as expected.

    Thanks once again!

    0 comments