MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat
23 24 25
26 27 28 29 30 31  
Jul Aug Sep
Oct Nov Dec

Sun, Aug 06, 2017 5:05 pm

COUNTIF incompatibility between OpenOffice Calc and Microsoft Excel

I have an Apache OpenOffice Calc spreadsheet that I use to track deposits to bank accounts. I loaned a friend money some time ago for a used car; when I get a check from him, I note it in a worksheet in the Calc workbook. I also get checks from him sometimes unrelated to the auto loan repayment and have received some checks that include the auto loan payment plus reimbursement for other expenses. I have a "note" column in the worksheet where I'll always have "75", if the check applies to the auto loan repayment, though there may be other text before and after the "75" in the cell. So I thought it would be easy to count the number of checks I've received from him that apply to the auto loan repayment by using the formula below, since the note column is column F and the deposits start in row 2 (the first row is a heading line) and go through row 32:


That works in Microsoft Excel, but it doesn't work in OpenOffice Calc (I'm using version 4.1.1). The count is always zero, if I use the formula above. I found I have to use the formula below, instead, which correctly reports 21 occurrences of 75 in column F at the moment:


Excel will treat the first asterisk as representing a character or any number of characters before the "75" and the asterisk after "75" as representingany number of characters appearing in the cell after "75". However, Calc doesn't view the asterisks that way. Instead, I need to use a regular expression. In a regular expression (regexp), a period represents any character and an asterisk represents zero or more occurrences of the preceding element. For example, ab*c matches "ac", "abc", "abbc", "abbbc", and so on. So the ".*" represents any character appearing zero or more times in the cell. If I try using the formula I use in Calc in Excel, it will report zero occurrences just as Calc reports zero occurrences when I use the formula that works in Excel in Calc.

Since I want to obtain the total amount he has repaid so far, which is the count of the number of occurrences of 75 in column F by $75, I use the forumula below in Calc to obtain that amount, which returns 1575 as of today:

=COUNTIF(F2:F32;".*75.*") * 75

[/software/openoffice] permanent link

Fri, Jan 08, 2016 10:19 pm

OpenOffice personal settings are locked

After I attempted to close a table in an Apache OpenOffice Base database, the database application stopped responding. The table remained open, but I couldn't do anything in it, open any other table, etc. So I forced the program to quit using the Activity Monitor. When I tried to reopen OpenOffice after its abnormal termination, I saw the message below:

OpenOffice 4.1.1

Either another instance of OpenOffice is accessing your personal settings or your personal settings are locked.
Simultaneous access can lead to inconsistencies in your personal settings. Before continuing, you should make sure user 'jasmith1' closes OpenOffice on host ''.

Do you really want to continue?


I selected "No" and looked in the OpenOffice user profile for my account for a .lock file - you need to use the -a option for the ls command to see "dot something" files that would otherwise be hidden.

$ ls -al ~/Library/Application\ Support/OpenOffice/4
total 8
drwxr-xr-x   4 jasmith1  NDC\Domain Users  136 Jan  8 16:28 .
drwxr-xr-x   3 jasmith1  NDC\Domain Users  102 May 18  2015 ..
-rw-r--r--   1 jasmith1  NDC\Domain Users  143 Jan  8 16:28 .lock
drwxr-xr-x  18 jasmith1  NDC\Domain Users  612 Jan  8 20:42 user

Since OpenOffice wasn't closed normally, the .lock file remained. After I deleted the .lock file, I was able to open OpenOffice without the message reappearing.

$ rm ~/Library/Application\ Support/OpenOffice/4/.lock

When the program reopened I saw an OpenOffice Document Recovery window allowing me to recover the database I had been working on before I had to terminate the Base application.

OpenOffice Document Recovery

I clicked on the Start Recovery button and then was informed that the recovery of the database was successful.

OpenOffice Document Recovery Successful

When I clicked on Next I was able to access the database again.

[/software/openoffice] permanent link

Sun, Apr 12, 2015 3:46 pm

Using offset in Apache OpenOffice Calc

As does Microsoft Excel, Apache OpenOffice calc provides an offset function that allows you to calculate a value based on the contents of a cell that is a specified distance in rows and columns from a particular cell. The syntax for calc is as follows:


Returns the value of a cell offset by a certain number of rows and columns from a given reference point.


OFFSET(Reference; Rows; Columns; Height; Width)

Reference is the reference from which the function searches for the new reference.

Rows is the number of rows by which the reference was corrected up (negative value) or down.

Columns (optional) is the number of columns by which the reference was corrected to the left (negative value) or to the right.

Height (optional) is the vertical height for an area that starts at the new reference position.

Width (optional) is the horizontal width for an area that starts at the new reference position.
Arguments Rows and Columns must not lead to zero or negative start row or column.
Arguments Height and Width must not lead to zero or negative count of rows or columns.
In the OpenOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.


=OFFSET(A1;2;2) returns the value in cell C3 (A1 moved by two rows and two columns down). If C3 contains the value 100 this function returns the value 100.

=OFFSET(B2:C3;1;1) returns a reference to B2:C3 moved down by 1 row and one column to the right (C3:D4).

=OFFSET(B2:C3;-1;-1) returns a reference to B2:C3 moved up by 1 row and one column to the left (A1:B2).

=OFFSET(B2:C3;0;0;3;4) returns a reference to B2:C3 resized to 3 rows and 4 columns (B2:E4).

=OFFSET(B2:C3;1;0;3;4) returns a reference to B2:C3 moved down by one row resized to 3 rows and 4 columns (B2:E4).

=SUM(OFFSET(A1;2;2;5;6)) determines the total of the area that starts in cell C3 and has a height of 5 rows and a width of 6 columns (area=C3:H7).

[ More Info ]

[/software/openoffice] permanent link

Thu, Mar 05, 2015 8:04 pm

Calc and datedif

The Apache OpenOffice spreadsheet program Calc as of the current 4.1.1 version does not provide the DATEDIF function found in Microsoft Office Excel and Google Sheets. The datedif function can be used to calculate the number of days between two dates in Excel.

Calc does provide a NETWORKDAYS function, but that gives you the number of work days between two dates. I.e., weekends are excluded.


Returns the number of workdays between a start date and an end date. Holidays can be deducted.


NETWORKDAYS(StartDate; EndDate; Holidays)

StartDate is the date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation.

EndDate is the date up until when the calculation is carried out. If the end date is a workday, the day is included in the calculation.

Holidays is an optional list of holidays. These are non-working days. Enter a cell range in which the holidays are listed individually.


How many workdays fall between 2001-12-15 and 2002-01-15? The start date is located in C3 and the end date in D3. Cells F3 to J3 contain the following Christmas and New Year holidays: "2001-12-24", "2001-12-25", "2001-12-26", "2001-12-31", "2002-01-01".
=NETWORKDAYS(C3;D3;F3:J3) returns 17 workdays.

However, the DATEDIF function isn't needed to calculate the number of days between two dates in Calc. Instead, you can simply subtract the two dates. E.g., if cell B18 contains the date January 1, 2014 and cell B19 contains the date March 4, 2015, you could place the formula =B19 - B18 in the cell where you want to store the number of days between the two dates. Unless you change the formatting of the cell to be something other than a number, you will see 427 as the value in the cell with that formula given the stated dates. That forumula will produce a result equivalent to DATEDIF(Start_Date,End_Date,"d") in Excel.

[/software/openoffice] permanent link

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo