Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 15 Apr 2019 20:32:21 +0200How 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/How do I find the last entry in a column that may contain many blanks (Solved)https://ask.libreoffice.org/en/question/190297/how-do-i-find-the-last-entry-in-a-column-that-may-contain-many-blanks-solved/I wish to easily find the last value in a column that may contain blank entries. I am allowing up to 70 rows of data.
Column A will contain the Date of an entry, but not every other column will have an entry on that date.
I would like to find a way to determine what row contains the last entry in Column B, so I can select the corresponding date in Column A
In the accompanying image -The range is A10:D79. Column A will contain consecutive rows of data (presently to Row 15) the last entry in Column B is in Row 13. How do I find the row number of that value?
![image description](/upfiles/15551604027861383.png)
I want the row number of the range A10:D79 that would correspond to that circled entry (relative row 4) so I can use it, along with the offset (+9) to determine the actual row number (13) in that sheet. Then I can use the indirect address function to extract the required value for the date on that row.
Other related questions seem to assume that ALL columns in any row are filled, and do not seem to address my particular need where there are many blank rows that are irregularly spaced in the target column.nobody_specialSat, 13 Apr 2019 15:26:02 +0200https://ask.libreoffice.org/en/question/190297/LibreOffice Calc how to fill blank cells with value of last known cell that is not emptyhttps://ask.libreoffice.org/en/question/190061/libreoffice-calc-how-to-fill-blank-cells-with-value-of-last-known-cell-that-is-not-empty/ I have a CSV file opened in Calc, in Column1, the last cell with value is on row 8333, starting from 8334 onwards to the bottom of the file, the cells are all empty; I am wondering how to fill these empty cells with the value of Column1 on row 8333; even better is it possible that I can fill these blank cells with randomly selected values from rows (2 - 8333) for Column1. All the headers are on row 1.
Since the file has about 74k rows, so mouse clicking is tedious, is it possible to automatically select blank cells starting from 8334 to 74503?dadaoThu, 11 Apr 2019 17:58:03 +0200https://ask.libreoffice.org/en/question/190061/How to return empty cell from function in calchttps://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/I want get a completely empty cell at return without using quotes, i.e. not like at previous [question](http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/)denis.polkovnikovWed, 14 May 2014 11:07:11 +0200https://ask.libreoffice.org/en/question/33893/[SOLVED] I keep getting #VALUE! [closed]https://ask.libreoffice.org/en/question/173806/solved-i-keep-getting-value-closed/I have researched this and tried many suggested solutions that I found but nothing seems to work.
I have a spreadsheet with 12 pages labeled Jan to Dec.
I am trying to keep a running total of an item each month (ie Year To Date). For each month there may or may not be a value entered in the item cell. If there has been no entry since the start of Jan I want the total cell to be blank until there is an entry.
The item "Entry" cell is N54 and the "Total" YTD cell is O54 in each of the 12 sheets.
I want to add the "Entry" cell from the current month to "Total" cell of the previous month.
I have tried many ways to accomplish this but my current method is as follows;
Since Jan is the start of the year there is no previous entry I used =IF(N54>0,N54,"")
For the rest of the year I have =IF (AND($Jan.O54="", N54=""),"",$Jan.O54+N54) with of course "Jan" being replaced with "Feb" and then "Mar" etc as the year proceeds.
My problem is if the previous month "Total" has no value I get #VALUE!.
I understand that "" is supposed to equate to "0" but it does not seem to be so. I have tried different formulas but nothing seems to work. I have also tried " " and {} but that did not work either. It seems if I try to add the current "Entry" cell to the previous "Total" cell that does not contain an entered value and is blank except for the formula the error occurs.
I hope my explanation of my problem is understandable and any suggestions that would get this to work would be appreciated. I am using Version: 6.1.2.1 (x64).Charlie GoodDogMon, 26 Nov 2018 00:26:39 +0100https://ask.libreoffice.org/en/question/173806/Fixing formula seeing blank spreadsheet cell same as cell with 0 in ithttps://ask.libreoffice.org/en/question/163347/fixing-formula-seeing-blank-spreadsheet-cell-same-as-cell-with-0-in-it/ I have this formula IF(D25=0,"No Report","") in cell I25
but if I delete the 0 it still says "No Report" If I now put a space character in the cell D25 or a different number I25 clears.
Can someone suggest a formula which shows "No Report" only if 0 in D25 please?BarrowmanMon, 20 Aug 2018 17:42:28 +0200https://ask.libreoffice.org/en/question/163347/Calc: completing text columns to make page sortablehttps://ask.libreoffice.org/en/question/154235/calc-completing-text-columns-to-make-page-sortable/I have a simple problem. We have a column of names and of reference numbers that are only mentioned in one row though they relate to places etc that occur in a series of rows. This means the page cannot be sorted. I want to extend the names etc down through the cells below them up to the next name. Of course, I could select and drag the corner, but there are lot of entries in many different sheets and files. It has been suggested to me to use an IF formula along the lines of =IF (A2=NULL, C1, A2), so I could obtain the required list in another column and then move it.
The only output I get with this kind of formula is #NAME? I have tried ISBLANK in place of NULL. What am I doing wrong?
Attached is a screenshot of the page as is.
Thanks in advance...!![image description](/upfiles/1525718682808074.png)philreccommMon, 07 May 2018 14:05:04 +0200https://ask.libreoffice.org/en/question/154235/Use SUMPRODUCT(ISBLANK()) to count alternated cells range.https://ask.libreoffice.org/en/question/141159/use-sumproductisblank-to-count-alternated-cells-range/ Hi, I'm trying to count the blank cells using the formula =SUMPRODUCT(ISBLANK()), but I realized that I can only count consecutive cells like =SUMPRODUCT(ISBLANK(V7:V21)) and what I need is to count alternated blank cells like (V7;V9;V11;V17;V19;V21).
Is it possible?wlldmTue, 19 Dec 2017 16:10:05 +0100https://ask.libreoffice.org/en/question/141159/Where is the GoTo Special Equivalent in LibreOffice Calc?https://ask.libreoffice.org/en/question/136459/where-is-the-goto-special-equivalent-in-libreoffice-calc/Hi,
I'm new to LibreOffice and I'm trying to select all the blank cells in a column. In Excel this is accomplished by highlighting the column, hitting F5, choosing "GoTo Special" and then marking "blanks." I am having difficulty locating this in navigator. I'm sure it's quite simple and I am just overlooking it.
Thanks in advance for any help you can provide!DoctorFareswellMon, 30 Oct 2017 03:38:43 +0100https://ask.libreoffice.org/en/question/136459/Win 10: All text is blank including menushttps://ask.libreoffice.org/en/question/135771/win-10-all-text-is-blank-including-menus/ OS is Win10 Professional 64 bitGerardMTue, 24 Oct 2017 16:30:17 +0200https://ask.libreoffice.org/en/question/135771/How to create macro that deletes a row under these criteria?https://ask.libreoffice.org/en/question/84005/how-to-create-macro-that-deletes-a-row-under-these-criteria/Hello,
I need a macro that would delete all rows if these two criteria are met:
- a cell in D column has a value equal to 5
- a cell in H column is blank
I would be grateful for any help on this.musha9Fri, 16 Dec 2016 14:21:42 +0100https://ask.libreoffice.org/en/question/84005/In Calc, how to find the distance to the nearest nonblank cell?https://ask.libreoffice.org/en/question/37847/in-calc-how-to-find-the-distance-to-the-nearest-nonblank-cell/Specifically, I want the number of blank cells above the current cell. I'm using a sheet as a log, and I want to leave a row blank when I miss a day, but be able to count the number of consecutive missed days. I use the missed days count to calculate the effective per-day averages on the next day that isn't missed.PolyergicSun, 03 Aug 2014 03:02:32 +0200https://ask.libreoffice.org/en/question/37847/Polling any type, 'Blank' cell to empty text (string)https://ask.libreoffice.org/en/question/35716/polling-any-type-blank-cell-to-empty-text-string/REALLY OFFTEN I have to take over some grouped data from elsewhere and want the type unchanged during that. OK, I have to accept that there isn't a consistent typing in spreadsheets. But...
It is very annoying that I have to use ugly complicated formulae for the purpose. Taking no special precautions I get the numerical value 0 (zero) if an empty cell (ISBLANK(CellReferece) = TRUE()) is referenced. And I definitely don't want that.
I often use =IF(CellReference="";"";CellReference) or =IF(ISNUMBER(CellReference);CellReference;T(CellReference)) and even more sophisticated formulae.
Why?
In many cases the cell reference is calculated by use of INDIRECT() or of INDEX(), this frequently combined. The expressions get soon VERY complicated (calculating the same reference twice or thrice) and VERY ugly then.
Ther must be a better way. You know one?
[begin EDIT1]@mariosv (and everyone)- Think I cannot give a SMALL example. My problems arise from my "obsession" solving recurring tasks by reusable "engines". Thus it will require some enthusiasm and a bit of time, of course, to study my example and looking out for enhancements. Thank you!
[ask35716PollAnyType001.ods](/upfiles/1403257775935933.ods)[end EDIT1]LuppThu, 19 Jun 2014 22:38:02 +0200https://ask.libreoffice.org/en/question/35716/Count rows based on three criteria, including blank cellhttps://ask.libreoffice.org/en/question/26933/count-rows-based-on-three-criteria-including-blank-cell/I have three columns, the first two contain a date, the third one a number or a blank cell.
I am trying to count all the rows in those columns where a date (in D3) falls between the dates in the first two columns and the third column contains an empty cell.
At the moment my formula looks like this:
=COUNTIFS(A2:A73,"<="&D3,B2:B73,">="&D3,C2:C73,"")
I have also tried:
=COUNTIFS(A2:A73,"<="&D3,B2:B73,">="&D3,C2:C73,ISBLANK(this.cell())
The first two critera seem to work, but as soon as I add the third one (check for empty cell) the result is always 0.KeesSun, 08 Dec 2013 15:36:22 +0100https://ask.libreoffice.org/en/question/26933/How can I distinguish a cell that it is blank or not while inside a user-defined function in Calc Basic?https://ask.libreoffice.org/en/question/24770/how-can-i-distinguish-a-cell-that-it-is-blank-or-not-while-inside-a-user-defined-function-in-calc-basic/How can I distinguish a cell that it is blank or not while inside a user-defined function in Calc Basic? For example if I want to write an "Average" function to work just like the built-in one, I'll try to do something like this:
Public Function AverageS(A)
Res = 0
N = 0
For i = LBound(A, 1) To UBound(A, 1)
For j = LBound(A, 2) To UBound(A, 2)
If Not IsEmpty(A(i, j)) Then
Res = Res + A(i, j)
N = N + 1
End If
Next
Next
AverageS = Res / N
End Function
But `NotEmpty` does not do what I want it to. What should I do?Shayan.ToSun, 03 Nov 2013 22:25:57 +0100https://ask.libreoffice.org/en/question/24770/How to count blank cells - formerly: Check if within a range of cells (a row) there is at least one blank cellhttps://ask.libreoffice.org/en/question/11565/how-to-count-blank-cells-formerly-check-if-within-a-range-of-cells-a-row-there-is-at-least-one-blank-cell/Hi there,
I'm trying to find a formula to check if within each row there is at least one blank cell.
Im using ISBLANK and OR.
If I do =OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2)...) and it works
but since my spreadsheet get till column FF, it is a very long formula and I would like to shorten it using ranges capabilities of libreoffice.
I've tried =OR(ISBLANK(A2):ISBLANK(FF2) but got an error E:502
I've tried =ISBLANK(A2:FF2) but got always FALSE
Anyone can help?
Thanks so much
miromiromarchiThu, 07 Feb 2013 17:39:44 +0100https://ask.libreoffice.org/en/question/11565/