Ask Your Question
0

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

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

Kunjomachen gravatar image

updated 2013-06-24 09:32:26 +0200

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 flag offensive 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

Comments

Could you please give examples?

ROSt52 gravatar imageROSt52 ( 2013-06-22 17:32:37 +0200 )edit

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

Kunjomachen gravatar imageKunjomachen ( 2013-06-23 13:32:46 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2013-06-23 09:32:07 +0200

mahfiaz gravatar image

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

edit flag offensive delete link more
1

answered 2013-06-23 10:59:15 +0200

Pedro gravatar image

updated 2013-06-23 11:03:00 +0200

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)

edit flag offensive delete link more

Comments

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

Kunjomachen gravatar imageKunjomachen ( 2013-06-23 13:13:54 +0200 )edit

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.

Kunjomachen gravatar imageKunjomachen ( 2013-06-23 13:24:44 +0200 )edit
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

Pedro gravatar imagePedro ( 2013-06-23 16:03:22 +0200 )edit

@Pedro: Thanks :)

Kunjomachen gravatar imageKunjomachen ( 2013-06-24 05:04:07 +0200 )edit

Question Tools

Stats

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

Seen: 976 times

Last updated: Jun 24 '13