Any computer having hardware at 32-bit can perform arithmetic operations with 15 significant digits, at the most. The only way to overcome this finite fixed precision is to adopt special software that extends the accuracy of the native arithmetic
This preliminary version of Xnumbers for OOo Calc contains over 30 functions to assist In the processing of extended precision numbers.
Here is an example of one of the functions – xmult
Here, we calculate the sin, cos, and tan of an angle to 150 places…
Ultimately, Xnumbers for Calc will have 300+ functions. The existing functionality is a Solid foundation upon which the remaining functions can be developed.
The original XNumbers add-in was developed for Excel by Leonardo Volpi and the Foxes team.
This is an addin that you may or may not find useful. It can be downloaded here
It takes some knowledge and experience with macros to create a custom form for inputting data into spreadsheet tables. This Data Entry Wizard makes it easy by automatically figuring out all the fields in your table and generating a customized dialog for you.
It also features full support for dates – which was the most difficult feature to implement.
In order for the Data Entry Wizard to work, your table must be surrounded by blank cells. Select a single cell inside the table and invoke the wizard as shown below.
I’d like to think that using the Data Entry Wizard is easy and intuitive.
New creates a new row at the bottom of the table.
Insert creates a new row at the current location.
Update updates the table based on the current values in the dialog.
Delete removes the current row from the table.
Next and Previous allow you to navigate throught the table
Also TAB moves you between fields.
Please send me your feedback/comments and especially requests for enhancement. This is open source so you can make your own changes if yu feel up to it.
In future versions, I’d like to add more error checking, sorting, date verification between bounds and selection lists.
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.
To convert a time to the number of minutes past midnight, just multiply by 1440 – as shown below.
You also need to make sure the result cell is formatted as a number.
Creating A Series Of Workdays
To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.
where B5 is the year we wish to find the date on which Easter (Sunday) falls.
This generates American date format MM/DD/YYYY and I believe it is valid to 2075 – which is long enough for me!
Converting text to dates
You have lots of dates to enter in your spreadsheet. This can be tedious when you have to format it correctly. Here is a formula that allows to to quickly enter dates as text.
In the example below, we enter text in column B and convert to dates in column C. The conversion formula for C3 is =DATEVALUE(LEFT(B3;2)&”/”∣(B3;3;2)&”/”&RIGHT;(B3;2))
Custom Time Formatting for a timesheet
In the timesheet example below, to represent the total hours worked for the week, we use a custom time format [H]:MM
Cells E3:E7 are also formatted similarly. If we had used HH:MM, E3 for example would display 07:15 – which is not quite what we are looking for.
Cells C3:D7 are formatted HH:MM AM/PM
Determining whether a year is a Leap Year
This tip explains how to determine whether the year in a date used in a OpenOffice Calc spreadsheet is a leap year.
In the Gregorian calendar, a normal year consists of 365 days. Because the actual length of a sidereal year (the time required for the Earth to revolve once about the Sun) is actually 365.25635 days, a “leap year” of 366 days is used once every four years to eliminate the error caused by three normal (but short) years. Any year that is evenly divisible by 4 is a leap year: for example, 1988, 1992, and 1996 are leap years.
However, there is still a small error that must be accounted for. To eliminate this error, the Gregorian calendar stipulates that a year that is evenly divisible by 100 (for example, 1900) is a leap year only if it is also evenly divisible by 400.
For this reason, the following years ARE NOT leap years 1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600 because they are evenly divisible by 100 but NOT by 400.
The following years ARE leap years 1600, 2000, 2400 because they are evenly divisible by both 100 and 400.
The following formula will determine whether the year number entered into a cell (in this example, cell B3) is a leap year:
=IF(OR(MOD(B3;400)=0;AND(MOD(B3;4)=0;MOD(B3;100)<>0));”Leap Year”; “NOT a Leap Year”)
Date & Time : Calculating the end-of-month
A commonly needed entity is the date of the last day of a particular month or a range of months, usually when payments are due or when interest is calculated. We show two approaches to this.
The EOMONTH function returns the last day of the month based on the specified date and offset value. For the current month, the offset is 0 and the offset is incremented by 1 for each subsequent month – as is illustrated below.
An alternative approach is to use the DATE function. A little known feature of the DATE function is that a zero in the day argument results in the last day of the previous month being output. The example below shows how this could be implemented.
Date & Time : Calculating Dates of Holidays
This particular exercise in developing formulas for each of the major US holidays is a good way to develop expertise in OOo Calc date manipulation. US holidays follow simple guidelines for the dates on which they fall. Some holidays fall on specific dates , such as New year’s day on January 1st and independence day on July 4th. Other holidays fall on specific days within the month. For example, Memorial Day is the last Monday in May and Thanksgiving Day is the fourth Thursday in November.
The OOo Calc functions that will feature prominently in this exercise are
DATE(year; month; day) Returns formatted date corresponding to specified year, month, and day values.
WEEKDAY(dateValue; type) Returns a decimal value corresponding to the day of the week for the input date.
Before we tackle the US holidays problem, let us become more familar with the above functions and how they are used in OOo Calc formulas.
The DATE function allows us to define and manipulate a dates components – year, month, and day – independently. We have already seen this in an earlier tip
The WEEKDAY function returns a decimal value between 1 & 7 corresponding to the day of the week for the specified date. By invoking the TEXT with the appropriate formmating, we can convert the output of the WEEKDAY function to something more meaningful. In the example below, both formats are shown. The last two formulas show one approach to determining the first day-of-week after a specified date.
The table below generates the 10 major US holidays for a specified year – in cell C2
New Year’s Day =DATE(C2;1;1)
Martin Luther King Jr. DayThis is the third Monday in January. =DATE(C2;1;IF(2<WEEKDAY(DATE(C2;1;1));10-WEEKDAY(DATE(C2;1;1));3-WEEKDAY(DATE(C2;1;1)))+14)
President’s Day This is the third Monday in February. =DATE(C2;2;IF(2<WEEKDAY(DATE(C2;2;1));10-WEEKDAY(DATE(C2;2;1));3-WEEKDAY(DATE(C2;2;1)))+14)
Memorial Day The last Monday in May, we subract 7 days from the first Monday in June. =DATE(C2;6;IF(2<WEEKDAY(DATE(C2;6;1));10-WEEKDAY(DATE(C2;6;1));3-WEEKDAY(DATE(C2;6;1)))-7)
I leave the rest for you as an exercise!
Date & Time Basics
Creating a column of consecutive dates is easy with the OOo Calc tool.
The formulas below illustrate how to increment a particular date by a given number of days, months, or years…
By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired.
Consider the data set below, which we wish to chart. This has been covered before elsewhere.
The default chart. In this example, we will adjust the y-axis to start at 20.
We first select the chart for editing by double-left-clicking anywhere inside the chart area. A solid border should appear around the chart as shown below.
Now enter the y-axis editor with the right mouse button as shown below.
Make sure the scale tab is selected. Before we modify the axis scaling, we first deselect the automatic settings. The fields indicated by the red arrows need to be changed.
With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.
Let us step through the relatively straightforward process of importing data from tables in a HTML document.
The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de
Here is what the source HTML file looks like : swiss.html
For this example, we will be importing awebpage from the local disk.
The External Data dialog is activated by Insert-External Data.
Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.
In a prior example we saw how data filtering could be used to reduce the size of the data set before doing analysis.
The database functions perform basic data analysis – but they also have advanced data filtering built in.
The DAVERAGE function returns the average of the values in a database column satisfying a specified condition.
With the database functions – such as DAVERAGE, the criteria are defined in the spreadsheet. This is illustrated below in cells D13:E14. The criteria matrix normally has the same colums and headers as the main database table. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed
In the example below, we have configured the criteria to average rows whose age is <25 AND whose salaries are less than $50,000. These criteria are both selected in the formula of B20. For the other example formulas, we select either the age or the salary condition, but not both.
In B18, we are averaging the filtered ages, but the other example formulas are averaging the salary.
We continue our look at the application of conditional formatting in OOo Calc. Here, we wish to apply alternate coloring to even and odd rows. Prior to invoking the conditional formatting dialog, we select all cells in the sheet. This was covered in an earlier tip
The trick here is using the mod function to identify odd rows to which we apply the custom format.
Here is another simple application of the VLOOKUP function. Consider a sales person who gets paid commissionson sales based on a sliding scale.
For sales up to $50, a comission of 25% is paid, between $50 and $100, the commission drops to 22.5% – and so on. By using the lookup table and VLOOKUP, the correct rate of commission can be applied to each sale.
Conditional formatting is a mechanism that allows the user to apply formatting to a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied. In OOo Calc, you can apply up to 3 rules in a cell or range of cells.
In the example below, we have an array of numbers. To illustrate how conditional formatting works, we will apply different formatting to those cells whose values are greater than the average of the range.
We first need to define a new formatting style.
Apply the desired formatting to an isolated cell and invoke the Styles and Formatting dialog as shown below.
Here, we click on the New Style from Selection icon.
This allows us to add our own custom style to the Style catalog.
We will now make use of our newly defined style in the Conditional Formatting dialog, which is enabled as shown below.
We select Condition 1 and the settings are shown below. The Cell STyle drop down list allows to to select any of the styles from the catalog – including any custom style we have defined.
You must use absolute addressing when referencing cells and cell ranges.
The final result of applying conditional formatting on the selected range of cells.
