Single vs double and rounding

sark666

Board Regular
Even though I've been using vba for a while, I've been bad in not declaring my variables which I've been trying to change.

So this code has me puzzled:

Code:
``````Sub test()
Dim res_rate As Single
Dim unique_issues As Integer
unique_issues = 6

res_rate = Round(unique_issues * 100 / headcount, 1)
Range("A1") = res_rate
Debug.Print res_rate
End Sub``````

I thought I would declare res_rate as a single as double takes more memory and I don't need that precision.

Once I round res_rate I would think regardless if it was a double or single it's value would be permanently changed to one decimal place.</SPAN>

However the above code gives A1 a value of 0.300000011920929 but the debug print prints what I would expect: 0.3

Range("A1") is formatted as general.

When I change the above code and declare res_rate as double then I get what I would expect 0.3 in the debugger and cell A1.

So what am I not seeing? How come after rounding somehow res_rate is retaining the complete decimal value??</SPAN>

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
All numbers on a worksheet are Doubles. The Double representation of 0.3 is hex 3FD3333333333333

The Single representation of 0.3 is 3E99999A, which, converted to a Double, is 3FD3333340000000, which is obviously greater than 0.3

Last edited:
More simply,

Code:
``Debug.Print CDbl(0.3!)``

... illustrates the issue.

More simply,

Code:
``Debug.Print CDbl(0.3!)``

... illustrates the issue.

Thanks for the response. I wasn't aware of this. I'm not sure I fully understand but I kind of get it is because of hexidecimal conversion. Hmm, this makes me weary though of ever using a single declaration.

Would always using a double dodge this issue? I guess I could also format the cell to 1 decimal place but again, I thought rounding would permanently change the value to 1 decimal place whereas formatting doesn't actually change a value, but rather how it's displayed.

The reason is because the IEEE floating point format uses base 2 decimals, and many base 10 decimals (e.g., 0.1) are repeating decimals in base 2. It's just something you need to be aware of and program around.

Replies
3
Views
403
Replies
3
Views
212
Replies
6
Views
167
Replies
3
Views
210
Replies
16
Views
283

1,207,401
Messages
6,078,261
Members
446,324
Latest member
JKamlet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back