# begginer stuff, round to second decimal

#### prikola

##### New Member
is there a simple way to round all numbers to second decimals, i.e I want to
2*22.22=44.44 and not 44.45 (because calculation is based on 10th decimal)

Thanks

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi
Try something like this:
=(Int(({..insert your expression here...})*100))/100
Remove the curly brackets and dots etc.
HTH, Andrew

Another possiblity would be to use the Round() function.

result = round(2*22.22, 2)
the 2 after the comma is the requested number of decimal positions.
Having all 2s in the example is not good. We could go with
result = Round(3*33.33, 2) and the answer would be: 99.99 If you went with the (int((3*33.33)*100))/100, then the answer you get would be 99.98 You will be the one to deside which way you want the problem calculated depending on the answer you need.

Hi Vic
The formula (int((3*33.33)*100))/100 returns 99.99 no t 99.98. It is designed to round down per the original request. The round() method you described rounds up the second decimal place where the third decimal place value exceeds 0.005. The difference is noticeable on calculations such as 3*2.222.
Andrew

Andrew,
I knew it didn't look right. But that is what the immediate window gave me. Here is a direct copy from my immediate window. And, no, I don't understand it.
Code:
``````?(int((3*33.33)*100))/100
99.98
?(int((3*33.33)*1000))/1000
99.989

?3*33.33
? 99.99 *100
? 9999 /100
99.99``````
After looking at the top 4 lines, I did the whole thing one step at a time. By getting the first answer (99.99) then adding the "* 100" after it and then the "/100" with that answer. I have decided there is something wrong with my computer, or at least the immediate window.
Thanks Andrew,

Hi Vic
How bizarre.....where did you do this? If I do my expression in the query SQL screen it returns the correct result. Where is the immediate window you mentioned?
Andrew

While in Access, press ctrl G, and a window pops up that allows you to do most anything, one line at a time, that you could do in code. You can put more than one statement on a line (as you can do in code too) by putting a colon ) between the statements. So, you can even do a For;Next loop.
Such as:
Code:
``For x = 1 to 3: Debug.print x: Next x``
This will produce output to this immediate window like:
Code:
``````1
2
3``````
You can also get the the immediate window via the View menu while in the VBA screen.
Let me know what you think of this window.

Based on bmacr's post, the problem is most likely that Single and Double are binary, and there is inaccuracy when representing decimals as binaries. Try this looping code to see the difference:
Code:
``````Option Explicit
Dim x As Integer
Dim y
Sub RunTest()
Sngl
Doubl
Deciml
Currncy
End Sub

Sub Sngl()
y = 0
For x = 1 To 10000
y = y + CSng(0.0001)
Next x
Debug.Print "Single: " & y
End Sub

Sub Doubl()
y = 0
For x = 1 To 10000
y = y + CDbl(0.0001)
Next x
Debug.Print "Double: " & y
End Sub

Sub Deciml()
y = 0
For x = 1 To 10000
y = y + CDec(0.0001)
Next x
Debug.Print "Decimal: " & y
End Sub

Sub Currncy()
y = 0
For x = 1 To 10000
y = y + CCur(0.0001)
Next x
Debug.Print "Currency: " & y
End Sub``````
Run the RunTest sub. Output in the Debug window:
Code:
``````Single: 1.000054
Double: 0.999999999999906
Decimal: 1
Currency: 1``````
Denis

Denis,
Great post! It's always good to learn something, and this example is a great illustration of the situation.
Thanks,

Replies
2
Views
338
Replies
1
Views
174
Replies
0
Views
541
Replies
1
Views
241
Replies
1
Views
120

1,196,073
Messages
6,013,265
Members
441,758
Latest member
Abren

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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