excel,google-spreadsheet,excel-formula,spreadsheet,openoffice-calc

I am suggesting the use of helper columns, with the column headers being the years for which the totals will be calculated i.e. 2015 and 2016. I have added an extra row to the data to make sure that it works when the time period is not a whole year....

OpenOffice uses 'OpenOffice Basic', previously known as StarBasic. So it does not understand the command 'File.CLOSE()'. The corresponding OpenOffice Basic command is 'thisComponent.close(true)'. https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide...

As you've discovered, DATEVALUE expects a string that looks like a date, not an actual date. However, there is nothing stopping you from taking your dates and converting them to strings with TEXT. The right format mask can distinguish between dates and string dates for you. The formula in...

openoffice.org,libreoffice,openoffice-calc

On your sheet of data, select the cells to be filtered and use Data>Filter>Advanced Filter.... Then give the list (range of cells) with content to be filtered (your second data example)....

Try plotting the result of table(). The function table() computes the cross-tabulation frequency, which is exactly what you want. set.seed(42) x <- sample(letters, 100, replace = TRUE) plot(table(x)) To plot the sorted values, try this: z <- sort(table(x)) plot(z, xaxt="n", type="h") axis(1, at=seq_along(z), names(z)) ...

regex,search,replace,openoffice-calc,libreoffice-calc

You can escape the brackets with a backslash so they are treated as regular characters. On that base, you could use the following regex to match all square brackets containing only digits: \[[:digit:]*\] When leaving the Replace with box empty, a search/replace run should remove all footnote marks in your...

function,sum,openoffice-calc,sumifs

First, make sure that regular expressions are enabled for formulas: open menu Tools -> Options -> OpenOffice Calc -> Calculate -> General Calculations; check "Enable regular expressions in formulas". Now, you can modify the formula as follows: =SUMIF(C3:C130; "^String.*"; D3:D130) The regular expression ^String.* matches every cell beginning with String,...

spreadsheet,vlookup,openoffice-calc

You should use VLOOKUP() for this: =VLOOKUP(B2;Sheet2.A2:B6;2;0) VLOOKUP (V = Vertical) is a function that will do exactly as you described, the parameters are: What value you wish to find (in this case B2 = 119) Where is the data (in this case the other sheet's A:B range), it is...

macros,charts,openoffice-calc,basic

You can't instantiate a com.sun.star.chart.PieDiagram directly, independent of an already-existing chart. Instead, you'll have to create the chart first, and then create a PieDiagram. Thus, to make the macro work, do the following: remove the line Dim oDiagram As New com.sun.star.chart.PieDiagram change the line oChart.Diagram = oDiagram to oChart.Diagram =...

For OpenOffice / LibreOffice Calc, the following function should work: =IF(AND(A1 = "high ground";NOT(ISERROR(SEARCH("water";B1;1))));"OK";"not OK") or split up on multiple lines with comments: =IF( # First function: result will depend on condition AND( # Condition: two sub-conditions have to be TRUE A1 = "high ground"; # First subcondition: simple equality...

Try this: Search for: (.*/)(.*)$ Replace with:: $1authcode123$2 Explanation: (.*) - Anything inside two parens "()" is called a capture group. The text that matches the pattern inside the first set of parens is stored in "capture group 1". You can use $1 to refer to this capture group when...

linux,bash,shell,unix,openoffice-calc

You can save the eXcel file as csv (format that uses as field separator the comma ,), then you can use awk. We assume you are in the parent directory of the A and B subdirectories. The command you can use is awk -F ',' '{print "mv A/"$1" B"}' myfile.csv...

java,openoffice.org,libreoffice,openoffice-calc,file-conversion

You need to start first : soffice -headless -accept="socket,host=127.0.0.1,port=8100;urp;" -nofirststartwizard then use some library to connect and use the service like jodconverter To call an executable from java : Process process = new ProcessBuilder("C:\\PathToExe\\MyExe.exe","param1","param2").start(); You gave him your path to openoffice exe, then each parameter (-headless...) more infos in runtime.exec...

You don't need code for this, it can be done using in-cell formulas. When you want to increment all even numbers by one: =ROUNDDOWN(A1 / 2, 0) * 2 + 1 When you want to increment all odd numbers by one: =ROUNDUP(A1 / 2, 0) * 2 ...where A1 is...

openoffice-calc,libreoffice-calc

For such a task, you don't need an array formula - it's a typical task for a pivot table. To use it: Add Column headers: Select the "data table" (in my example: A1:B4); Menu Data -> Pivot Table -> Create...; confirm current selection as source: Drag Category to column (or...

The only difference between the two solutions is the delimiter used for the concatenate and substitute statements i.e. ";" vs ",". Openoffice Solution: =CONCATENATE(A1;", ";SUBSTITUTE(A1;"_";"-")) Result Excel Solution: =CONCATENATE(A1,", ",SUBSTITUTE(A1,"_","-")) Result I know i can just use find & replace to replace _ with - but how can i copy...

The problem was that ALL cells were formated as text (format @) at first, prior to the HH:MM format. This placed the formula '00:30 (notice the first character). Clicking on the cell and removing it worked as intended. Example: The bad value is on the left, the good one is...

excel,csv,ms-office,openoffice-calc

There are obviously many different ways you could accomplish this - if you're on Windows, this snippet provides a nice one-liner for PowerShell to split a csv file based on the values in a column: Import-Csv file.csv | Group-Object -Property "department" | Foreach-Object {$path=$_.name+".csv" ; $_.group | Export-Csv -Path $path...

excel,google-spreadsheet,openoffice-calc

Thank you @PsysicalChemist, the VLookup function is working in Calc to.

formatting,openoffice-calc,formulas,libreoffice-calc

You can increase shown numbers after the decimal point by this: Tools -> Options -> OpenOffice.org Calc -> Calculate Set flag "Limit decimals for general number format" and set maximum number in input box (for me it's 20).