The MROUND function allows you to round to any value you want and is particularly useful in financial/commerce applications where rounding to the nearest nickel/dime/quarter is required. This function is only available if Analysis AddIn is installed. It rounds the target value to any multiple that you specify.
To round the value in A1 to the nearest nickel we use =MROUND(A1; 0.05)
Unlike the equivalent Excel function, MROUND in Calc allows negative numbers.
In fact, MROUND() is so useful – it can even be used to round the time to the nearest quarter hour. An example of this is shown below.
At first glance, the TEXT function may not sound very useful. Here are a few simple applications.
In the top example, without the TEXT function, the total is not formatted correctlt (as a currency). However, the TEXT function allows us to apply the correct format to the number component of the string.
In the second example, we see how the TEXT function allows us to reformat existing data – such as dates.
Version 2.0 of OOo Calc will feature significant improvements to the DataPlot functionality. For this reason, we will revisit this powerful feature over the next few days.
First, the basics…
Consider the table of data below. There are numerous ways we may want to analyse this data. By using the DataPilot functionality in OOo Calc, we can perform the different analyses quite easily – with a bit of practice.
We first select the table we wish to analyze. Then Data-DataPilot-Start as is illustrated below.
We first are presented with the popup dialog below. We have already selected the data we are interested in – so we just select OK.
The next dialog box allow us to choose the layout of the DataPilot table. In our example, we are interested in knowing how each broker performed in each exchange.
Normally, at least one of the columns in our data source is numerical data that will be processed against the variables we are interested in.
Finally, we get to decide how the data is processed against the input variables. By default, the data is SUMmed, but that may not necessarily always be the case. This dialog is activated by double-clicking on the data field button.
Finally, the output DataPIlot table (or Pivot Table in Excel speak). It isplaced by OOo Calc just below the original data in this example.
I have plans to implement a Keno game in the OOo basic language – but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.
Conditional formatting would also help you spot blank cells.
Conditional Formatting: Comparing two 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.