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,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,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 ...

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,...

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...

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) *...

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...

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).

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",...

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...

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))) ...

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....