formula to change a Negative No to a Positive

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

This may be more of a math question rather than an Excel one.

If I have a spreadsheet with a cell that produces different negative number each time it is used, is there a way i can add a formula to make it a positive number.

So if Cell A1 showed -£244.22, I want to add an equation that changes if to £244.22

Cell A1 changes so it would need to do the same for any negative number within that cell.

Kudos to u all

**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

**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