# Is it possible to add a ceiling to the value of a cell using its number format? [closed]

Instead of using the ceiling function to round a number upwards, is it possible to make a cell's number format such that the number is rounded upwards to the predefined multiple?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-02 21:46:08.042936

@ROSt52 : For example if we put the format code as 0 then the value is rounded (14.23 to 14, 15.65 to 16). similarly a format code such that ceiling is done to a value to the significance (e.g. every 5 ). If such a format code exists it will be easier than putting a ceiling function to each individual cell.

Sort by » oldest newest most voted No, it is not possible. However there might be a workaround, if storing wrong numbers is an option. Let's say your cells show original value+0,4999999 and every time you use values from that cells you subtract it first, (A1-0,4999999), then you will mostly get correct answers (if your numbers precision is less than 7 decimal points).

more If your idea is to round to a class (e.g. every 5 cm) then yes, it is possible. You need to use a simple formula instead of a function or cell formatting.

For a 5cm class you would do =(INT((A1-0,1)/5)+1)*5 This will result in 8 rounded to 10, 13 rounded to 15, etc

There are two important details: +1 is added so that the number is rounded up (remove it if you want it rounded down); -0.1 is subtracted so that 5 is included in the 5 class and not in 10 (remove this if you are rounding down or if you prefer your classes open on the upper limit)

more

is'nt it same as =CEILING(A1,5)?

What I was looking for is a format code such that the value of a cell is rounded to a class as you said. For example if we put the format code as 0 then the value is rounded (14.23 to 14, 15.65 to 16). similarly a format code such that ceiling is done to a value to the significance (e.g. every 5 cm). If such a format code exists it will be easier than putting a ceiling function to each individual cell.

1

It is indeed the same as CEILING. I wasn't aware of the function. Thank you! However applying a function or formula really rounds the number while formatting only displays the value as rounded. If you only want it to look good then I guess the answer is: no, you can't format that way. Maybe ask for an Enhancement at Bugzilla? https://bugs.freedesktop.org/enter_bug.cgi?product=LibreOffice