Help about Round() function

[sachuchem22]

I created a report of marks of students in which i want to round up marks to next whole number if are in fraction. i were used round() but it roundup 29.5 to 30 but not 30.5 to 31. In report marks alway either whole number or fractional like X.5 . So i want to round up only fractional mark to next whole number and whole number marks stay as it is.

[jforbes]

I'm guessing you are running into the "Bankers Rounding" covered in How To Implement Custom Rounding Procedures

If you are looking to round up, always, no mater the fractional amount, you can use something like this:

Fix([YourNumber] + 1)
OR
Int([YourNumber] + 1)

[PhilOfWalton]

Sorry, that doesn't work.

Fix(10) + 1 = 11 when the required answer is 10.
Round of X.5 gives X if X is an even number and X + 1 if X is Odd

so round(10.5) = 10
round(11.5) = 12

Best solution is to add a bit say 0.00001

Round(YourNumber] + .00001)
 

Phil

[jforbes]

Good call, Phil. I was hoping to be slick and missed that.

With that in mind, I would go with something like this:

Public Function roundUp(ByVal x As Double) As Double
    roundUp= IIf(x = Int(x), x, Int(x + 1))
End Function

?roundup(10)
>10
>?roundup(10.5)
>11

[PhilOfWalton]

Looks even better than my solution.

Probably doesn't apply to the OP because I doubt whether negative marks are given, but your function rounds say - 10.7 to -10.0

Phil

[NeoPa]

Phil:
Looks even better than my solution.

I'd have to disagree :-)

If the marks are always a single decimal place, as I believe the OP was trying to say, then Round(Value + 0.0001) is reliable and accurate, as well as being quite simple.

Sorry J. I believ Phil called it right first time on this one.

[zmbd]

What about Allen's solution?

Rounding up (Read More)

To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this:
- Int( - [MyField])

As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.

To round up to the higher cent, multiply by -100, round, and divide by -100:
Int(-100 * [MyField]) / -100

So In op [Mark]=29.5
using AB;
- Int( - [Mark]) === - Int( - [29.5]) === -(-30) === 30

If [Mark]=28.2
- Int( - [Mark]) === - Int( - [28.2]) === -(-29) === 29

If [Mark]=26.0
- Int( - [Mark]) === - Int( - [26.0]) === -(-26) === 26

etc...

Of Course, this fails a bit if the fractional part is sufficiently small...
[Mark]=28.000000000000001
- Int( - [Mark]) === -Int(-28.000000000000001)
=== -(-28) === 28

[jforbes]

Allen has a lot of cool tricks up his sleeve. Flipping the sign is probably the least CPU intensive operation there is.

[NeoPa]

zmbd:
What about Allen's solution?

That works fine Z. Not a lot better than using Round() with an addition of 0.5 though. In my earlier post, and in Phil's original suggestion, the basis was simply working to rounding up .5. If you're looking at the original question and want to round up any fraction then your approach will work, but is a bit fiddly (Changing sign twice as well as calling a function.) but using Round() is fraught because you'd want to map the spread of a single unit (from .0 to .999) to something that is rounded reliably. With the behaviour of Round() varying as it does that's not an option.

So, if the original question is to get correct results specifically for values ending in .0 or .5, as I believe the question is asking, then the tweaking of Round() is fine. For a more general approach your (Allen's) suggestion is required.

[zmbd]

flipping the sign is a tad fiddly :)
however, it's a single bit operation at the processor level... shrug.

The way I distilled the OP was:
?Round(29.5) = 30 'ok
?Round(30.5) = 30 'desired result is 31
"... round up only fractional mark to next whole number ... "

What I read here is the #.5 is only an example and that the last statement intends that any fractional part was to increment the number to the next whole number in the same manner as the Excel Function
=RoundUp(30.5,0) = 31 or =RoundUp(30.02,0) = 31

Hopefully sachuchem22 will clear this up :)