AUTOMATICALLY ENTER AND CALCULATE NUMBERS AS NEGATIVES
 

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.

AUTOMATICALLY ENTER AND CALCULATE NUMBERS AS NEGATIVES
 

Hi,
with custom format you will see the numbers as negatives, in your formulas
you will need to treat them as positives for example if you enter 1 in cell
A1 and you format it to be seen as -1, then in B1 you enter =A1 it will show
as 1 (positive) if you want to be shown as negative you have to enter =-A1

"EarthAngel" wrote:

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.

AUTOMATICALLY ENTER AND CALCULATE NUMBERS AS NEGATIVES
 

Formatting is visual only.

Your data are still positive.

You must use the minus sign or event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<<<adjust to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code" Copy/paste the code into that
sheet module.

Edit the range to suit and Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Thu, 27 May 2010 13:22:27 -0700, EarthAngel
wrote:

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.

AUTOMATICALLY ENTER AND CALCULATE NUMBERS AS NEGATIVES
 

Thanks for your response. I figured as much. I wanted to see if there was
something out there that I was unaware of that would allow me to use the sum
function instead of creating a formula. I will now fix my formula at the
bottom of the column to allow for this.

"Eduardo" wrote:

Hi,
with custom format you will see the numbers as negatives, in your formulas
you will need to treat them as positives for example if you enter 1 in cell
A1 and you format it to be seen as -1, then in B1 you enter =A1 it will show
as 1 (positive) if you want to be shown as negative you have to enter =-A1

"EarthAngel" wrote:

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.