Thread: how can i subtract single integer value from the entire column

how can i subtract a single integer value from the entire column
say : i wanna subtract 10 from the entire column of numbers

Type 10 in an empty cell; copy this cell
Select the column of data; use Edit|Paste Special->check the Delete box->OK
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"naga s chintala" <naga s chintala@discussions.microsoft.com> wrote in
message news:623D4B9F-426B-4B87-825E-AAD7811287CE@microsoft.com...
> how can i subtract a single integer value from the entire column
> say : i wanna subtract 10 from the entire column of numbers

say for example i have (10,20,30,40,4,2,1,3) in column A .Now i would like to
subtract 5 from all the no.s or from the entire column A.
so which formula am i supposed to use

"naga s chintala" wrote:

> how can i subtract a single integer value from the entire column
> say : i wanna subtract 10 from the entire column of numbers

Naga,

If you have the following in column A -> cells A1:A8,

A
10
20
30
40
4
2
1
3

Add this formula to cell B1 and copy it down the range to cell B8:

=A1-5

The formulas in column B when copied down the range look like this:

A B
10 =A1-5
20 =A2-5
30 =A3-5
40 =A4-5
4 =A5-5
2 =A6-5
1 =A7-5
3 =A8-5

The final output looks like this:

A B
10 5
20 15
30 25
40 35
4 -1
2 -3
1 -4
3 -2


----
Regards,
John Mansfield
http://www.pdbook.com


"naga s chintala" wrote:

> say for example i have (10,20,30,40,4,2,1,3) in column A .Now i would like to
> subtract 5 from all the no.s or from the entire column A.
> so which formula am i supposed to use
>
> "naga s chintala" wrote:
>
> > how can i subtract a single integer value from the entire column
> > say : i wanna subtract 10 from the entire column of numbers

what i meant is instead of entering everytime the formula ie
A1-5,A2-5........can we have a generalised formula which can be applied for
the entire column.
say for example "=(A1:A8)-5" and 5 is subtracted from A1 to A8 .Is there
anything like that

"John Mansfield" wrote:

> Naga,
>
> If you have the following in column A -> cells A1:A8,
>
> A
> 10
> 20
> 30
> 40
> 4
> 2
> 1
> 3
>
> Add this formula to cell B1 and copy it down the range to cell B8:
>
> =A1-5
>
> The formulas in column B when copied down the range look like this:
>
> A B
> 10 =A1-5
> 20 =A2-5
> 30 =A3-5
> 40 =A4-5
> 4 =A5-5
> 2 =A6-5
> 1 =A7-5
> 3 =A8-5
>
> The final output looks like this:
>
> A B
> 10 5
> 20 15
> 30 25
> 40 35
> 4 -1
> 2 -3
> 1 -4
> 3 -2
>
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "naga s chintala" wrote:
>
> > say for example i have (10,20,30,40,4,2,1,3) in column A .Now i would like to
> > subtract 5 from all the no.s or from the entire column A.
> > so which formula am i supposed to use
> >
> > "naga s chintala" wrote:
> >
> > > how can i subtract a single integer value from the entire column
> > > say : i wanna subtract 10 from the entire column of numbers

Naga -

You can either enter a formula into each cell as John suggested, or you can use the
technique Bernard suggested that uses paste special - values to subtract the pasted
value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

naga s chintala wrote:

> what i meant is instead of entering everytime the formula ie
> A1-5,A2-5........can we have a generalised formula which can be applied for
> the entire column.
> say for example "=(A1:A8)-5" and 5 is subtracted from A1 to A8 .Is there
> anything like that
>
> "John Mansfield" wrote:
>
>
>>Naga,
>>
>>If you have the following in column A -> cells A1:A8,
>>
>>A
>>10
>>20
>>30
>>40
>>4
>>2
>>1
>>3
>>
>>Add this formula to cell B1 and copy it down the range to cell B8:
>>
>>=A1-5
>>
>>The formulas in column B when copied down the range look like this:
>>
>>A B
>>10 =A1-5
>>20 =A2-5
>>30 =A3-5
>>40 =A4-5
>>4 =A5-5
>>2 =A6-5
>>1 =A7-5
>>3 =A8-5
>>
>>The final output looks like this:
>>
>>A B
>>10 5
>>20 15
>>30 25
>>40 35
>>4 -1
>>2 -3
>>1 -4
>>3 -2
>>
>>
>>----
>>Regards,
>>John Mansfield
>>http://www.pdbook.com
>>
>>
>>"naga s chintala" wrote:
>>
>>
>>>say for example i have (10,20,30,40,4,2,1,3) in column A .Now i would like to
>>>subtract 5 from all the no.s or from the entire column A.
>>>so which formula am i supposed to use
>>>
>>>"naga s chintala" wrote:
>>>
>>>
>>>>how can i subtract a single integer value from the entire column
>>>>say : i wanna subtract 10 from the entire column of numbers