Rounding & Formula Bar

[Otto Moehrbach]

Excel 2002, WinXP
I am helping an OP and I have run into a problem. The code calculates a
value (number), say 22.666667. The OP wants this number in some cell, but
rounded to one decimal place. He doesn't want the cell display rounded
(formatted), he wants the number itself rounded. No problem. The code
rounds the number and
places the rounded number in the cell and it appears in the cell as 22.7.
But the Formula Bar displays 22.7000007629394 and he wants the formula
bar to display the rounded number.
The following short macro demonstrates the problem.
Sub TestRound()
Dim Num As Single
Num = 22.666667
[A1] = Num
[A2] = Round(Num, 1)
Num = Round(Num, 1)
[A3] = Num
End Sub

The result of running this macro is:
A1 - 22.666667 in the cell, 27.66666679282324 in the formula bar
A2 - 22.7 in the cell, 22.7000007629394 in the formula bar
A3 - same as A2

I have 3 questions:
Why does A1 display 27.66666679282324 in the formula bar when the value of
Num is defined as 22.666667?
Why do A2 & A3 display 22.7000007629394 in the formula bar?
How can I get 22.7 to display in the formula bar for A2 & A3?

I am running out of hair. Thanks for your help. Otto

 

[David C.]

I'm confused as you are because I was sure it was ok...
and after trying it is ok (with my excel 2000)

but, you can change the formula this way:
[A1].formula = num

this will (sure ?) help !
I hope for you

( in fact I prefer to use Range("A1").formula, but... [] also works fine )

 

[Ron Rosenfeld]

Excel 2002, WinXP
I am helping an OP and I have run into a problem. The code calculates a
value (number), say 22.666667. The OP wants this number in some cell, but
rounded to one decimal place. He doesn't want the cell display rounded
(formatted), he wants the number itself rounded. No problem. The code
rounds the number and
places the rounded number in the cell and it appears in the cell as 22.7.
But the Formula Bar displays 22.7000007629394 and he wants the formula
bar to display the rounded number.
The following short macro demonstrates the problem.
Sub TestRound()
Dim Num As Single
Num = 22.666667
[A1] = Num
[A2] = Round(Num, 1)
Num = Round(Num, 1)
[A3] = Num
End Sub

The result of running this macro is:
A1 - 22.666667 in the cell, 27.66666679282324 in the formula bar
A2 - 22.7 in the cell, 22.7000007629394 in the formula bar
A3 - same as A2

I have 3 questions:
Why does A1 display 27.66666679282324 in the formula bar when the value of
Num is defined as 22.666667?
Why do A2 & A3 display 22.7000007629394 in the formula bar?
How can I get 22.7 to display in the formula bar for A2 & A3?

I am running out of hair. Thanks for your help. Otto

I suspect you are running into issues with precision. Try:

Dim Num as Double


--ron

 

[Dave Peterson]

I'm gonna guess it has more to do with the precision of a single and putting it
into a cell (which is treated as a double).

I changed:
Dim Num As Single
to
Dim Num As Double

and it worked ok.

For what it's worth, I don't use singles or integers.

Excel 2002, WinXP
I am helping an OP and I have run into a problem. The code calculates a
value (number), say 22.666667. The OP wants this number in some cell, but
rounded to one decimal place. He doesn't want the cell display rounded
(formatted), he wants the number itself rounded. No problem. The code
rounds the number and
places the rounded number in the cell and it appears in the cell as 22.7.
But the Formula Bar displays 22.7000007629394 and he wants the formula
bar to display the rounded number.
The following short macro demonstrates the problem.
Sub TestRound()
Dim Num As Single
Num = 22.666667
[A1] = Num
[A2] = Round(Num, 1)
Num = Round(Num, 1)
[A3] = Num
End Sub

The result of running this macro is:
A1 - 22.666667 in the cell, 27.66666679282324 in the formula bar
A2 - 22.7 in the cell, 22.7000007629394 in the formula bar
A3 - same as A2

I have 3 questions:
Why does A1 display 27.66666679282324 in the formula bar when the value of
Num is defined as 22.666667?
Why do A2 & A3 display 22.7000007629394 in the formula bar?
How can I get 22.7 to display in the formula bar for A2 & A3?

I am running out of hair. Thanks for your help. Otto

 

[Otto Moehrbach]

Ron, Dave
Thanks for your help. Changing the declaration to a Double did it.
And, Dave, I'm going to take your advise and stay away from Single and
Integer. Thanks again. Otto