Rounding

[Jim at Eagle]

When, with this,
STaxRate = Application.Round(TTax / SubTotal, 7)
While in code the number is rounded to 7 digits but when posted to a cell in
worksheet it's stored at 16 digits.
How can I get my rounded number to a cell and still be rounded?
Thanks

 

[Jarek Kujawa]

try to reformat the cell where STaxRate is stored:

1) by hand, OR

2) name the cell STaxRate and then:

Range("STaxRate").NumberFormat = "0.0000000"

 

[Jim at Eagle]

Format is fine for appearance. However I want the number to only be 7 digits.

 

[Jim Cone]

How are the three variable declared?
What is the value of TTax and SubTotal?
What is the number format of the cell that is receiving the data?
What is the code you use to add the value to the cell?
What version of Excel are you using?

Also, "Subtotal" is not a good name to use for a variable as it is already
used by Excel as a method of the Range object.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jim at Eagle"
wrote in message
When, with this,
STaxRate = Application.Round(TTax / SubTotal, 7)
While in code the number is rounded to 7 digits but when posted to a cell in
worksheet it's stored at 16 digits.
How can I get my rounded number to a cell and still be rounded?
Thanks

 

[Jim at Eagle]

All declared as single
TTax = Application.InputBox(Prompt:="What is the total SALES TAX for this
receipt?", Default:=0, Type:=1)
SubTotal = Application.InputBox(Prompt:=msg2, Title:="Sub-Total
Declaration", Type:=1)

sheet1.Range("A1").value=STaxRate(no format is applied to vaiable or cell)
Excel 2003

 

[Jim Cone]

Must be something else going on ?
The following code produces a cell value that has seven digits to the right
of the decimal point.
'--
Sub DoesItWork()
Dim dTTax As Double
Dim dTaxRate As Double
Dim dSubTotal As Double

dTTax = Application.InputBox(Prompt:= _
"What is the total SALES TAX for this receipt?", Default:=0, Type:=1)
dSubTotal = Application.InputBox(Prompt:= _
"Be Careful Out There", Title:="Sub-Total Declaration ", Type:=1)
dTaxRate = Application.Round(dTTax / dSubTotal, 7)
Worksheets("Sheet1").Range("A1").Value = dTaxRate
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Jim at Eagle"
wrote in message
All declared as single
TTax = Application.InputBox(Prompt:="What is the total SALES TAX for this
receipt?", Default:=0, Type:=1)
SubTotal = Application.InputBox(Prompt:=msg2, Title:="Sub-Total
Declaration", Type:=1)

sheet1.Range("A1").value=STaxRate(no format is applied to vaiable or cell)
Excel 2003

 

[Jim at Eagle]

Jim, The only thing different was in the declaration. I used "Single" you
used "Double". Why that makes a difference, I don't know, but it does.
It's mysteries like this that keeps me awake while driving.
--
Jim at Eagle

Must be something else going on ?
The following code produces a cell value that has seven digits to the right
of the decimal point.
'--
Sub DoesItWork()
Dim dTTax As Double
Dim dTaxRate As Double
Dim dSubTotal As Double

dTTax = Application.InputBox(Prompt:= _
"What is the total SALES TAX for this receipt?", Default:=0, Type:=1)
dSubTotal = Application.InputBox(Prompt:= _
"Be Careful Out There", Title:="Sub-Total Declaration ", Type:=1)
dTaxRate = Application.Round(dTTax / dSubTotal, 7)
Worksheets("Sheet1").Range("A1").Value = dTaxRate
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Jim at Eagle"
wrote in message
All declared as single
TTax = Application.InputBox(Prompt:="What is the total SALES TAX for this
receipt?", Default:=0, Type:=1)
SubTotal = Application.InputBox(Prompt:=msg2, Title:="Sub-Total
Declaration", Type:=1)

sheet1.Range("A1").value=STaxRate(no format is applied to vaiable or cell)
Excel 2003

 

[Jerry W. Lewis]

Most decimal fractions are non-terminating binary fractions that can only be
approximated (just as 1/3 can only be approximated as a decimal fraction).
Single uses less than half of the bits that Double uses for approximating the
value. As a result, a Single value may differ from your intended value in
the 8th significant figure, whereas a Double value should equal your intended
value to at least 15 significant figures.

As documented, Excel will display no more than 15 significant figures, so
you cannot directly see the that an unavoidable approximation took place.
Since STaxRate is declared Single, VBA will display no more than 7
significant figures of its value, again hiding the approximation until you
explicitly convert it to a Double value by one of CDbl(), assignment to a
declared Double variable, or putting the value in an Excel cell (always
Double).

Now that large amounts of memory are readily available, the main
justification for the continued existence of the Single data type is backward
compatibility with legacy code. As a general rule of thumb, you should not
use single unless either:
- The variable will never contain non-integer values (in which case Long
would support a broader range of integers without using any more memory, and
should calculate faster)
- You sufficiently understand the implications of binary approximation to
floating point numbers to be certan that Single will not cause problems such
as this one.

Jerry