REVERSING SIGNS OF NUMBERS

[Guest]

Can anyone help me out in revealing a way that all the positive numbers in a
worksheet would convert to negative ones and negative ones to positive ones,
without multiplying with -1.

Since I further need to calculate upon the same I want to change not only
the format but also the true nature. In other words some way other than
choosing a custom format.

If no other simple way, macros are welcome.

 

[ed]

Can anyone help me out in revealing a way that all the positive numbers in a
worksheet would convert to negative ones and negative ones to positive ones,
without multiplying with -1.

Since I further need to calculate upon the same I want to change not only
the format but also the true nature. In other words some way other than
choosing a custom format.

If no other simple way, macros are welcome.

To change the - to + try the Edit menu Find / Replace. Then put - in
the Replace window and a + in the With window, Then hit Replace
All. I don't think there's a way to reverse a positive number to
negative unless there's a + in the formula (just reverse the above to
convert all the + to - . Then you only have the bare numbers that
have no + or - to deal with. You'd think that If the "bare numbers"
have something in common (like starting with 5) put 5** in the Replace
window, but that changes the entiree number and you don't know what to
replace it with. The problem with a bare number is there's only a
space to find and you don't want to change every space in your
worksheet to -.

ed

 

[Bob Phillips]

Why without multiplying by -1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Well if you can't change +ve to -ve I think the suggestion shall be useless.
Can't u refer a MACRO like "on worksheet change" etc?

 

[MartinW]

Hi Faraz,

You say that you don't want to multiply by -1.
Is that because you think it would be a slow or awkward solution?

It's a very slick operation if you do it like this.
Put -1 into any blank cell.
Copy the cell.
Select all of your data.
Right click and select Paste Special.
Check the Multiply radio button and OK out.

All done nice and neat and very fast.
Or is there some other reason that you haven't explained yet?

HTH
Martin

 

[Guest]

To convert negative number to positive =abs()
I dont know if there is a function to convert positive to negative

 

[Pete_UK]

The SIGN function returns 1 for +ve numbers, 0 for zero and -1 for -ve
numbers. So, you could make use of this to construct a formula to
convert the numbers, but Martin's solution is the better way to
approach this.

Pete

 

[ed]

Why without multiplying by -1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Your link doesn't work so i don't know what you said in it, but I
suggest you use Martin's method which is the way to go while we were
all trying to work around your no multiplying by -1 inhibitions. It
will work both ways, + to - and - to
+, is simple, and no macros! Did you think you had to multiply each
individual cell by -1?. Paste Special / Multiply does it for you.
It isn'g a matter of changing +ve to -ve its changing ve to -ve. See
my previous post of why. Now if all the positive number are preceeded
by a + that'll work, but Excel assumes a blank is positive and rejects
it as unnecessary unless it is to denote addition of two numbers.


ed

 

[David Biddulph]

Multiply by -2 and then divide by 2?

 

[Dallman Ross]

You say that you don't want to multiply by -1. Is that because
you think it would be a slow or awkward solution?

I don't know either why not, but I can conjecture. I suspect
he wants values in the range and not a formula. Of course,
there are lots of ways to multiply by -1 and then replace
the formulas with values, though.

(One I use frequently is to copy and paste in place, then choose
from the little pop-up helper paste-options box "values only".
By the way, how come that little helper gizmo doesn't appear
occasionally [when I most want it to!]? I'm in Excel 2002.)

 

[David Biddulph]

To multiply the values doesn't need a formula and then Paste Special/
Values; you can use Paste Special/ Multiply.
--
David Biddulph

You say that you don't want to multiply by -1. Is that because
you think it would be a slow or awkward solution?

I don't know either why not, but I can conjecture. I suspect
he wants values in the range and not a formula. Of course,
there are lots of ways to multiply by -1 and then replace
the formulas with values, though.

(One I use frequently is to copy and paste in place, then choose
from the little pop-up helper paste-options box "values only".
By the way, how come that little helper gizmo doesn't appear
occasionally [when I most want it to!]? I'm in Excel 2002.)

 

[Earl Kiosterud]

David,

LOL. Good workaround. Thanks for the grin!

 

[Dallman Ross]

To multiply the values doesn't need a formula and then Paste
Special/ Values; you can use Paste Special/ Multiply.

How does that work, by the way? I've seen that menu item but
never used it. I just tried it on a cell, copying a value
with Ctrl-C and pasting it in place with my right mouse button
and selecting Paste Special. If I choose "Multiply," the
value gets squared. I don't see how to multiply the value
by a number of my choosing (e.g., -1). I'm sure this is
simple, but it's eluding me now that you got me to try it out.

 

[Pete_UK]

Have a look at Martin W's reply above. You enter -1 into a blank cell
somewhere then <copy> that cell. Then move cursor to highlight the
range that you want this to apply to and then do Edit | Paste Special
| Multiply - every cell in the range gets multiplied by -1. A similar
use is if you have a list of prices and you want to increase all of
them by 3% - just multiply by 1.03 from another cell.

Hope this helps.

Pete

 

[Dallman Ross]

Have a look at Martin W's reply above. You enter -1 into a blank cell
somewhere then <copy> that cell. Then move cursor to highlight the
range that you want this to apply to and then do Edit | Paste Special

Ah. Okay, it was, indeed, simple. Thanks, Pete.