Negative numbers

  • Thread starter Thread starter Jessie
  • Start date Start date
J

Jessie

Hello,

Does anyone know how to create a formula that will allow
for a begining balance of $0.00 and then take away $20.00
to give you an ending balance of -$20.00. And then have
a begining balance of $-20.00 and take away $20.00 for a
total of -40.00 instead of $0.00. I keep getting a zero.
 
Hi Jessie;

In VBA you can dim the value as an Interger or a Long, do
the math in memory and then write it to a cell. An
example might be

Sub try()

MyValue = Int(activecell) - 20
activecell = MyValue

End Sub

Thanks

Greg
 
Hi Greg,

Since Jessie is clearly dealing with currency calculations, it would not
seem appropriate to dim your variable as either long or integer.

Further, why would you advocate using the Int function?
 
Actually, for a completely different reason, Long might well be a good
way to go.

Longs will avoid many of the problems introduced by small rounding
errors in IEEE double precision floating point math. Values can be
internally multiplied and divided by 100 to match the currency
requirements.


So try() could be rewritten:

Public Sub try()
Dim MyValue As Long
With ActiveCell
MyValue = .Value * 100& - 2000&
.Value = MyValue \ 100&
End With
End Sub

However, I don't think that was the original responder's intention.
 
Hi JE,
Longs will avoid many of the problems introduced by small rounding
errors in IEEE double precision floating point math. Values can be
internally multiplied and divided by 100 to match the currency
requirements.

Excellent and well-made point!
 
Personal preference is to use the "Currency" data type to help avoid the
small rounding issues. Depending of course on what one is doing...
Dana DeLouis
 

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

Back
Top