Jonathan Pickup Posted July 2, 2009 Share Posted July 2, 2009 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. Quote Link to comment
Jonathan Pickup Posted July 2, 2009 Author Share Posted July 2, 2009 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? Quote Link to comment
michaelk Posted July 2, 2009 Share Posted July 2, 2009 (edited) Jonathan Will INT(number) + 1 work? michaelk Edited July 2, 2009 by michaelk Quote Link to comment
Jonathan Pickup Posted July 2, 2009 Author Share Posted July 2, 2009 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. Quote Link to comment
Pat Stanford Posted July 2, 2009 Share Posted July 2, 2009 Have you tried Round(D3+0.5)? Quote Link to comment
IanH Posted July 2, 2009 Share Posted July 2, 2009 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. Quote Link to comment
Jonathan Pickup Posted July 2, 2009 Author Share Posted July 2, 2009 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. Quote Link to comment
MullinRJ Posted July 2, 2009 Share Posted July 2, 2009 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 Quote Link to comment
rDesign Posted May 27, 2015 Share Posted May 27, 2015 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. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.