CODEWITHSUNDEEP
excelcopy
user1982244
user1982244

Reputation: 15

Copy formula across columns

I have a spreadsheet with 4 columns : A, B, C, D.

I want to calculate the difference between A-B and C-D. So in cell E2 I enter the formula =A2-B2 , and get the result that I want, which is the difference between A-B.

Now I drag the formula in E2 to cell F2, expecting that Excel will "think" that now I want the difference between C-D. However, when I drag the formula from column E to column F I get =B2-C2 (you see, Excel assumes I want to use B2 again).

My question is: how can I make Excel “understand” that I want to use =C2-D2 after =A2-B2, and NOT =B2-C2?

This an abbreviated example. The original data-set has hundred of columns. Thanks for your help.

Upvotes: 1

Views: 7560

Answers (3)

robinCTS
robinCTS

Reputation: 5886

As your original data-set has hundreds of columns, you may want to use a non-volatile formula, eg:

=INDEX(A1:K1,1+COLUMN(L1)-COLUMN($L1))-INDEX(A1:K1,2+COLUMN(L1)-COLUMN($L1))

This will speed up calculations in the workbook. Depending on how big the data-set is and how many other formulas are in the workbook, the difference in speed could be enormous.

The same conditions apply as in Peter's example.

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17495

There's no way in the UI that you get Excel to "think" that much - it'll only shift the references by one column if you drag it across. If deleting the columns in between is not an option, you need to rewrite you formula, e.g.:

=OFFSET(A1,0,COLUMN(L1)-COLUMN($L1))-OFFSET(B1,0,COLUMN(L1)-COLUMN($L1))

In this example, it is assumed that you start showing the difference in column L - of course you need to adjust according to your worksheet - but once done, you can drag the formula to the right as far as you need.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

  1. Enter Formula in E2
  2. Drag the formula up till G2. Now G2 will have the formula which you want.
  3. Delete Col F. Now Col G will move to Col F, which will have your required formula

Upvotes: 1

Related Questions