Rounding decimals in a math operation

%3CLINGO-SUB%20id%3D%22lingo-sub-2542288%22%20slang%3D%22en-US%22%3ERounding%20decimals%20in%20a%20math%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2542288%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3CBR%20%2F%3EWhen%20performing%20a%20arithmetic%20operation%20in%20an%20Access%20field%2C%20such%20as%3CBR%20%2F%3E((5*5*4%2F3)%2B2*5%2B2*4)%2F20%3D2.56%3CBR%20%2F%3EI%20want%20a%20code%20that%20rounds%20this%20result%20to%203%3CBR%20%2F%3Emeaning%20nearest%20and%20highest%20integer%3CBR%20%2F%3Ethank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2542288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2543402%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20decimals%20in%20a%20math%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2543402%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20e.g.%20use%20the%20%3CSTRONG%3EFormat%3C%2FSTRONG%3E%20function%20like%20this%3A%20%3CSTRONG%3EFormat%3C%2FSTRONG%3E(Whatever%2C%20%22%23%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20also%20many%20user%20defined%20Rounding%20functions%20like%20%3CA%20href%3D%22https%3A%2F%2Fwww.donkarl.com%3FFAQ2.1%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ethe%20one%20on%20my%20website%3C%2FA%3E%20(in%20German%2C%20but%20the%20code%20works%20for%20other%20languages%20too%20%3B-).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%22-1%22%3E%3CFONT%20face%3D%22Verdana%22%3EServus%3CBR%20%2F%3EKarl%3CBR%20%2F%3E*********%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2FAccessDevCon.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttp%3A%2F%2FAccessDevCon.com%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.donkarl.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.donkarl.com%3C%2FA%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2544135%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20decimals%20in%20a%20math%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2544135%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20Karl%20suggested%20there%20are%20many%20ways%20of%20doing%20this%2C%20jncludjng%20formatting.%3C%2FP%3E%3CP%3EOther%20methods%20using%20built%20in%20functions%20include%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%3FCInt(2.56)%0A%203%20%0A%3FRound(2.56%2C0)%0A%203%20%0A%3FInt(2.56)%2B1%0A%202%20%0A%3FFix(2.56)%2B1%0A%203%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EInt%20and%20Fix%20both%20give%20the%20integer%20value%20so%20you%20need%20to%20add%201%20to%20the%20result%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20what%20if%20the%20result%20is%20nearer%20to%20the%20lower%20integer%20value%20e.g.%202%3C%2FP%3E%3CP%3EBoth%20CInt%20and%20Round%20will%20now%20round%20down%20so%20you%20would%20need%20to%20add%201.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20avoid%20those%20as%20not%20consistent.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20consider%20negative%20results%20such%20as%20-2.56%20or%20-2.16%3C%2FP%3E%3CP%3EAssuming%20you%20want%20the%20result%20to%20be%20-2%20in%20each%20case%2C%20then%20the%20only%20function%20that%20always%20gives%20the%20desired%20answer%20in%20all%20scenarios%20is%20using%20Int(number)%2B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20makes%20sense%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello
When performing a arithmetic operation in an Access field, such as
((5*5*4/3)+2*5+2*4)/20=2.56
I want a code that rounds this result to 3
meaning nearest and highest integer
thank you

4 Replies

Hi,

 

You can e.g. use the Format function like this: Format(Whatever, "#")

 

There are also many user defined Rounding functions like the one on my website (in German, but the code works for other languages too ;-).

 

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

As Karl suggested there are many ways of doing this, jncludjng formatting.

Other methods using built in functions include

?CInt(2.56)
 3 
?Round(2.56,0)
 3 
?Int(2.56)+1
 2 
?Fix(2.56)+1
 3 

Int and Fix both give the integer value so you need to add 1 to the result

 

But what if the result is nearer to the lower integer value e.g. 2

Both CInt and Round will now round down so you would need to add 1. 

So avoid those as not consistent.

 

Now consider negative results such as -2.56 or -2.16

Assuming you want the result to be -2 in each case, then the only function that always gives the desired answer in all scenarios is using Int(number)+1

 

Hope that makes sense

@Morya_ali_asiri 

... as the nearest integer could be lower, I guess you mean "nearest higher integer".

That is rounding up which is easy to perform:

ValueUp = -Int(-(5*5*4/3+2*5+2*4)/20)
' ValueUp will be 3.

 For serious rounding of any values with extreme precision, go to VBA.Round .

Interesting. So both of these expressions round up to the next higher integer for all positive and negative numbers
Int(YourNumber)+1
-Int(-YourNumber)

However if the result is itself an integer, adding 1 will give the wrong result.
So I agree that that using Gustav's method is better.

-