formula to change a Negative No to a Positive

**Maistrye** · 12-26-2006, 12:22 PM

Generally, you'd use the function ABS()

If A1 is itself a formula, you simply have to put the ABS() around the value, ie.
A1: =ABS(A5*A6) [as an example]

If A1 is an entry cell, you have four choices as I see it:
1) Put this formula in B1: =ABS(A1) and then use B1 in your calculations.
2) Put ABS(A1) in every formula where you use A1.
3) Put Data -> Validation on the cell, and restrict input to positive values.
4) Learn how to use VBA/Macros with Worksheet Events, so that if a negative value is entered, you automatically change it to a positive one. (Probably the most difficult of these suggestions.)

Scott

**baggy098** · 12-26-2006, 12:26 PM

Thank you for the quick reply, I shall try out your suggestions.

**baggy098** · 12-26-2006, 03:09 PM

by the way

Is there a function that will do the opposite (change a positive number to a negative)

THanks

**MSP77079** · 12-26-2006, 03:12 PM

= - ABS(whatever)

**Flintstone** · 12-26-2006, 03:14 PM

Try this:

=IF(A1<0,ABS(A1),-ABS(A1))

Matt

**daddylonglegs** · 12-26-2006, 03:22 PM

Originally Posted by Flintstone

Try this:

=IF(A1<0,ABS(A1),-ABS(A1))

Matt

I think this a little more than you'd need - you can do the same with

=-A1

**baggy098** · 12-26-2006, 06:32 PM

Thank you one & all

Kudos

**VBA Noob** · 12-26-2006, 06:35 PM

Who's Kudos...didn't see his post

No problem. Glad you got a solution to your problem

VBA Noob

**VBA Noob** · 12-26-2006, 03:15 PM

Hi,

Try

type -1 in an unused cell somewhere and copy it
then select all the numbers you need to convert and then choose
edit / paste special - MULTIPLY

Or

The simplest method would be to insert a column, and for each entry
write a formula: =0-A1 (where A1 is the positive number- replace A1
with your actual cell reference). Then copy the inserted column,
highlight your original column, and Paste As values. Then delete the
column you inserted.

VBA Noob

**VBA Noob** · 12-26-2006, 03:19 PM

or with some VBA

Link shows you how to add code

http://www.mcgimpsey.com/excel/modules.html

Sub Change_to_Minus()
Dim rcel As Range
Dim Rng As Range
Set Rng = Range(ActiveCell, Cells(Rows.Count, _
          ActiveCell.Column).End(xlUp))
    For Each rcel In Rng
    If rcel.Value <> "" And rcel.Value >= 0 Then
    rcel.Value = rcel.Value * -1
End If
Next rcel
End Sub

VBA Noob

formula to change a Negative No to a Positive

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Cheers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions