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. My question is: how can I make Excel “understand” that I want to use =C2-D2 after =A2-B2, and NOT =B2-C2?

On Thursday, January 17, 2013 2:25:40 PM UTC-5, Ricardo wrote:
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.. My question is: how can I make Excel “understand” that I want to use =C2-D2 after =A2-B2, and NOT =B2-C2?

BTW...This is an example...the real dataset has hundreds of columns. Hopefully there's a better way to do it than manually.

Ricardo used his keyboard to write :
On Thursday, January 17, 2013 2:25:40 PM UTC-5, Ricardo wrote:
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. My
question is: how can I make Excel €œunderstand€� that I want to use =C2-D2
after =A2-B2, and NOT =B2-C2?

BTW...This is an example...the real dataset has hundreds of columns.
Hopefully there's a better way to do it than manually.

Your formula uses Relative Refs to cols/rows and so moving the formula
1 col to the right shifts the refs 1 column to the right. Can you enter
the formula manually in E/F and copy as pairs to other columns?

Note that the refs will auto-adjust and so the layout must be
consistant:

=A2-B2 (E2)
=C2-D2 (F2)

Copy to K/L to do the next set:

=G2-H2 (will be the resulting formula in K2)
=I2-J2 (will be the resulting formula in L2)

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion