If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|