Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 09 Oct 2020 10:23:31 +0200How to use "assume zero" on data series without messing up dates?https://ask.libreoffice.org/en/question/138385/how-to-use-assume-zero-on-data-series-without-messing-up-dates/Hello everyone,
I'm trying to use 'assume zero' on data series in a chart with dates on the x-axis.
Whenever i try to use it the x-axis get's messed up and shows dates starting with 1999 instead of the entered values.
Is there a trick, so it only shows the entered dates?maxbSat, 18 Nov 2017 16:33:24 +0100https://ask.libreoffice.org/en/question/138385/Formula result in cell always shows zerohttps://ask.libreoffice.org/en/question/270287/formula-result-in-cell-always-shows-zero/Every formula shows 0 in cell.
For example this formula computes to a non-zero value, but the cell doesn't show the result properly.
![image description](/upfiles/16022311337867574.png)
There is no special formatting on the cell.
Even the formula "=1" gives 0.00.
Every sheet does this. But not in the cells that already had formulas in them. some switch was flipped idk what. HazeAerospazeFri, 09 Oct 2020 10:23:31 +0200https://ask.libreoffice.org/en/question/270287/Calculation of exponential trend with occasional zero valueshttps://ask.libreoffice.org/en/question/263906/calculation-of-exponential-trend-with-occasional-zero-values/I'm using a spreadsheet formula for calculation of an exponential regression:
`=EXP(INTERCEPT(LN(OFFSET(Data.$H$5,ph2StartIndex,0,ph2EndIndex-ph2StartIndex+1,1)),OFFSET(Data.$D$5,ph2StartIndex,0,ph2EndIndex-ph2StartIndex+1,1)))`
but when stripped of the book-keeping it's just the standard array formula given in HELP:
`=EXP(INTERCEPT(LN(Yarray,Xarray)))`
The problem is that a Y-variable can sometimes go to zero, which causes an ERR:502 exception because LN(0) is undefined. Since the Y-variables are themselves calculated, I could change 0 to 0.1 wherever it occurs, but that smacks of fiddling with the data.
Can anyone suggest a more elegant way of fixing the problem? Could the entries containing (y,x=0,x) be ignored by including a zero test in the above array formula? I see the built-in trend calculation doesn't have this problem.
DavidLDavidL1Thu, 03 Sep 2020 08:25:26 +0200https://ask.libreoffice.org/en/question/263906/Display and print a blankhttps://ask.libreoffice.org/en/question/260310/display-and-print-a-blank/I am contemplating the move from Excel for our business.
I'm trying Calc v7 with a workbook created in XL that is central to our operations, so I have opened it in Calc to do a test.
I have a sheet in this workbook where each cell simply equals its corresponding cell in another sheet.
For example, cell A1 has a simple = formula, referring to cell A1 in the source sheet.
Where there is no entry in the source sheet, the destination sheet shows a 0.
I want it to display (and print) as per the source sheet, so I want it to show a blank instead of 0.
I only want to do this in this sheet.
I can do this in XL by going to the options for the file and unchecking "show a zero where cells have zero value" (or something like that).
I've experimented by changing the setting under tools/options/LibreOffice Cal/View/Display, and un-checking "Zero Values" and that suppresses the zeros nicely - but this is a global setting, not just the sheet.
If I use Format Cells/ Number/Format Code, I see "General" is the default code. I've tried both over-typing it and appending it with a ; and this suppresses the zero cells, but it also suppresses the cells where the source sheet does have entries that are not 0 and which should be displayed.
Is there a way of suppressing the display of zero only for those cells which don't have an entry in the source sheet?
Many thanks in advance for any help you can give.YorgSat, 15 Aug 2020 08:50:55 +0200https://ask.libreoffice.org/en/question/260310/How I suppress zero values in spreadsheethttps://ask.libreoffice.org/en/question/160475/how-i-suppress-zero-values-in-spreadsheet/ I have a spreadsheet which looks good on t.he screen but prInts all zero values. I would like zero values not to be printed at all. I'm sure it's simple but just can't see how to do it.Glyn-eThu, 12 Jul 2018 16:12:16 +0200https://ask.libreoffice.org/en/question/160475/How can I import a csv in Calc WITH the leading zeros that are in the csv file?https://ask.libreoffice.org/en/question/246295/how-can-i-import-a-csv-in-calc-with-the-leading-zeros-that-are-in-the-csv-file/I have tried to turn off all auto-formatting, but libreoffice always finds a way to autoformat. What is particularly annoying this time is it is taking off leading zeros from numbers in a csv file. I wish that there was a way to turn off formatting of numbers - that it would just save and display whatever I have typed into a field. I have looked at the csv file as text and can see that libreoffice did save the numbers with the leading zeros, but I can't figure out how to import them. How can I resolve this?drvdwMon, 25 May 2020 16:59:53 +0200https://ask.libreoffice.org/en/question/246295/Some Zero values not printing..https://ask.libreoffice.org/en/question/245547/some-zero-values-not-printing/ Hi, this is a follow on question about zero values. Thanks to JohnSUN I have managed to get my spreadsheet to look how I want it to, with most (..but not all..) zero values hidden. However, when I print, print-preview or export as pdf, ALL the zeros vanish. Is there any setting that I can use to make only those Zeros that show to print while leaving the others blank ?Pete of EborThu, 21 May 2020 13:24:55 +0200https://ask.libreoffice.org/en/question/245547/Show some zero values when zero values not to be shownhttps://ask.libreoffice.org/en/question/244335/show-some-zero-values-when-zero-values-not-to-be-shown/Sorry if the question sounds a bit non-sensical.. I have a spreadsheet which I have set up NOT to show zero values as there are lots of them. I would rather have a blank column than a column of zeros.
*Tools > Options > LibreOffice calc > view > display zero values* - is unchecked so as to not show zero values.
However, there are a few specific cells where I would like to show zero values as 0.00 Can this be done ? If so, How ? I have looked at 'format cells' but I cannot seem to do it that way. I could just use an IF statement - eg IF {value} = 0 then "0.00", but that would make the cell a text value and could cause more problems elsewhere on the sheet.Pete of EborThu, 14 May 2020 16:21:30 +0200https://ask.libreoffice.org/en/question/244335/Change in Number formatting, or my error?https://ask.libreoffice.org/en/question/232560/change-in-number-formatting-or-my-error/Until recently, with help from this forum, I was able to format zeros as dashes by adding a third format code to the user enterable format codes in LibreCalc. For example: _(#.##0_);\(#.##0\);"-"_)_);_(@_) or [>0]#,##0" ";[<0][RED]-#,##0;"--".
In a new spreadsheet created this week, none of them seem to work. 100 minus 100 equals blank no matter what I do: I can't even manage to get a 0 out if it. I've tried "\- " and some others too.
I'm guessing I'm missing a fundamental setting somewhere, but I've been duckduckgo-ing around looking for clues without any joy. I'd like to use dashes because they are easier to read than zeros, and indicate that I have not omitted or deleted a formula.
When I'm in flame mode, of course, I think that 6.4.1 invalidated this completely essential functionality (all caps of course), but in my heart, I know it didn't. I just goofed somehow. Thank you all for the previous advice posted, and thanks again for input on this one.SeamusMoranFri, 06 Mar 2020 08:49:42 +0100https://ask.libreoffice.org/en/question/232560/How do I turn off "print zero values" in a spreadsheet Template (.xlt) permanently.https://ask.libreoffice.org/en/question/218735/how-do-i-turn-off-print-zero-values-in-a-spreadsheet-template-xlt-permanently/ I know how to use 'format page' and turn it off for each individual file created by the template but I am trying to have the template do this automatically. ThanksDaveAlex1Mon, 25 Nov 2019 00:27:33 +0100https://ask.libreoffice.org/en/question/218735/supress printing zeroshttps://ask.libreoffice.org/en/question/218598/supress-printing-zeros/ Hi Is there any way to supress **printing** zero values. I have a multi sheet spreadsheet which I use for keeping a record of people who have paid for events, and how much they have paid. I use different colours for people who are going/not going/paid/unpaid or part paid. What I have tried to do is copy the data on this coloured sheet to a different sheet with no colours which I can easily print out and take with me. I want this sheet to supress all zero values. I already have ''zero values" under *Tools > Options > LibreOffice calc > View > Zero values* unticked. Oddly, on the screen, the sheet shows no zero values, but when I print it, it does.Pete of EborSat, 23 Nov 2019 20:38:22 +0100https://ask.libreoffice.org/en/question/218598/Calc backup files all zero KBshttps://ask.libreoffice.org/en/question/208160/calc-backup-files-all-zero-kbs/Why are my calc backup files all zero KBs?
There are also dozens of backup files for a particular time. There is only supposed to be one every 10 minutes. Very odd.
It happens with all version of calc that I have run.GriffinitTue, 10 Sep 2019 23:02:51 +0200https://ask.libreoffice.org/en/question/208160/how to display zero values in chartshttps://ask.libreoffice.org/en/question/201423/how-to-display-zero-values-in-charts/ I have a series of bar charts that I am displaying for cumulative statistics showing organic chemical components. Some of these have a value of 0.0 and it is desirable to show this. As an example, if an element 'x-desy3-9' has a blue color legend, and it's value is 0.0 grains in the excel column, I would like to show a "flat square" of blue on the floor of the chart. For some reason, I cannot get this to display. Thanks for any input you can offer.TerreniumWed, 17 Jul 2019 17:11:45 +0200https://ask.libreoffice.org/en/question/201423/Calc:Conditional formatting-How to stop empty cell being treated as zero?https://ask.libreoffice.org/en/question/200887/calcconditional-formatting-how-to-stop-empty-cell-being-treated-as-zero/ Version: 5.1.1.3
Context:
I am using the numerical value of a cell in one column to change the background colour in the adjacent one using the conditional formatting tool.
I have 3 formulae which are working fine except when the source cell is empty. The "treat empty cells as zero" option is NOT turned on but the result of this formula returns the result as if it were zero. I have tried conditions using both a formula and a cell value to return either "" or a white background in the first and last positions in the condition ladder but no joy.
The formula is "IF((AND(P17>=0,P17<6)))" which works perfectly well for the value range in the source cells.
Any help would be very gratefully received:-)
CantagrilFri, 12 Jul 2019 13:17:39 +0200https://ask.libreoffice.org/en/question/200887/How do you make blank cells calculated as zerohttps://ask.libreoffice.org/en/question/190588/how-do-you-make-blank-cells-calculated-as-zero/ I have just downloaded LibreOffice and it appears just what I am looking for. However, I have a large number of spreadsheets done in .xls, .ods and .sxc. In all of these, all blank cells are calculated as zero. However, when I open any of them with LibreOffice and fill in the required information which usually means many cells are blank, I get a #VALUE response where results are shown as the blank cells are not being taken as zero, leaving me the only alternative of putting a zero in each of the cells, which is very time consuming. I assume there is a simple way to make LibreOffice consider blank cells as zero to solve this issue but so far I have not been able to find it.LeeLMon, 15 Apr 2019 20:32:21 +0200https://ask.libreoffice.org/en/question/190588/Do not show #Value!https://ask.libreoffice.org/en/question/182088/do-not-show-value/ Hello im having some trouble with office calc. As you can see bellow on the image i am getting as a result in some cells the **#VALUE!**, but i dont want it to be seen. Any idea how i could do this ? Also you can see the the equation for the cell. I tried to uncheck displaying zero values but no success.
And finaly any idea how i could make the **-##:##:##** to be show in red ? i succeded to do it on the positive but on negatives had no success.
Thanks in advance
![image description](/upfiles/15494506354932174.png)kik94Wed, 06 Feb 2019 11:58:30 +0100https://ask.libreoffice.org/en/question/182088/Can we return an empty cell instead of Zero by keeping the formula ?https://ask.libreoffice.org/en/question/182197/can-we-return-an-empty-cell-instead-of-zero-by-keeping-the-formula/Hello,
I would like to copy (on calc) and paste (in my mailbox) a list of mails that has been generated by a Paste Special with the option "Link" checked.
The problem is that when the referring cell is empty, the "linked" cell returns a zero.
The result: the copied email list contains mails and zeros.
(Note: On Calc, "Paste Text Only" allows omitting zeros when pasting.)
Thank you all for your help !
BiGThu, 07 Feb 2019 11:27:09 +0100https://ask.libreoffice.org/en/question/182197/Align Zeros on Primary and Secondary Y Axes?https://ask.libreoffice.org/en/question/160841/align-zeros-on-primary-and-secondary-y-axes/I have created this chart in Calc with two curves that align to different Y axes. Although they are wildly differing scales, I would like the zeros to lign up, to show visually that the red line (secondary Y axis) plunges into the negative whereas the blue line (primary Y axis) is always positve.
![image description](/upfiles/15317583409517144.png)David.CheshireMon, 16 Jul 2018 18:35:34 +0200https://ask.libreoffice.org/en/question/160841/Formatting calc sheets headers & Footers and Zero valueshttps://ask.libreoffice.org/en/question/92041/formatting-calc-sheets-headers-footers-and-zero-values/Linux Mint 18.1 Cinnamon 64 bit
Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial1
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default;
Locale: en-GB (en_GB.UTF-8); Calc: group2
I have a Calc spreadsheet that has 7 different tabs. I want each sheet to have its own header and footer rather than them all being the same, as they are at present. I also want some of the sheets to display zero values while the other do not.BigAlTue, 04 Apr 2017 15:15:57 +0200https://ask.libreoffice.org/en/question/92041/How to zeroe the last 3 digits of a number (not rounding)https://ask.libreoffice.org/en/question/148968/how-to-zeroe-the-last-3-digits-of-a-number-not-rounding/In LibreOffice Calc, how do I zeroe the last 3 digits of a number
e.g.
123,456,789
gives
123,456,000
Note that numbers are integers. There is no decimal part.Libre comme un oiseauMon, 12 Mar 2018 09:22:44 +0100https://ask.libreoffice.org/en/question/148968/How to show zero values in redhttps://ask.libreoffice.org/en/question/145767/how-to-show-zero-values-in-red/ I have a spreadsheet of various currency values. I want zero values to be formatted in red. Negative values are currently in red, but I can't figure out how to extend that to zero. I looked into conditional formatting and creating styles, and I'm just not understanding how I can accomplish this.nitmdThu, 08 Feb 2018 21:49:53 +0100https://ask.libreoffice.org/en/question/145767/How to ignore zeroes or empty values in a chart?https://ask.libreoffice.org/en/question/140160/how-to-ignore-zeroes-or-empty-values-in-a-chart/ I have a table with entries (name and price) and some empty rows for future additions.
I have a pie chart that represent the total price distribution, so I know how much each entry contributes.
Problem is that the padding empty rows I have are at 0 or blank, and they are displayed in the chart legend.
So my legend looks like this:
[color1] Item1
[color2] Item2
[color3] Item3
[color4] #N/A
[color5] #N/A
How can I tell the chart pie to ignore #N/A or blank or 0 ?
I dont want to have to redefine the data range of the pie everytime I add/remove an entry.
Thank you!OoDeLallyFri, 08 Dec 2017 02:04:50 +0100https://ask.libreoffice.org/en/question/140160/LibreOffice Calc - zero valueshttps://ask.libreoffice.org/en/question/26873/libreoffice-calc-zero-values/I need to print/export to pdf with visible zero values in cells. I couldn't find the solution.
I'm using LibreOffice Calc version 5.0.2.3 under Ubuntu (linux).
I have configured my spreadsheet to always show zeros as blanks. However, in a small number of selected cells, I want zeros to be explicitly shown.
Is there a way to format individual cells to override the default "hide if zero" behavior? I can't find any way to do this.
Thank you very much.
MoringerFri, 06 Dec 2013 09:40:57 +0100https://ask.libreoffice.org/en/question/26873/Solved: Calc: Concatenate does not carry over leading zerohttps://ask.libreoffice.org/en/question/68703/solved-calc-concatenate-does-not-carry-over-leading-zero/ I am combining data from three fields into one. The first column is "Hour", the Second "Minute", and the third "AM/PM". Data in the first two columns is numeric, and for the third column it is either blank, A, or P (text). In the minutes column, 00 is represented as 0, and 05 is 5, and so on. I revised the format for this column so it would have leading zeros, so became 00. When I use concatenate (along with a : between them) the leading zeros disappear, so for example 8:00 is displayed as 8:0. I've read I can format the minutes as text, however won't they need to be recognized as numbers for when I convert the time to 24 hour format? The end result of these cells is that I will have them not as 8:00 PM but rather 20:00.Crash16Sat, 23 Apr 2016 03:31:49 +0200https://ask.libreoffice.org/en/question/68703/Calc: imported csv does not show leading zeroeshttps://ask.libreoffice.org/en/question/32278/calc-imported-csv-does-not-show-leading-zeroes/I have a csv file that, in the editor, shows a field with leading zeros, i.e. "072931"
but that field is displayed in calc as 72931
even if I have Format/cells Leading zeroes 1
How can I make calc display the leading zeroes?
Actually, the "" indicate this is a character field, why are the leading zeroes removed?
ThanksaloboWed, 02 Apr 2014 17:16:59 +0200https://ask.libreoffice.org/en/question/32278/accounting format in calchttps://ask.libreoffice.org/en/question/20066/accounting-format-in-calc/good afternoon.
I'm having problems with the accounting format I use in ms excel.
The issue of alignment and the numbers stay with "(" before and after already decided. However the issue is pending the cells that have the number "0".
In MS Excel they are visually with the standard "-" marked, but in libreoffice such cells are visually with the pattern "# # #".
I'm waiting for a possible solution.
I thank you.
Anderson MeloAndersonTue, 09 Jul 2013 20:34:18 +0200https://ask.libreoffice.org/en/question/20066/My simple formulas no longer give blank cellshttps://ask.libreoffice.org/en/question/28295/my-simple-formulas-no-longer-give-blank-cells/ I have used simple formulas like =IF(B10>0,+B10-B9,"") for years and have always returned a blank cell. Worked fine in Libreoffice until a recent update and now it will not return a blank cell, instead it returns zeros. I see where I can turn off " show zero values" but then I can't have other cells where I want to display zero as a value. Is there a way to return to showing a blank cell instead of zeros while retaining the ability to display zeros where I choose?
Added comment:Using windows 8.1 and Libre Version: 4.1.4.2
Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72RCMSun, 12 Jan 2014 23:42:37 +0100https://ask.libreoffice.org/en/question/28295/MB and GB Hidding zero'shttps://ask.libreoffice.org/en/question/15648/mb-and-gb-hidding-zeros/I'm working on a table that has a large number of MegaByte, GigaByte and TeriByte Values. For the simplicity of math I assume every thing less than 1 is a KiloByte and all cells are MegaByte (Base 1000 not 1024).
Currently cell are formatted to have a tailing MB
Example:
Photos | Documents | Videos
----------+---------------+------------------
53.768 MB | 13 589.378 MB | 27 879 321.558 MB
Is there an easy way to format cells to hide the last 3 zero's but maintain the full value, so people reading the table would see:
Photos | Documents | Videos
----------+---------------+----------
53.768 MB | 13.589 GB | 27.879 TB
or:
Photos | Documents | Videos
----------+-----------+----------
53 MB | 13 GB | 27 TB
This would make it a lot easier to read, but cannot compromise the underlining maths. I would still need to be able to multiple 27TB by 2 and get 55758643,116 not 54.
Thanks for your help.alabanditWed, 03 Apr 2013 10:18:01 +0200https://ask.libreoffice.org/en/question/15648/