begginer stuff, round to second decimal

prikola

New Member
Joined
Feb 14, 2006
Messages
14
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi
Try something like this:
=(Int(({..insert your expression here...})*100))/100
Remove the curly brackets and dots etc.
HTH, Andrew
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Denis,
Great post! It's always good to learn something, and this example is a great illustration of the situation.
Thanks,
 
Upvote 0

Forum statistics

Threads
1,223,195
Messages
6,170,660
Members
452,344
Latest member
LarryRSch

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?

Disable AdBlock

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
Back
Top