Discussion Re: Rounding decimals in a math operation in Access
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2545793#M3330
Interesting. So both of these expressions round up to the next higher integer for all positive and negative numbers<BR />Int(YourNumber)+1<BR />-Int(-YourNumber)<BR /><BR />However if the result is itself an integer, adding 1 will give the wrong result.<BR />So I agree that that using Gustav's method is better.<BR /><BR />-Wed, 14 Jul 2021 07:45:27 GMTisladogs2021-07-14T07:45:27ZRounding decimals in a math operation
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2542288#M3321
<P>Hello<BR />When performing a arithmetic operation in an Access field, such as<BR />((5*5*4/3)+2*5+2*4)/20=2.56<BR />I want a code that rounds this result to 3<BR />meaning nearest and highest integer<BR />thank you</P>Tue, 13 Jul 2021 11:39:34 GMThttps://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2542288#M3321Morya_ali_asiri2021-07-13T11:39:34ZRe: Rounding decimals in a math operation
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2543402#M3322
<P>Hi,</P>
<P> </P>
<P>You can e.g. use the <STRONG>Format</STRONG> function like this: <STRONG>Format</STRONG>(Whatever, "#")</P>
<P> </P>
<P>There are also many user defined Rounding functions like <A href="https://www.donkarl.com?FAQ2.1" target="_self">the one on my website</A> (in German, but the code works for other languages too ;-).</P>
<P> </P>
<P><FONT size="-1"><FONT face="Verdana">Servus<BR />Karl<BR />*********<BR /><A href="http://AccessDevCon.com" target="_blank" rel="noopener">http://AccessDevCon.com</A><BR /><A href="https://www.donkarl.com" target="_blank" rel="noopener">https://www.donkarl.com</A></FONT></FONT></P>Tue, 13 Jul 2021 15:59:55 GMThttps://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2543402#M3322Karl Donaubauer2021-07-13T15:59:55ZRe: Rounding decimals in a math operation
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2544135#M3325
<P>As Karl suggested there are many ways of doing this, jncludjng formatting.</P><P>Other methods using built in functions include</P><LI-CODE lang="visual">?CInt(2.56)
3
?Round(2.56,0)
3
?Int(2.56)+1
2
?Fix(2.56)+1
3 </LI-CODE><P>Int and Fix both give the integer value so you need to add 1 to the result</P><P> </P><P>But what if the result is nearer to the lower integer value e.g. 2</P><P>Both CInt and Round will now round down so you would need to add 1. </P><P>So avoid those as not consistent.</P><P> </P><P>Now consider negative results such as -2.56 or -2.16</P><P>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</P><P> </P><P>Hope that makes sense</P>Tue, 13 Jul 2021 18:18:41 GMThttps://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2544135#M3325isladogs2021-07-13T18:18:41ZRe: Rounding decimals in a math operation
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2545721#M3329
<P><LI-USER uid="1102207"></LI-USER> </P><P>... as the <EM>nearest integer</EM> could be lower, I guess you mean "nearest higher integer".</P><P>That is <EM>rounding up</EM> which is easy to perform:</P><LI-CODE lang="visual">ValueUp = -Int(-(5*5*4/3+2*5+2*4)/20)
' ValueUp will be 3.</LI-CODE><P> For serious rounding of any values with extreme precision, go to <A title="VBA.Round" href="https://github.com/GustavBrock/VBA.Round" target="_blank" rel="noopener">VBA.Round</A> .</P>Wed, 14 Jul 2021 07:12:55 GMThttps://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2545721#M3329Gustav Brock2021-07-14T07:12:55ZRe: Rounding decimals in a math operation
https://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2545793#M3330
Interesting. So both of these expressions round up to the next higher integer for all positive and negative numbers<BR />Int(YourNumber)+1<BR />-Int(-YourNumber)<BR /><BR />However if the result is itself an integer, adding 1 will give the wrong result.<BR />So I agree that that using Gustav's method is better.<BR /><BR />-Wed, 14 Jul 2021 07:45:27 GMThttps://techcommunity.microsoft.com/t5/access/rounding-decimals-in-a-math-operation/m-p/2545793#M3330isladogs2021-07-14T07:45:27Z