Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 24 Jun 2013 05:04:07 +0200Is it possible to add a ceiling to the value of a cell using its number format?https://ask.libreoffice.org/en/question/19432/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? Sat, 22 Jun 2013 06:11:35 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/Comment by ROSt52 for <p>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? </p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19442#post-id-19442Could you please give examples?Sat, 22 Jun 2013 17:32:37 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19442#post-id-19442Comment by Kunjomachen for <p>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? </p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19471#post-id-19471@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.Sun, 23 Jun 2013 13:32:46 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19471#post-id-19471Answer by Pedro for <p>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? </p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?answer=19464#post-id-19464If 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)Sun, 23 Jun 2013 10:59:15 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?answer=19464#post-id-19464Comment by Kunjomachen for <p>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.</p>
<p>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</p>
<p>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)</p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19469#post-id-19469What 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.Sun, 23 Jun 2013 13:24:44 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19469#post-id-19469Comment by Kunjomachen for <p>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.</p>
<p>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</p>
<p>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)</p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19468#post-id-19468is'nt it same as =CEILING(A1,5)?Sun, 23 Jun 2013 13:13:54 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19468#post-id-19468Comment by Pedro for <p>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.</p>
<p>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</p>
<p>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)</p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19473#post-id-19473It 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=LibreOfficeSun, 23 Jun 2013 16:03:22 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19473#post-id-19473Comment by Kunjomachen for <p>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.</p>
<p>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</p>
<p>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)</p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19491#post-id-19491@Pedro: Thanks :)Mon, 24 Jun 2013 05:04:07 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?comment=19491#post-id-19491Answer by mahfiaz for <p>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? </p>
https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?answer=19458#post-id-19458No, 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).Sun, 23 Jun 2013 09:32:07 +0200https://ask.libreoffice.org/en/question/19432/is-it-possible-to-add-a-ceiling-to-the-value-of-a-cell-using-its-number-format/?answer=19458#post-id-19458