Ceiling in VB [RESOLVED]

[krtxmrtz]

I'm just stuck with this, what's the VB function that yields the "ceiling" of a number, i.e. the smallest integer greater of equal than the number?
For instance:

2.1 => 3
3 => 3
3.7 => 4

So it's complementary to the int function that produces the largest integer less or equal than the number.

If it doesn't exist, what would be a way to emulate it?

[vbasicgirl]

are you thinking of the Round() function ?

casey.

[krtxmrtz]

Originally posted by vbasicgirl
are you thinking of the Round() function ?

casey.

I first thought about it but it's not the same thing, for example,
Round(3.7 , 0) gives 3 instead of 4.

[Pasvorto]

You could try something like

x = mod(number)
if x > 0 then
number = int(number + 1)
endif

[Mc Brain]

Try:

Private Function Ceil(sgNumber As Single) As Long
    Ceil = Int(sgNumber) - (sgNumber - Int(sgNumber) > 0)
End Function

[robbedaya]

just add 0.5 to the number you want to round.


round(1.2 + 0.5, 0 ) => 1
round(1.7 + 0.5, 0 ) => 2

[dglienna]

unless the number is negative.


FIX(-99.8) returns -99, so if you made the number negative, you'd get the next lower number, right?

[Mc Brain]

In that case, maybe this is what you need:

Private Function Ceil(sgNumber As Single) As Long
    Dim iDir As Integer
    
    iDir = IIf(sgNumber < 0, -1, 1)
    Ceil = Int(sgNumber) - (sgNumber - Fix(sgNumber) > 0) * iDir
    
End Function

[krtxmrtz]

Originally posted by robbedaya
just add 0.5 to the number you want to round.


round(1.2 + 0.5, 0 ) => 1
round(1.7 + 0.5, 0 ) => 2

Thank you, but the function f I need must be such that f(1.2) = 2

[krtxmrtz]

Originally posted by Mc Brain
In that case, maybe this is what you need:

Private Function Ceil(sgNumber As Single) As Long
    Dim iDir As Integer
    
    iDir = IIf(sgNumber < 0, -1, 1)
    Ceil = Int(sgNumber) - (sgNumber - Fix(sgNumber) > 0) * iDir
    
End Function

Gracias Emiliano, this function and the previous one you've posted are actually what I was after.

[Mc Brain]

Por nada. Anyway, just in case you did not understand something...

Originally posted by krtxmrtz
Thank you, but the function f I need must be such that f(1.2) = 2

robbedaya's code did work. If you do

Debug.Print Round(1.2 + 0.5, 0 )

you would get 2 as the answer. The only problem was the negative numbers.

[vbasicgirl]

didnt realise you wanted to always round up.

what about

MsgBox -Int(-yournumber)

casey.

[krtxmrtz]

Originally posted by Mc Brain
Por nada. Anyway, just in case you did not understand something...

robbedaya's code did work. If you do

Debug.Print Round(1.2 + 0.5, 0 )

you would get 2 as the answer. The only problem was the negative numbers.

I tried Round to begin with but discarded it because it doesn't work in a few instances where the number has no decimal part. For example,

y = Round(4 + 0.5, 0)

produces 4, but

y = Round(5 + 0.5, 0)

gives 6.

Could it be due to the internal floating representation? In principle the binary representations of both 4.5 and 5.5 should have the same mantissa.
I'd be curious to know why this unexpected behaviour takes place.

[krtxmrtz]

Originally posted by vbasicgirl
didnt realise you wanted to always round up.

what about

MsgBox -Int(-yournumber)

casey.

This one seems to work nicely! I don't know if there may be some erratic behaviour like in the Round function as I've described in my previous post, but I've tested it with a good deal of examples and have found no flaw.

Thanks casey, the simpler the better.

[vbasicgirl]

i think its because round uses bankers rounding.

eg:
Round(1.5) = 2
Round(2.5) = 2
Round(3.5) = 4
Round(4.5) = 4


casey.

[Mc Brain]

Originally posted by krtxmrtz
This one seems to work nicely! I don't know if there may be some erratic behaviour like in the Round function as I've described in my previous post, but I've tested it with a good deal of examples and have found no flaw.

Thanks casey, the simpler the better.

Beware with negative numbers. If yournumber is negative you'll get the smaller and not the bigger.

[vbasicgirl]

getting the smaller would be correct wouldnt it because it is actually being greater because of the negative ??

casey.

[Mc Brain]

Originally posted by vbasicgirl
getting the smaller would be correct wouldnt it because it is actually being greater because of the negative ??

casey.

It is... but I understood he needed that way.

[krtxmrtz]

Originally posted by Mc Brain
It is... but I understood he needed that way.

As a matter of fact the figures I'm working with are the results of counting the number of elements in certain groups, so they're always positive. But it's nice to have McBrain's recipe for negative numbers too.

[krtxmrtz]

Originally posted by vbasicgirl
i think its because round uses bankers rounding.

eg:
Round(1.5) = 2
Round(2.5) = 2
Round(3.5) = 4
Round(4.5) = 4


casey.

Are bankers off their onions???