rounding help 1

[griffitd]

Hi

I ahve the following values in a grid

6 12 19 12 6 5

What I want to achieve is setting the 19 to 18 and the 5 to a 6 i.e. divisible by 6.

This also needs to work for the following:
30 60 65 43 30
The 65 needs to be 66 the 43 and the 43 needs to be 42

Thanks

 

[SkipVought]

hi,

...19 to 18 and the 5 to a 6...

First instance rounds DOWN to the nearest divisible by 6.

Second instance rounds UP to the nearest divisible by 6.

Please explicitly state your logic.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Given you expressed intention, it appears that:

[blue]Public Function TweakNumber(n As Long) As Long
    TweakNumber = CLng(n / 6) * 6
End Function[/blue]

 

[SkipVought]

strongm,

I did not realize that Clng() behaves differently than Int(). I learned something!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

One thing to keep in mind

When the fractional part of a value is exactly 0.5, the CLng function rounds to the closest even number. For example, 0.5 rounds to 0, 1.5 rounds to 2, and 3.5 rounds to 4. The purpose of rounding to the closest even number is to compensate for a bias that could accumulate when many numbers are added together.
CLng differs from the Fix and Int functions, which truncate, rather than round, the fractional part of a numbernts for a set period of time.

You may want or not want this behavior. So using strongm's code both 9 and 15 round to 12.

 

[SkipVought]

...which is why I stated, "Please explicitly state your logic."

I would have approched it...

Public Function TweakNumber(n As Long, Optional Offset As Integer = 0) As Long
    TweakNumber = Int((n + Offset) / 6) * 6
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[griffitd]

Thanks for the replies

The logic is that I need to the numbr to be to the nearest rounded up or down number whichever is the closest

The function is great but hoe do i calculate the offset

i.e. for 19 the offset is 0 for 5 the offset is 1

Thanks

 

[strongm]

>nearest rounded up or down number whichever is the closest


Sure - but the point MajP is making (and Skip was getting at as well) and that I deliberately ignored, since none of your example numbers were affected, is you have to make a decision about what you want to do when your value is exactly halfway between two viable options. So, do you want 9 to be 6 or 12? Do you want 15 to be 12 or 18?

The function I used uses something often known as financial (or bankers) rounding, which is designed to try and minimise rounding issues in financial calculations. MajP has described how it works.

My function can be tweaked either way, to ensure which way we round at the mid points:

TweakUp = CLng((n + 0.5) / 6) * 6

or

TweakDown = CLng((n - 0.5) / 6) * 6

 

[MajP]

There are actually eight common tie breaking rules, as described in this article.

http://en.wikipedia.org/wiki/Rounding

4 Tie-breaking

    * 4.1 Round half up
    * 4.2 Round half down
    * 4.3 Round half away from zero
    * 4.4 Round half towards zero
    * 4.5 Round half to even
    * 4.6 Round half to odd
    * 4.7 Stochastic rounding
    * 4.8 Alternating tie-breaking

4.1, and 4.5 are the two we are discussing and the most commonly used.