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

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?

Could you please give examples?

@ROSt53 : 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.

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).

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)

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.

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

@Pedro1: Thanks :slight_smile: