Andy T
Lv 4
Andy T asked in Computers & InternetSoftware · 6 years ago

How can you drag the formula in an Excel cell & keep the cells (in the formula) logically grouped?

It's hard to describe in just a question, so let me explain:

I have a collection of sets of columns - 3 columns (let's just say 1,2, & 3) with a merged header of the year (2014, 2013, 2012, etc). At the end of this I have a column for each year & the formula in question -> (1+2)/3.

If the 2014 1,2,&3 cells were in A3, B3, & C3 I would type the formula as (A3+B3)/C3. If I drag this formula to each of the year-columns, the next cell would have a formula of (B3+C3)/D3 - obviously NOT what I am going for (which would be (D3+E3)/F3).

Is there a way (such as changing the formula, adding "$" somewhere, etc), to make it so that when I drag the formula into the next cell(s), it goes onto using the NEXT group of 3 & not include any of the previous ones. ex: (A3+B3)/C3 then (D3+E3)/F3 then (G3+H3)/I3, etc etc

2 Answers

  • 6 years ago

    Probably easier handled using VBA.

    I infer that this is a group of columns to the right of all other columns. Which columns hold your 'year' values to divide?

    How many groups of years (three column sets) do you have?

  • 6 years ago

    not in excel. you would have to modify specifically for each year, but then after that, you can drag down using that method, to get a4+b4/c4, a5+b5/c5....etc.

Still have questions? Get your answers by asking now.