Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Ex: =A1+A2 next cell down A3+A4 next cell down A5+A6, not A1+A2, A2+A3, A3+A4. I am trying to net cells, some are exact duplicates, some are not

share|improve this question
    
I’m not clear on what your objective is. Do you want a general solution to the question posed in the title, where successive rows in one column reference successive pairs of rows in another column, or are you just trying to solve the A1+A2, A3+A4, A5+A6 problem? I don’t understand “I am trying to net cells, some are exact duplicates, some are not”. –  Scott Nov 14 '13 at 21:14

3 Answers 3

If you are just trying to solve the A1+A2, A3+A4, A5+A6 problem, here’s a solution:  Enter

=SUM(OFFSET($A$1, 2*(ROW()-1), 0, 2, 1))

in cell B1 and drag/fill down.  OFFSET($A$1, 2*(ROW()-1), 0, … addresses a cell in Column A that steps down two rows every time the formula steps down one row.  For example, in B1, ROW() is 1 and this evaluates to OFFSET($A$1,0,0,… (cell A1), and in B2 it becomes OFFSET($A$1,2,0,…, i.e., cell A3OFFSET(…, 2, 1) identifies a region 2 rows high and one column wide.

A somewhat more general approach would be

=OFFSET($A$1, 2*(ROW()-1), 0, 1, 1)  +  OFFSET($A$1, 2*(ROW()-1)+1, 0, 1, 1)

or, equivalently,

=OFFSET($A$1, 2*(ROW()-1), 0, 1, 1)  +  OFFSET($A$2, 2*(ROW()-1), 0, 1, 1)

which explicitly references the two cells separately.

share|improve this answer

As a more general solution to Scott's answer, the following formula variant doesn't need to start in row 1:

=SUM(OFFSET($A$1, 2*(ROW()-ROW($A$1)), 0, 2, 1))

To use it, enter the formula in same row as the first data point, adjust the absolute cell references to point to the first data point, and Auto Fill the formula down. E.g. to get the sum of A2+A3, A4+A5, etc., put the formula in B2 and point it to "$A$2", and drag/fill the formula down.

Similarly, to take the sum of three cells at a time (A1+A2+A3, A4+A5+A6, etc.), you can use

=SUM(OFFSET($A$1, 3*(ROW()-ROW($A$1)), 0, 3, 1))

share|improve this answer

If the formula have some patterns (as per to your example =A1+A2, and then =A3+A4), then yes it is possible.

If you put (for example on B1) = A1+A2
and on B2 = A3+A4

highlight both B1 and B2, and then click and drag the bottom right corner of B2 (it should show as a thicker box) and drag and it will follow the pattern you have set previously.

If this is not to your need, you will need to update your questions with more details, and preferably some examples.

share|improve this answer
    
When I do that, it doesn't follow my pattern. If I highlight only B2 and drag it down I get, A1+A2, A2+A3, A3+A4. Then I tried highlighting both B1 & B2 and dragging it down and I get A3+A4, A3+A4, A5+A6, A5+A6, A7+A8, A7+A8.... –  Reanna Nov 14 '13 at 4:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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