I have a column of numbers, some positive and some negative.

[Skip]

I woiuld like to add three to each number so

EXAMPLE

4 becomes 7 in the next cell
and
-3 becomes -6 in the next cell
Any help appeciated. Thanks.

 

[Ron Rosenfeld]

I woiuld like to add three to each number so

EXAMPLE

4 becomes 7 in the next cell
and
-3 becomes -6 in the next cell
Any help appeciated. Thanks.

B1: =A1+SIGN(A1)*3

 

[Ron Rosenfeld]

I woiuld like to add three to each number so

EXAMPLE

4 becomes 7 in the next cell
and
-3 becomes -6 in the next cell
Any help appeciated. Thanks.

Note that with my previous suggestion, zero will stay as zero.

Your example shows that you want to add three to positive numbers, and subtract three from negative numbers; but you do not define what you want to do with zero.

 

[Skip]

Note that with my previous suggestion, zero will stay as zero.

Your example shows that you want to add three to positive numbers, and subtract three from negative numbers; but you do not define what you want to do with zero.

I would like zero to become 3.
Thnaks for the help.

 

[plinius]

"Skip" <[email protected]> ha scritto nel messaggio

Note that with my previous suggestion, zero will stay as zero.

Your example shows that you want to add three to positive numbers, and
subtract three from negative numbers; but you do not define what you want
to do with zero.

I would like zero to become 3.
Thnaks for the help.


****************

=(ABS(A1)+3)*IF(A1<0,-1,1)

bye, E.

 

[Ron Rosenfeld]

I would like zero to become 3.
Thnaks for the help.

=A1+((A1=0)+SIGN(A1))*3

 

[joeu2004]

=A1+((A1=0)+SIGN(A1))*3

Or simply:

=IF(A1=0,3,A1+3*SIGN(A1))

 

[Rick Rothstein]

=A1+((A1=0)+SIGN(A1))*3

Or simply:

=IF(A1=0,3,A1+3*SIGN(A1))

Or maybe more simply...

=A1+IF(A1<0,-3,3)

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Or maybe more simply...

=A1+IF(A1<0,-3,3)

Not sure if this would qualify as simpler, but it does eliminate all
function calls...

=A1+3-6*(A1<0)

Rick Rothstein (MVP - Excel)

 

[Harlan Grove]

Not sure if this would qualify as simpler, but it does eliminate all
function calls...

=A1+3-6*(A1<0)

At the cost of additional add and multiply operations and a type
conversion (boolean to double). Unlikely this would be much faster.