Always Round up

[dimava]

hi, how do I always round a number up a number to the whole digit?

thanks

dimava

[Guv]

This should almost always work.

Add .99999999999 to the number and then convert to integer. I assume you can figure out the code to do it.

[chrisf]

You need to add.4999999999 instead of .9999999999.
CInt(4.9 + .999999) returns 6.

[dimava]

ok, thanks guys

[Guv]

Strange that 4.9 + .9999999 equals 6. Are you sure? Wouldn't you get 5.9 if you added one? How could adding .999999 result in more than adding one?

I thought you wanted the next higher integer no matter what. If so, perhaps truncate to integer and then add one.

Adding .499999 does not seem correct. Why not .5?

[kedaman]

Cint doesnt truncate to integer, it rounds to closest integer

[HarryW]

In that case it depends on which function you use. If I remember right, Int() just truncates the number.

[kedaman]

yeah but i think you should round with cint this time:
Cint(number+0.5)

[Nucleus]

You guys have forgotten that if int(number) = number then you shouldn't add anything to the number, however, if int(number) <> number then you should add an integer. That's how to accurately round up.

Don't forget to allow for negative numbers though.

[HarryW]

Well not really, if you notice Guv suggested adding 0.999999 or thereabouts. If you add this to an integer and truncate it, you get the original integer.

[Nucleus]

Harry, what about if I have 1.0000001?

1.00000001
0.99999990+
---------------
1.99999991 which will be rounded down to 1.

On the other hand, (int(1.0000001) = 1.0000001) will return false so you can catch the error and round it to 2 rather than 1.

Comparing int(number) vs number is the most accurate way I can think of to do this...

[HarryW]

Well there is a limit to how accurately a floating point number can be represented in binary, so it is certainly possible to add the closest value to 1 that is less than 1. At greater accuracy than that, it's pointless trying to be accurate because you are limited by the machine.

[kedaman]

Sometimes it seems like the last digit is dropped off when displayed, but i guess the displayed value is not equal to the stored, if you pass 0.999999999 there might just be a fraction that is smaller than 0.000000001 thats why i suggested cint(x+0.5)

[Nucleus]

Kedaman, I don't think your appraoch is necessarily appropriate. Consider if I want to round the two numbers 0 and 1 to nearest integer that is they should return 0 and 1:

debug.print CInt(0 + 0.5)
debug.print CInt(1 + 0.5)

This is because CInt and CLng always round it to the nearest even number.

Better is to compare int(number) vs number.

[kedaman]

youre right.