Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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" On 23 Mar, 19:51, Jim at Eagle wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
Format is fine for appearance. However I want the number to only be 7 digits.
-- Jim at Eagle "Jarek Kujawa" wrote: 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" On 23 Mar, 19:51, Jim at Eagle wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 Cone" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 Cone" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 "Jim Cone" wrote: 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 Cone" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding
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 "Jim at Eagle" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding off to .48 or .98 | Excel Discussion (Misc queries) | |||
Worksheet rounding vs VBA rounding | Excel Programming | |||
Rounding | Excel Programming |