Spreadsheet Awesome: Check Mark Totals

Here’s the problem, you want to use LibreOffice to do a simple registration for a small class, so you open it up and write a small table for the dates you want to record:

Spreadsheet showing simple registration form with several names and some checked off boxes

You use a simple X to record when someone was present and a simple dash – to record an absence. But the mood strikes you and you want to make it look a little more professional. So you load up the Character Map program and grab a tick character from the symbols available and paste it into all the marked in cells:

Character Map showing a pannel of symbols

OK so your spreadsheet looks nice, but your reviewer wants to know how many students were in each class total. this should be easy enough and you make a new row and add in the cell SUM formulas. But woe! it doesn’t work. Using characters in a spreadsheet doesn’t count because they’re not numbers:

Spreadsheet showing the new tick symbol and a new totals column incorrectly showing 0 in each cell.

So to fix the issue you use the search and replace to replace all your nice tick symbols with the number ‘1’ and to be consistent, all of the dashes to the number ‘0’. And it works, you have your totals; but this doesn’t look nice! So you decide to use the format cell option to figure it out:

Spreadsheet showing all tick symbols replaced with 1

This brings up the number formatting window. Here you can decide what the cell should look like given a certain value. Our values are ‘1’ and ‘0’, anything else is a problem, so we use the cell formatting code: [=1]"✔";[=0]-;[RED]"Error" which shows a tick when the cell is ‘1’, a dash when the cell is ‘0’ and a red coloured Error when the cell is anything else:

LibreOffice Format Cell Window showing new custom format typed in.

Now everything is formatted wonderfully and LibreOffice Calc has saved us from having to decide between an ugly or a useless spreadsheet, we can have both beauty and functionality!

Spreadsheet showing correct registration totals and nice tick symbols.

As a bit of extra curricular, I also created one for deciding if someone loves you:

Google Doc Mount

I was bored and made this video and this gtk gui for mounting google docs as a folder on your computer.

Anyway, please watch my video, mute the sound it’s nothing but static.

View Video on Blip

[1] PPA: ppa:doctormo/ppa
[2] Package: gdocs-mount-gtk

Now be warned, if you try this, remember that it’s not a supported piece of software, bugs aren’t likely to be fixed (unless you fix them of course) the code is available and it should work on Lucid. It won’t be ported to any other versions (at least by me) but it should be easy to recompile everything for other versions anyway.

I will answer questions here or by email. Your thoughts?

The Software Cooperative

I had the very good pleasure today of meeting with Joe Golden of the old Green Mountain Linux company up in Vermont. He expressed to me a strong desire to help people get in touch with Free and Open Source ideals and importantly get people to recognise the community efforts that go into making all this great software.

So I had a bit of a think, some of my in-laws up there took me to the local farmers market. It’s a great market if you’ve never been to Burlington town center, lots of fresh produce, cider, wine, bread, excellent stuff.

Well they’re involved with the Diggers Mirth farming cooperative where they all get involved and all get to share the rewards for their hard work. The food is even sold in the local supermarket.

So since people obviously value food cooperatives, why not explain the software that we write in those terms?

To experiment a bit and see what kind of results this could turn up, I’ve drafted a simple, alpha quality leaflet which could be used at markets such as these as well as other places such as libraries or whole food type supermarkets:

Flyer Image

Update: I’ve updated it to version 2.1, to fix a whole bunch of issues reported in my comments section.

Update: Download svg on deviantArt, click image for link through. also licence terms are specified.

OSS push in VT Schools and Local Gov

vtI know what the first reaction will be to the news I hear on the grape vine that VT are pushing for Open Source. VT isn’t a normal state, the people there are much more sensible than the rest of the country. After all they elected Bernie Sanders, a self declared socialist as Senator.

But I’d argue that schools up and down the USA are much like each other, facing the same problems and generally with the same kinds of pressures from parents, teachers, government and big businesses. So to see Burlington School District issue this information (as part of a general update) on Open Source was surprising:

Open Source:
This year we are beginning our large-scale testing of open source software. Both the City Council and the School Board have requested this as part of ways to save costs. In addition to our web services, which have been running open source for several years, we have moved our main storage and printing systems to an open source operatins system. On all replacement computers we are using Open Office instead of Microsoft Office, and Open Office is available on all district computers. Although this is a small step, it represents a saving of 10% per new computer, or about $20,000 this year. In addition, Open Office is freely available to any home that wants it, so all of our students can have it, which is a saving of over $200 per copy for the parents. Of course, the savings would be irrelevant if the software wasn’t useful, but Open Office has virtually all of the functionality of Word, Excel, and PowerPoint, and can open any of those Microsoft files (even docx). Stay tuned for more information on using OpenOffice.

If we read this right it means there is real local government preference for Open Source. Now right now they’re going into because of costs instead of control, but I think a taste of freedom tends to stick around once you’ve deployed a FOSS solution and it’ll be more difficult for Microsoft or even IBM, Novell etc to get back in without offering serious concessions.

I also like that they are informing people that students can take advantage of OpenOffice, because it’s free too and supports all the same formats. That’s very good news as it’s an aspect of Free Software in schools which is often overlooked (that what is taught can be taken home without pressuring poor students to buy expensive software).

I’ll keep my ears open for any other news, because they are looking at a whole bunch of things as part of the review.