Libre Calc - Setting cell with a formula
libreoffice,libreoffice-calc,calc,libreoffice-base,libreoffice-basic
Under Format, choose Conditional Formatting --> Condition. You will see a dialog box where you can impose the Condition with the setting Formula is as follows; enter the formula: (C1 / B1) <= .25 Then, add a new style with the desired background color, red. Say ok and then add...
OpenOffice Calc: SUM(IF()) not working
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...
LibreOffice Calc macro - How to remove header and footer in given sheet?
libreoffice,libreoffice-calc,libreoffice-basic
I found this is the way to turn header and footer off: Dim oDoc As Object Dim oSheet As Object Dim oStyles As Variant Dim oPstyle As Variant oDoc=ThisComponent oSheet=oDoc.Sheets.getByName("Sheet1") oStyles=oDoc.StyleFamilies.getByName("PageStyles") oPstyle=oStyles.getByName(oSheet.PageStyle) oPstyle.HeaderOn=FALSE oPstyle.FooterOn=FALSE ...
How to use LibreOffice functions into Basic?
libreoffice,basic,libreoffice-calc,libreoffice-basic
MDETERM needs a square array. And ZTEST works only if there are values in the range. Sub Main Dim oFunc as Object Dim vResult as Variant Dim oRange as Object Dim bDoZTEST as Boolean Dim aSubArray as Variant Dim vValue as Variant oFunc = GetProcessServiceManager().createInstance("com.sun.star.sheet.FunctionAccess") vResult = oFunc.callFunction("SUM", Array(1, 2,...
libreoffice calc concatenation of strings, function name in different language
function,libreoffice,libreoffice-calc
There's no need to translate the names manually. If the user has selected trollish as i18n language and if he has disabled using english function names in calc (Menu Tools -> Options -> LibreOffice Calc -> Formula -> Formula options), he will see the corresponding function name in trollish. The...
LibreOffice formula - find from list and sum the quantity
You can solve this easily using the VLOOKUP() function. It tooks an array, searches for a certain value and returns a value from another column of the same row. In your case, you would put the following formula into the cell Sheet2:C2 (first cell below "Total"): =VLOOKUP(A2; Sheet1!$A$2:$B$7; 2) *...
LibreCalc Search and Replace, Search for [] and replace it, along with its contents
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...
OpenOffice Calc displays only 9 numbers after decimal points, how to show more
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).
Is it possible to recalculate all cells (i.e. Ctrl+Shift+F9 function) automatically on a timer?
Running libreoffice 4.4.3.2 Ok you can use a link to a value in another sheet if you like. To create a name range insert->names->define Sub recalc_timer document = ThisComponent.CurrentController.Frame switch_on = ThisComponent.NamedRanges("switched_on").ReferredCells.getCellByPosition(0,0) dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem value must be more than 0 to hold while condition while switch_on.getValue()>0 dispatcher.executeDispatch(document, ".uno:CalculateHard",...
Compare big gapped data columns in chart
charts,libreoffice-calc,data-comparison
There are different ways to handle differently-scaled data when creating a chart. The easiest way is to "scale" the raw data; e.g., to divide every value in col1 by 1000 and use the result as source data for the chart. If modifying the source data is no option, you could...
Parametrized Conditional formatting in Calc
function,conditional-formatting,libreoffice-calc
ADDRESS returns the reference as text. CELL expects a reference. So you need to translate the reference string using INDIRECT: =CELL("CONTENTS", INDIRECT(ADDRESS(1,3))) ...
Date calculation in Libre Office
Just subtract, it will give you the number of days. =A2-A1 If A2 was 25 Feb and A1 was 21 Feb, the result will be 4....