Postive # converted to Negative #

2

2MShad

I want to be able to enter a positive # in a cell and have it display as a
negative? Help
 
Z

zxcv

I want to be able to enter a positive # in a cell and have it display as a
negative? Help
Click to expand...

You can create this sub in the sheet (go to Tools->Macro->Visual Basic
Editor and double click the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each T In Target
If IsNumeric(T.Value) Then
If T.Value > 0 Then
T.Value = -T.Value
End If
End If
Next
End Sub
 
G

Gord Dibben

Be aware that the number is still positive.

All you did was change the display, not the value.

Do not rely on these for calculations.


Gord Dibben MS Excel MVP
 
Z

zxcv

Be aware that the number is still positive.

All you did was change the display, not the value.

Do not rely on these for calculations.

Gord Dibben  MS Excel MVP




- Show quoted text -
Click to expand...

How do you figure that it did not change the value?
T.Value = -T.Value

It looks like it changed the value to me and some simple tests confirm
it.
 
G

Gord Dibben

Formatting a number does not change the value of the inputted number.

Enter 1 to 10 in A1:A10

Format as "-"0.0 to display these as negative.

In D1 enter =SUM(A1:A10)

Format D1 as Number or General

I will re-state...............do not rely on the formatted display for
calculations,.


Gord
 
Z

zxcv

Formatting a number does not change the value of the inputted number.

Enter 1 to 10 in A1:A10

Format as  "-"0.0  to display these as negative.

In D1 enter  =SUM(A1:A10)

Format D1 as Number or General

I will re-state...............do not rely on the formatted display for
calculations,.

Gord







- Show quoted text -
Click to expand...

Fair enough. My method however DID change the value. Your method may
be preferable in the OP's case if they did not want the value to
change or if they did not want to have a macro.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automatically Scheduled Task with Start on Constraint and Neg Slack 0
Program buttons to create a word document 0
if negative figure 8
Matching Negative and Positive Values 20
How can I show up and down arrows when a value changes? 1
SUMIF with broken range 10
adding positive and negative numbers 3
making a # value positive or negative based on contents of another 2

Top