Hello-

I have a bunch of data in excel right now, and I need help writing a formula
that will automatically do subtractions.

Here is an example to help explain what I mean...

In column A I have 400.00. In column B I have 60.00, which means I want
column C to be 340.00. Now, Column A is constant at 400.00, but the data in
column B changes with each row. So I was wondering, is there a formula I can
write in the C column that will automatically change to whatever row I am in.

Example: the formula in row 1 is =a1-b1

Now if I were to copy and paste into the next say, 20 rows I want the
formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....

Does this make sense to anyone? If not, I would appreciate it if someone
could help me out, I could even send the excel doc. to your email. Any
information would be greatly appreciated! Thank you!!

=$A$1-B1

and copy down.

The dollar cell references will not adjust, the other ones will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
| Hello-
|
| I have a bunch of data in excel right now, and I need help writing a formula
| that will automatically do subtractions.
|
| Here is an example to help explain what I mean...
|
| In column A I have 400.00. In column B I have 60.00, which means I want
| column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| column B changes with each row. So I was wondering, is there a formula I can
| write in the C column that will automatically change to whatever row I am in.
|
| Example: the formula in row 1 is =a1-b1
|
| Now if I were to copy and paste into the next say, 20 rows I want the
| formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
|
| Does this make sense to anyone? If not, I would appreciate it if someone
| could help me out, I could even send the excel doc. to your email. Any
| information would be greatly appreciated! Thank you!!
|

Hi,

You can use the $ sign to make the cell absolute:

=$A$1-B1
copy down

Cheers!
Jean-Guy

"tstokes6887" wrote:

Hello-

I have a bunch of data in excel right now, and I need help writing a formula
that will automatically do subtractions.

Here is an example to help explain what I mean...

In column A I have 400.00. In column B I have 60.00, which means I want
column C to be 340.00. Now, Column A is constant at 400.00, but the data in
column B changes with each row. So I was wondering, is there a formula I can
write in the C column that will automatically change to whatever row I am in.

Example: the formula in row 1 is =a1-b1

Now if I were to copy and paste into the next say, 20 rows I want the
formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....

Does this make sense to anyone? If not, I would appreciate it if someone
could help me out, I could even send the excel doc. to your email. Any
information would be greatly appreciated! Thank you!!

Hi,

You might want to use an IF function so that if a cell in column B is empty
then the subtraction won't take place.

=IF(B1="","",$A$1-B1)
the double quotes in the formula represent a blank or empty cell, so if say
B1 is empty then do nothing else $A$1-B1

Hope this helps!
Jean-Guy

"tstokes6887" wrote:

Hello-

I have a bunch of data in excel right now, and I need help writing a formula
that will automatically do subtractions.

Here is an example to help explain what I mean...

In column A I have 400.00. In column B I have 60.00, which means I want
column C to be 340.00. Now, Column A is constant at 400.00, but the data in
column B changes with each row. So I was wondering, is there a formula I can
write in the C column that will automatically change to whatever row I am in.

Example: the formula in row 1 is =a1-b1

Now if I were to copy and paste into the next say, 20 rows I want the
formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....

Does this make sense to anyone? If not, I would appreciate it if someone
could help me out, I could even send the excel doc. to your email. Any
information would be greatly appreciated! Thank you!!

I write =$A$1-B1 for the first row, all I do now is copy and paste that
formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?





"Niek Otten" wrote:

=$A$1-B1

and copy down.

The dollar cell references will not adjust, the other ones will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
| Hello-
|
| I have a bunch of data in excel right now, and I need help writing a formula
| that will automatically do subtractions.
|
| Here is an example to help explain what I mean...
|
| In column A I have 400.00. In column B I have 60.00, which means I want
| column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| column B changes with each row. So I was wondering, is there a formula I can
| write in the C column that will automatically change to whatever row I am in.
|
| Example: the formula in row 1 is =a1-b1
|
| Now if I were to copy and paste into the next say, 20 rows I want the
| formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
|
| Does this make sense to anyone? If not, I would appreciate it if someone
| could help me out, I could even send the excel doc. to your email. Any
| information would be greatly appreciated! Thank you!!
|

I write =$A$1-B1 for the first row, all I do now is copy and paste that
formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?

Hi,

Yes, unless you want to subtact the total of column B, then use
=$A$1-SUM($B$1:B1) copy down, you don't have to copy and paste, just grab
the small rectangle on the lower right corner of the cell and drag down.

HTH
Jean-Guy

"tstokes6887" wrote:

I write =$A$1-B1 for the first row, all I do now is copy and paste that
formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?





"Niek Otten" wrote:

=$A$1-B1

and copy down.

The dollar cell references will not adjust, the other ones will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
| Hello-
|
| I have a bunch of data in excel right now, and I need help writing a formula
| that will automatically do subtractions.
|
| Here is an example to help explain what I mean...
|
| In column A I have 400.00. In column B I have 60.00, which means I want
| column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| column B changes with each row. So I was wondering, is there a formula I can
| write in the C column that will automatically change to whatever row I am in.
|
| Example: the formula in row 1 is =a1-b1
|
| Now if I were to copy and paste into the next say, 20 rows I want the
| formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
|
| Does this make sense to anyone? If not, I would appreciate it if someone
| could help me out, I could even send the excel doc. to your email. Any
| information would be greatly appreciated! Thank you!!
|

You are awesome! If I could, I would give you a hug right now! Thanks again
so much, you just saved me about 700 subtractions I would have had to do
manually!

"pinmaster" wrote:

Hi,

Yes, unless you want to subtact the total of column B, then use
=$A$1-SUM($B$1:B1) copy down, you don't have to copy and paste, just grab
the small rectangle on the lower right corner of the cell and drag down.

HTH
Jean-Guy

"tstokes6887" wrote:

I write =$A$1-B1 for the first row, all I do now is copy and paste that
formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?





"Niek Otten" wrote:

=$A$1-B1

and copy down.

The dollar cell references will not adjust, the other ones will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
| Hello-
|
| I have a bunch of data in excel right now, and I need help writing a formula
| that will automatically do subtractions.
|
| Here is an example to help explain what I mean...
|
| In column A I have 400.00. In column B I have 60.00, which means I want
| column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| column B changes with each row. So I was wondering, is there a formula I can
| write in the C column that will automatically change to whatever row I am in.
|
| Example: the formula in row 1 is =a1-b1
|
| Now if I were to copy and paste into the next say, 20 rows I want the
| formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
|
| Does this make sense to anyone? If not, I would appreciate it if someone
| could help me out, I could even send the excel doc. to your email. Any
| information would be greatly appreciated! Thank you!!
|

Yes.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
|I write =$A$1-B1 for the first row, all I do now is copy and paste that
| formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?
|
|
|
|
|
| "Niek Otten" wrote:
|
| =$A$1-B1
|
| and copy down.
|
| The dollar cell references will not adjust, the other ones will
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "tstokes6887" wrote in message
...
| | Hello-
| |
| | I have a bunch of data in excel right now, and I need help writing a formula
| | that will automatically do subtractions.
| |
| | Here is an example to help explain what I mean...
| |
| | In column A I have 400.00. In column B I have 60.00, which means I want
| | column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| | column B changes with each row. So I was wondering, is there a formula I can
| | write in the C column that will automatically change to whatever row I am in.
| |
| | Example: the formula in row 1 is =a1-b1
| |
| | Now if I were to copy and paste into the next say, 20 rows I want the
| | formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
| |
| | Does this make sense to anyone? If not, I would appreciate it if someone
| | could help me out, I could even send the excel doc. to your email. Any
| | information would be greatly appreciated! Thank you!!
| |
|
|
|

You're welcome, thanks for the feedback it's always appreciated!

Cheers!
Jean-Guy

"tstokes6887" wrote:

You are awesome! If I could, I would give you a hug right now! Thanks again
so much, you just saved me about 700 subtractions I would have had to do
manually!

"pinmaster" wrote:

Hi,

Yes, unless you want to subtact the total of column B, then use
=$A$1-SUM($B$1:B1) copy down, you don't have to copy and paste, just grab
the small rectangle on the lower right corner of the cell and drag down.

HTH
Jean-Guy

"tstokes6887" wrote:

I write =$A$1-B1 for the first row, all I do now is copy and paste that
formula for the next rows and B will change from B1, to B2, B3, B4, etc... ?





"Niek Otten" wrote:

=$A$1-B1

and copy down.

The dollar cell references will not adjust, the other ones will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"tstokes6887" wrote in message ...
| Hello-
|
| I have a bunch of data in excel right now, and I need help writing a formula
| that will automatically do subtractions.
|
| Here is an example to help explain what I mean...
|
| In column A I have 400.00. In column B I have 60.00, which means I want
| column C to be 340.00. Now, Column A is constant at 400.00, but the data in
| column B changes with each row. So I was wondering, is there a formula I can
| write in the C column that will automatically change to whatever row I am in.
|
| Example: the formula in row 1 is =a1-b1
|
| Now if I were to copy and paste into the next say, 20 rows I want the
| formula to automatically change to =a1-b2, =a1-b3, =a1-b4, ect.....
|
| Does this make sense to anyone? If not, I would appreciate it if someone
| could help me out, I could even send the excel doc. to your email. Any
| information would be greatly appreciated! Thank you!!
|