A Sudoku Solver!

I have been wanting to do this for the longest time – ever since I became aware of this puzzle last year. So for the past week, I’ve been busy putting together this spreadsheet that will assist you with solving a Sudoku puzzle.

Anyway, three days and 900 formulae later, I present… ooo_sodoku.

This is not a push-button solver. Perhaps the next version will allow that. Basically, for each square in the solution grid, the solver will determine all the possible values – saving you a lot of the drudge work associated with these puzzles.

As you manually enter the values in the solution grid – top left, the main grid in the center will display one of three colors for each square. Light blue indicates that square already filled in solution grid. Green indicates only one possible value – which has yet to be entered in the solution grid, and brown indicates more than one solution

Your comments, encouragement and feedback are always welcome. As I said earlier, there are 900+ formulae here – so bugs are possible.

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Conditional Formatting II

To illustrate the application of conditional formatting, we consider the need to highlight dates in a spreadsheet that are fast approaching or are already past.

The original spreadsheet without highlighting.

Before we apply conditional, we define any custom cell formats we may need. In this case, we have cells highlighted orange and cells highlighted yellow. We open the conditional formatting dialog and evaluate two conditions. The order is important here. In the conditional formatting dialog, once a cell evaluates TRUE, it will not be evaluated again. With that in mind, we first evaluate if a date is past due. We then evaluate if a date is fast approaching.

The modified spreadsheet with conditional formatting applied. Using this technique, the spreadsheet has become more readable and any key dates or deadlines can be more readily identified.

Read More

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.

Read More

Regression Analysis II : Basic functions, charting

In a continuation from the previous tip, we look at some basic built-in functions for determining trendline coefficients of a X-Y plot.

For a series of X-Y values that we suspect have a linear relationship, we can determine the slope and y-intercept values of the linear approximation using the builtin functions SLOPE() and INTERCEPT as showb below. Compare these values to those we obtained when performig our regression analysis without using the builtin functions.

Now, plotting X-Y data is straightforward. Adding a trendline takes a little more work. We start off with the basic X-Y plot.

First, select the chart for editing by right-clicking anywhere inside the chart boundary. Now, before we add the trendline, we need to select the data series for editing. Move the cursor over one of the data points – you will see popup info about the nearest data point.

Left-click and the data series is now selected (below)

The Data Series dialog opens up. Select the Statistics tab (below). Select the Linear Regression curve and click OK

The modified X-Y plot with the newly added trend line.

Read More

Freezing row & column headers

For large spreadsheets that do not fit completely in the window, it is useful to be able to freeze the row and/or column headers to better view the data.

Consider the example below, where we have 12 columns and a large number of rows. By selecting the cell below and to the right of A1 and selecting Window – Freeze , you will now be able to scroll down or to the right while keeping the top row and left column frozen in place.

Note that once you close a sheet, the above setting disappears and you will need to refreeze your window when you reopen the sheet.

Read More

Charting: Creating a Gantt chart

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…

Read More

DataPilot 101

OOo Calc has a feature that is similar in functionality to the Excel pivot table. It is known as the DataPilot. A whole book could be written on this topic. We will just introduce the concept with a simple example.

Consider the table of data below. There are numerous ways we may want to analyse this da


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.

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Advanced Functions: INDIRECT

The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.

First, let us look at how INDIRECT works with the help of a few examples.

D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.

B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)

B12 =SUM(INDIRECT(“D”&B3;&”:D”&B4;)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM

B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT.ADDRESS takes row and column numbers as arguments – converting them to a string – just what INDIRECT needs.

B15 =SUM(INDIRECT(“Sheet”&B6;&”.”&B5;)) An indirect reference to another sheet in the same document.

The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.

Read More