There is a new Spanish language OO related blog from Rafael Sanchez
He points out an interesting Easter Egg in the OpenOffice Calc program.
In any cell, type =starcalcteam()
The image below is displayed…
The Paste Special feature can be used to quickly link to range of cells from another sheet.
In the example below, we have a table on the sheet labelled First. We want to link to the bottom row from the sheet labelled Second
Select the desired range for linking b9:D9, then select Edit - Copy
Now go to the sheet from where we will be linking. Select the start cell of the new range (corresponding to B9 )
Now select Edit - Paste Special.
The Paste Special dialog is shown below. Select the Links option on bottom left.
The end result. The equations are for illustration. This may seem like overkill for a few cells - but can be very useful if yu have a very large range of cells that you need mapped to another spreadsheet.
The Autoformat feature in Calc is used to assign preset formats to a range of cells - creating attractive, professional-looking table designs at the press of a button.
In the ‘before’ picture below, we have a rather bland table.Time to spice it up. Obvious approach is to do it manually - selecting groups of cells in turn and applying different formats. The micromanagers among us probably favor that approach.
There is a ‘quick-and-dirty’ alternative - namely the Autoformat feature.
The Autoformat feature can be used to assign preset formats to a range of cells. Boder styles, number formats etc can all be configured and reused for different tables.
First, select the cells you want to format. From the menu, select Format - AutoFormat
In the Autoformat dialog box, select the desired Autoformat…
The end result. A prettier table with little or no effort.
We can use an array formula to sum up only the positive numbers in a given range.
=SUM(IF(A1:A10>=0;A1:A10;0))
An array formula is defined by hitting SHIFT-CTRL-Enter instead of Enter for a normal (scalar) formula.
In documentation and texts, an array formula is indicated by ‘curly braces’ as shown below
{=SUM(IF(A1:A10>=0;A1:A10;0))}
However, the curly braces should not be included when entering the formula on the spreadsheet.
Harlan Grove added some comments regarding the Excel DATEDIF() function, that I feel deserve an article of their own…
YEARS(EarlierDate;LaterDate;)
MONTHS(EarlierDate;LaterDate;)
but
DAYS(LaterDate;EarlierDate) [and no argument]
Maybe this was the fault of the original StarDivision programmers, but it’s still stupid, and it makes a bad impression for OOo Calc. Maybe it looks like this because the FAR SIMPLER equivalent formula
=LaterDate-EarlierDate
puts LaterDate before EarlierDate.
The only excuse for not fixing this is backwards compatibility: extant design errors must remain so that formulas adapted to those design errors continue to work.
Anyway, the more useful DATEDIF characteristics are YM, YD and MD. Those are more difficult to manage in OOo Calc. Actually, YM is simple.
=MOD(YEARS(EarlierDate;LaterDate;0);12)
YD takes more work.
=DATE(YEAR(EarlierDate)+(TEXT(EarlierDate;”mmdd”)>TEXT(LaterDate;”mmdd”));
MONTH(LaterDate);DAY(LaterDate))-EarlierDate
MD is more difficult.
=DATE(YEAR(EarlierDate);
MONTH(EarlierDate)+(DAY(EarlierDate)>DAY(LaterDate));
DAY(LaterDate))-$A3
is arguably more accurate, but it doesn’t produce the same results as Excel. Excel, for good or ill, NEVER returns anything higher than 29 for DATEDIF(EarlierDate,LaterDate,”MD”) while it can produce screwy results for 5 or fewer. Excel’s DATEDIF(EarlierDate,LaterDate,”MD”) may produce results consistent with DAYS360, but I’ll leave that to someone else to check.
I recently came across a blog in Indonesian - which had useful pointers related to OpenOffice alternatives to the Excel DATEDIF() function. Here is the link: http://maseko.com/
The DATEDIF() function is a strange beast in Excel - so much so that it isn’t even implemented in OpenOffice Calc.
The syntax for DATEDIF is as follows:
=DATEDIF(Date1,Date2,Interval)
Where
Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.
There are sixdifferent interval codes in Excel DATEDIF() and we will cover them all with alternatives in OpenOffice Calc.
For example, to calculate someones age (in years) in Excel, you would use =DATEDIF(A1,NOW(),”Y”)
In OpenOffice CALC, we can use =YEARS(A1;NOW();0)
For calculating number of months in Excel, we use =DATEDIF( A1,NOW(),”M”)
In OpenOffice CALC, we use = MONTHS(A1;NOW();0)
For calculating number of days using DATEDIF, we use =DATEDIF( A1,NOW(),”Y”)
In Openoffice CALC, we can use.. =INT(DAYS(now();A1))
Please bear with me while I relocate my Open Office tips to WordPress.
For an full index of articles in the old blog, see http://www.richhillsoftware.com
Right now, I’m cutting pasting articles from old blog to new. Then I will do cleanup. At the same time I’ll start writing new tips for Open Office Calc.
The COUNTBLANK function allows you to total the number of blank or empty cells in a specified range.
It is used as show n below in the example. Cells C3, B5 and D5 are empty - so the COUNTBLANK function in D7 returns 3.
You may have a situation where the array is quite large and it is important that each cell has a value. To make it easier to trap blank cells, you can combine the COUNTBLANK inside an IF function…
=if(COUNTBLANK(A1:A500)>0,”Blank Cell”,average(A1:A500))
Conditional formatting would also help you spot blank cells.
We will use conditional formatting to identify unique entries between two lists.
In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.
Step 1: Select B2:B19 and open the Conditional Formatting dialog - below. We have already defined a formatting style that has a darker background.
In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.
We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.
The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.
Here is a useful formula - a long with a variant if you need to
enter dates quickly without the delimiters between the days, months and years.
In the example below, we enter our shorthand dates in column B. This column is formatted as Text so that any leading zeroes are not discarded by OOo Calc as it interprets the numbers entered as an integer.
Column C is formatted as a Date and the formula entered in C2 is
=DATEVALUE(TEXT(CONCATENATE(LEFT(B2;2);”/”;MID(B2;3;2);”/”;RIGHT(B2;2));”MM/DD/YY”))
Instead of using the CONCATENATE function, you can use the corresponding “&” operator.
=DATEVALUE(TEXT(LEFT(B3;2)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2);”MM/DD/YY”))
