We will be migrating from Ask to Discourse on the first week of August, read the details here

 Ask Your Question

# 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

## Comments

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

## 2 Answers

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

## Comments

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

## Stats

Asked: 2013-06-22 06:11:35 +0200

Seen: 1,355 times

Last updated: Jun 24 '13