OpenOffice Calc Tips

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…

---

Excellent example of POOR DESIGN in OOo Calc.

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.

---

Thanks Harlan for taking the time to comment on this topic!

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

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.

In the spreadsheet below, we wish to apply a formula to the values of a particular column - C and display the results in column D. However, the column is ’sparse’ in that not all cells in the column have values.

This would be problematic if the formula returns an error when applied to a blank cell. However, by incorporating the ISBLANK function
as shown below, we can gurantee that only non-blank entries are processed.

The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen
before how to split a person’s name into it’s component parts.

Here, we take a single cell containing a US address and split it into it’s City, State and Zip components. As long as the string we want to partition is well beahved - as is the case here, splitting should be relatively straightforward with the appropriate combination of text functions.

If, for example, we did not have a comma after the city, we could use the formulae below to extract the city and state components…

=LEFT(B3;LEN(B3)-9) and

=MID(B3;LEN(B3)-7;2)

Also, if you want to delete column B, you will first need to preserve the columns you have just created by doing a Paste Special (values only) as shown
here

This worksheet can be downloaded here

The SUBTITUTE function replaces a sequence of characters with a different sequence of characters in a text string.

The arguments to the functions are :
SUBSTITUTE(text, old_text, new_text [,instance_num])
where

• text The text for which you want to substitute characters.
• search_text The text you want to replace.
• new_text The text you want to replace search_text with.
• occurence Optional: The occurrence number you want to replace.

This function should be used when you want to replace all the instances of some text regardless of their location in the text. If “occurence” is left blank, every occurrence of “search_text” is changed to “new_text”. The SUBSTITUTE function is very similar to the REPLACE function.

See also this tip for a clever application of the SUBSTITUTE function.

If you are familiar with project management and the various tools that are used to schedule deadlines and resources, you may be familiar with Gantt charts.

In this particular tutorial, we look at using stacked bar charts

First, we need to tabulate our project data. The numbers in columns D & E are total days completed & estimated days remaining in each task respectively.

Selecting the above table, we invoke the chart wizard with Insert - Chart.

Both the first row and column are selected as labels..

Select the Bar Chart type - below…

Of the different bar chart types, we choose the stacked variant - below…

Here, we add a meaningful title..

The ‘finished’ chart. We have some postprocessing further to perform before it looks like a Gantt chart. Select it for editing…

The blue bar above represents the start date of each task. We will make it disappear. Fist double click on any blue bar to invoke the Data Series editor below. Set the area fill to None and remove the border.

This is what the chart looks like with the blue bars removed …

Now select the Y axis for editing as shown below…

We now want to adjust the Scale settings for the Y-axis based on the timelines defined in the initial table.

The major interval (61.0) is approximately 2 months. The minor interval (1.0) is one day.

On the Label tab, we rotate the text to almost 270 degrees as shown below…

The finished Gantt chart…

In OpenOffice Calc, the FIXED function returns a text representation of a number rounded to a specified number of decimal places.

(more…)

The CHOOSE function comes in handy when there are a number of options for a particular value based on the result of an expression or result. It saves having to construct a complex formula involving nested-ifs.

In the example below, the CHOOSE function is used to select the text form for the day of the week - according to the numerical value returned by the WEEKDAY function