I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

Format the cells with one decimal place, then use Tools/Options/Calculation
tab, and check "Precision as displayed". Click OK to the message about losing
accuracy.
Bob Umlas
Esxcel MVP

"Mike" wrote:

I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

That works, but, is there VB code to activate it? I generate the worksheet
from the VB code.

Mike


"Bob Umlas, Excel MVP" wrote:

Format the cells with one decimal place, then use Tools/Options/Calculation
tab, and check "Precision as displayed". Click OK to the message about losing
accuracy.
Bob Umlas
Esxcel MVP

"Mike" wrote:

I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

Also, what I don't understand is why the extra decimal positions were there
in the first place. The number I enter using the program had already been
rounded to a single decimal point. Why did excel generate the extra
positions?

Mike


"Bob Umlas, Excel MVP" wrote:

Format the cells with one decimal place, then use Tools/Options/Calculation
tab, and check "Precision as displayed". Click OK to the message about losing
accuracy.
Bob Umlas
Esxcel MVP

"Mike" wrote:

I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

Formatting a cell impacts the way that the value is displayed, but does not
change the value itself (unrounded). If you want to calculate with rounded
values, you either have to explicitly round them, or set the Precision as
Displayed option.

When you are unsure how to program an operation, record a macro while you do
it manually and examine the code. From that you would find
ActiveWorkbook.PrecisionAsDisplayed = True

Jerry

"Mike" wrote:

Also, what I don't understand is why the extra decimal positions were there
in the first place. The number I enter using the program had already been
rounded to a single decimal point. Why did excel generate the extra
positions?

Mike


"Bob Umlas, Excel MVP" wrote:

Format the cells with one decimal place, then use Tools/Options/Calculation
tab, and check "Precision as displayed". Click OK to the message about losing
accuracy.
Bob Umlas
Esxcel MVP

"Mike" wrote:

I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

Jerry,

Thanks for the code. I have used the record macro before. I should have
thought of that before posting that question.

I'm still not clear on what is happening in the first place. My program
rounds the number to a single decimal before it ever puts it into the
spreadsheet. So, the spreadsheet shouldn't even see any extra decimal
positions. Also, if you look at the decimal values added, they are
consistant and repeat. It's like excel added them. I've even used MsgBox to
view the value before it is entered into the spreadsheet and it only has one
decimal place.

Any insight into what is happening is appreciated.

Mike


"Jerry W. Lewis" wrote:

Formatting a cell impacts the way that the value is displayed, but does not
change the value itself (unrounded). If you want to calculate with rounded
values, you either have to explicitly round them, or set the Precision as
Displayed option.

When you are unsure how to program an operation, record a macro while you do
it manually and examine the code. From that you would find
ActiveWorkbook.PrecisionAsDisplayed = True

Jerry

"Mike" wrote:

Also, what I don't understand is why the extra decimal positions were there
in the first place. The number I enter using the program had already been
rounded to a single decimal point. Why did excel generate the extra
positions?

Mike


"Bob Umlas, Excel MVP" wrote:

Format the cells with one decimal place, then use Tools/Options/Calculation
tab, and check "Precision as displayed". Click OK to the message about losing
accuracy.
Bob Umlas
Esxcel MVP

"Mike" wrote:

I'm having a problem with rounding that I hope someone can help with. I've
written code in VB to take readings from a piece of test equipment and round
the value to a single decimal point before putting it into a spreadsheet.
The problem is that the actual values in the cells have 13 decimal places.
The cells are formatted as a number with one decimal place and that is how
they are displayed in the cell of the spreadsheet. But, when I click on the
cell and the actual value in shown in the Formula Bar, it shows the value
with 13 decimals places.

Code:
Dim TempStr As Single
TempStr = Round(Mid(ValueStr, 2, 14), 1)
Sheets("Temps").Cells(Rrow, (X * 2)) = TempStr

Results:
Cell Shows in Formula Bar
23.7 23.7000007629394
23.8 23.7999992370605
23.9 23.8999996185302
24.0 24
24.1 24.1000003814697
24.2 24.2000007629394
24.3 24.2999992370605
24.4 24.3999996185302
24.5 24.5
24.6 24.6000003814697
24.7 24.7000007629394
24.8 24.7999992370605
24.9 24.8999996185302
25.0 25

Mike wrote:
I'm still not clear on what is happening in the first place. My program
rounds the number to a single decimal before it ever puts it into the
spreadsheet. So, the spreadsheet shouldn't even see any extra decimal
positions.

There are two problems, one that you can mitigate, the other that you
have no control over.

The latter first. In simplified terms, binary computers approximate
decimal fractions as a sum of powers of 1/2, each one called a bit.
For example, 0.625 is represented by 1*(1/2) + 0*(1/4) + 1*(1/8). But
there is only a limited number of bits in the binary representation.
Most decimal fractions cannot be represented exactly within the fixed
number of bits.

However, the numerical error should be very small. And you might not
always see the numerical error because of heuristic algorithms in Excel
that attempt to hide such errors when it displays values in cells.

In any case, you exacerbate the problem by declaring your VBA variable
as Single instead of Double. In a binary computer, a Single floating
point representation has less than half the number of bits than a
Double floating point representation for what is called the mantissa
(the series of powers of 1/2). Consequently, the Single sum of binary
powers may be truncated when it is stored into a Double, which I
presume Excel uses for numeric cell values. For example, format cells
as Scientific with 14 decimal places and compare the results of the
following two functions (see the VBA below):

=singleval(23.7) (2.37000007629394E+01)
=doubleval(23.7) (2.37000000000000E+01)

Bottom line: You should use type Double in your VBA program to
minimize the numerical error due to the binary representation.

-----
VBA examples:

Function singleval(foo As Single) As Double
singleval = foo
End Function

Function doubleval(foo As Double) As Double
doubleval = foo
End Function

Thanks for the explanation. The previous instructions on how to fix the
problem were very helpful, but I always like to know why something is
happening the way it is as well.

Mike


" wrote:

Mike wrote:
I'm still not clear on what is happening in the first place. My program
rounds the number to a single decimal before it ever puts it into the
spreadsheet. So, the spreadsheet shouldn't even see any extra decimal
positions.

There are two problems, one that you can mitigate, the other that you
have no control over.

The latter first. In simplified terms, binary computers approximate
decimal fractions as a sum of powers of 1/2, each one called a bit.
For example, 0.625 is represented by 1*(1/2) + 0*(1/4) + 1*(1/8). But
there is only a limited number of bits in the binary representation.
Most decimal fractions cannot be represented exactly within the fixed
number of bits.

However, the numerical error should be very small. And you might not
always see the numerical error because of heuristic algorithms in Excel
that attempt to hide such errors when it displays values in cells.

In any case, you exacerbate the problem by declaring your VBA variable
as Single instead of Double. In a binary computer, a Single floating
point representation has less than half the number of bits than a
Double floating point representation for what is called the mantissa
(the series of powers of 1/2). Consequently, the Single sum of binary
powers may be truncated when it is stored into a Double, which I
presume Excel uses for numeric cell values. For example, format cells
as Scientific with 14 decimal places and compare the results of the
following two functions (see the VBA below):

=singleval(23.7) (2.37000007629394E+01)
=doubleval(23.7) (2.37000000000000E+01)

Bottom line: You should use type Double in your VBA program to
minimize the numerical error due to the binary representation.

-----
VBA examples:

Function singleval(foo As Single) As Double
singleval = foo
End Function

Function doubleval(foo As Double) As Double
doubleval = foo
End Function

Mike wrote:
Thanks for the explanation.

Glad to hear it helped. Just to clarify....

I wrote:
Consequently, the Single sum of binary
powers may be truncated when it is stored into a Double

I probably should have said: the sum of binary powers may be truncated
when stored into a Single, and that truncation is carried forward when
the Single is stored into Double, which (I presume) is how Excel stores
numeric cell values.