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:
=COUNTIF(F2:F32,"*75*")
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:
=COUNTIF(F2:F32;".*75.*")
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:
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 'gs371.example.com'.
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.
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.
I clicked on the Start Recovery button and then was informed that the
recovery of the database was successful.
When I clicked on Next I was able to access the database again.
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:
OFFSET
Returns the value of a cell offset by a certain number of rows and
columns from a given reference point.
Syntax
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.
Example
=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).
Calc does provide a NETWORKDAYS function, but that gives
you the number of work days between two dates. I.e., weekends are excluded.
NETWORKDAYS
Returns the number of workdays between a start date and an end
date. Holidays can be deducted.
Syntax
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.
Example
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.
