How do I get absolute values for a range of cells?

I various spreadsheets with a mixture of positive and negative numbers. I
would like to make them absolute values. I believe a macro would be
necessary.

Hi Terry

i've just tried this on a SMALL data set - so you might like to test it a
bit more thoroughly before believing in it ... but i selected the range i
wanted to convert to positive values and did
edit / replace
find what: -
replace with: <<leave blank>>

clicked Replace All

seemed to work.

Cheers
julieD

"Terry" <[email protected]> wrote in message
news:[email protected]...
>I various spreadsheets with a mixture of positive and negative numbers. I
> would like to make them absolute values. I believe a macro would be
> necessary.

Not really, you could use a help column and a simple formula

=IF(A1<0,A1*-1,A1)

copy down/across and then copy and paste special as values,
now you could replace the originals with the help column

--

Regards,

Peo Sjoblom


"Terry" <[email protected]> wrote in message
news:[email protected]...
> I various spreadsheets with a mixture of positive and negative numbers. I
> would like to make them absolute values. I believe a macro would be
> necessary.

It's also possible by way of the ABS() function, which returns the
absolute value of whatever is between the brackets (cell reference,
etc). Would it be easier to use a formula than write and maintain a
macro?

Hi!

Depends on where your numbers come from and whether you want to change only the display or to change the value.

To change the display you can use a custom format such as ###;###
(The usual thing but without the minus sign).

If you are dealing with constants in the cells, then Peo's approach
will do it.

If you have formulae in the cells, then you can use Peo's line but don't copy and paste. Otherwise, you have to change the formulae.

If the formulae are straightforward and repetitive, you can actually edit them using find/replace. First get rid of = and replace it with Abs (making formula into text). Then put a parenthesis at the end by similar means. Then replace Abs with =Abs.
It worked fine on =sin(A1), for example - an easy one...

Alf