Rounding to 2 dp - odd results

# Thread: Rounding to 2 dp - odd results

1. Member
Join Date
Dec 1969
Posts
82

## Rounding to 2 dp - odd results

Hi<BR>I&#039;m working with some currency values and need to round the nearest 0.01. The Round(varX, 2) produces results that I was not expecting.<BR><BR>e.g. Round(0.015, 2) gives 0.02 - great that&#039;s what I want.<BR><BR>However Round(0.005) gives 0 not the 0.01 I had expected and indeed want.<BR><BR>Tracing back through the VBScript manual this all seems related to the fact that CInt rounds to the nearest even number.<BR><BR>So if I do CInt(0.005*100)/100 I get 0<BR>but if I do CInt(0.015*100)/100 I get 0.02<BR><BR>Well at least it does what says in the tin so to speak but somewhat odd I thought and basically I can&#039;t use Round(varX, 2) for my purposes here.<BR><BR>What&#039;s even more puzzling is that FormatNumber(0.005, 2) returns 0.01 !!! <BR><BR>Anybody got my a "MyRound" function that does what I want Round to do?<BR><BR>I guess I could have go with Int and adding on +0.5 at convenient places to force the rounding to the nearest.<BR><BR>In fact this looks promising as<BR>Int(0.005*100+0.5)/100 yields 0.01 and<BR>Int(0.015*100+0.5)/100 yields 0.02<BR><BR>Cheers<BR>Tim

2. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: Rounding to 2 dp - odd results

Actually, Round is doing just what it&#039;s designed to do.<BR>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctround.asp<BR><BR>I didn&#039;t design it, but it explicitly states that it performs round to even.<BR><BR>Craig.

3. Member
Join Date
Dec 1969
Posts
82

## RE: Rounding to 2 dp - odd results

Ah thanks, my Windows Script Technologies VBScript guide is obviously a "lite" reference guide as misses out this minor detail.<BR><BR>I am struggling to think of a time in 20 years or so of programming when rounding to the nearest even number would have been useful to me. Anybody ever needed to use this functionality?<BR><BR>Cheers<BR>Tim

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Strangely enough...

...this same rounding is performed by many languages nowadays.<BR><BR>I dunno when this shift in paradigm took place. I know that in languages we built back in the 70&#039;s and 80&#039;s we did "round up on .5 or greater", but today...???<BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: Strangely enough...

That&#039;s what I always learnt too, all the way through my schooling.<BR><BR>As the documentation quotes "statistical" sources, I&#039;ll also note that I can&#039;t see how rounding to an even or odd will make stats any more accurate. However, I didn&#039;t do it to degree level, so I&#039;ll agree that I could well be wrong.<BR><BR>I&#039;ll also mention lies, damned lies and statistics.<BR><BR>Craig.

6. Member
Join Date
Dec 1969
Posts
82

## RE: Strangely enough...

More googling reveals the "reason" is that this is statistically more accurate over many runs when you are looking for the most accurate total. A simplistic case given where probability of a head or tail is 0.5 but if you always "round to larger" you get 1 all the time.<BR><BR>Anyway this all well and good but my company is receiving invoices using the "round to larger" more intutitve method as I&#039;m sure do most companies worldwide so hence my little effort below which I&#039;m testing at present. Can&#039;t believe I&#039;ve got to do this but you live and learn...<BR><BR>&#060;%<BR>Function RoundToLarger(varX, N)<BR><BR> &#039; Does a round to larger of varX to N decimal places<BR> <BR> &#039; This differs from the inbuilt VBScript function Round which does a round to even -<BR> &#039; i.e. 0.005 to 0 for 2dp but 0.015 to 0.02 whereas we want 0.005 to 0.01 and 0.015 to 0.02 etc<BR> <BR> If varX &#062;= 0 Then<BR> RoundToLarger = Int(varX * 10 ^ N + 0.5) / 10 ^ N<BR> Else<BR> RoundToLarger = Int(varX * 10 ^ N - 0.5) / 10 ^ N<BR> End If<BR> <BR>End Function<BR>%&#062;<BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not very accurate...

10 ^ N is inherently very "lossy". [Not to mention slow as treacle.] For example, if you did<BR> RoundToLarger( 5, 9.876545 )<BR>then doing<BR> 9.876545 * 10 ^ 5 <BR>could well give you<BR> 987654.4999998<BR>and so you&#039;d add 0.5 to that and do the INT and you&#039;d still get the wrong answer.<BR><BR>I&#039;d avoid 10 ^ N if you can.<BR><BR>Unless you are likely to get a wide range of "varX" values, I&#039;d just SELECT CASE on them. <BR><BR>&#060;%<BR>Function RoundUp( num, places )<BR> Dim sign<BR> sign = SGN( num )<BR> num = ABS( num )<BR><BR> SELECT CASE places<BR> CASE 0<BR> RoundUp = sign * Int( num + 0.5 ) <BR> CASE 1<BR> RoundUp = sign * Int( num * 10 + 0.5 ) / 10<BR> CASE 2<BR> RoundUp = sign * Int( num * 100 + 0.5 ) / 100<BR> CASE 3<BR> RoundUp = sign * Int( num * 1000 + 0.5 ) / 1000<BR> CASE 4<BR> RoundUp = sign * Int( num * 10000 + 0.5 ) / 10000<BR> CASE 5<BR> RoundUp = sign * Int( num * 100000 + 0.5 ) / 100000<BR> CASE ELSE<BR> RoundUp = sign * Int( num * 10^places + 0.5 ) / 10^places<BR> END SELECT<BR>END FUNCTION<BR>%&#062;<BR><BR>Or simply don&#039;t support out-of-range "places" values.<BR><BR><BR>

8. Member
Join Date
Dec 1969
Posts
82

## RE: Not very accurate...

Ah that&#039;s quite nice.<BR>To be honest I only want to use it with 2 dp at present but if I ever fancied 1 or 3 say then would not need to write another function.<BR>Can&#039;t see that would ever want to call it with a float N though.

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## Doesn't matter...

x^y is *always* done via DOUBLE arithmetic.<BR><BR>It&#039;s actually done by way of <BR> EXP( y * LOG(x) )<BR>if you care. That works because of course<BR> LOG(x ^ y) == y * LOG(x)<BR>and because of the identity<BR> EXP( LOG(n) ) == n<BR><BR>[Having said that, *maybe* today&#039;s CPUs are more clever and do successive multiplies if the power is an integer less than some number. I haven&#039;t tested this in maybe 15 or more years.]<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•