Formula Help Please!!
 

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!!

Formula Help Please!!
 

=$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!!
|

Formula Help Please!!
 

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!!

Formula Help Please!!
 

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!!

Formula Help Please!!
 

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!!
|

Formula Help Please!!
 

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... ?

Formula Help Please!!
 

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!!
|

Formula Help Please!!
 

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!!
|

Formula Help Please!!
 

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!!
| |
|
|
|

Formula Help Please!!
 

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!!
|