round up to the next whole number

[Jonathan Pickup]

i'm working in a worksheet, and I want to round the number up to the next whole number. I've tried =Round(criteria), but if the true answer is less that 0.5, the number is rounded down, i want it rounded up, always.

[Jonathan Pickup]

I found a way to do it, but I have to use a long calcualtion and an IF statement. Should there be an easier way?

[michaelk]

Jonathan

Will INT(number) + 1 work?

michaelk

Edited July 2, 2009 by michaelk

[Jonathan Pickup]

not always, there is a situation where you end up with one too many, when the area divided by the number equals an exact number. i did use:

=INT(D3/E3)+(IF((D3/E3)-INT(D3/E3)>0, 1, 0))

This will only add and extra 1 if it needs to. I just thought there might be a better way.

[Pat Stanford]

Have you tried Round(D3+0.5)?

[IanH]

Pats way is traditionally one of the ways of doing this however it does not necessarily take into account negative numbers if you need to handle them.

If you need to handle negative numbers, then it is slightly more complex. Pascal often has a sgn function that returns -1 or +1 depending on the sign of the number. VS does not have this so if you do need to handle negative numbers, you can either create a function to mimic sgn and use the formula below.

However, your application may require that rounding of negative numbers cannot be accomplished with the round function in a simple formula, you will need to revert to using an if statement for the whole formula that implements the formula slightly differently depending on whether you want a negative number rounded up or down.

i := ROUND( ABS( criteria ) + 0.5 )) * sgn( criteria );

FUNCTION sgn( n : REAL ) : LONGINT;

BEGIN

IF n < 0 THEN

sgn := -1

ELSE

sgn := 1;

END;

You also need to test that the 0.5 added to 1 will not round up to 2. Some Pascal implementations might and it may even be processor specific resulting in rounding errors that mean that the value you see on screen (ie 0.5) is really a slightly different number (ie 0.50000000001) internally. In this case, the 0.5 may need to be 0.49... depending on the potential precision of the input value and floating point number in use.

[Jonathan Pickup]

thanks for your input guys. I was building a worksheet, So I can't use a script. I wanted to see if there was an easy way to to this in a worksheet. I'm not sure if my way is the most elegant, but it works.

the worksheet is supposed to work out the area of a building, divide it by the carpark ratio and then show how many whole carparks are needed. INT() and ROUND() both do part of the job, and changing the decimal places to 0 would do it, but i want to know that my cell will ALWAYS round up to the next whole number.

I have also used the same formula to calculate how many downpipes I need on a roof. If you go over one downpipe, the worksheet has to show that 2 downpipes are needed.

[MullinRJ]

Jonathan,

Add a wish for Floor and Ceiling functions. Floor is a round down function and Ceiling is a round up function, both found in most popular spreadsheets. VW surely needs more functions for its WS.

Raymond

[rDesign]

I was just wondering about Vw Worksheet functions and found this old thread requesting 'Floor' & 'Ceiling' (round up & down) functions.

Have the worksheet rounding functions been expanded / improved in Vw2015?

Thanks.