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:

9 thoughts on “Spreadsheet Awesome: Check Mark Totals

  1. Just use the function “COUNTIF” I just tested it and it works.

  2. I have issues with that “love” example.

    First, how is it possible for someone to love you on one day, then suddenly not love you the next day, then love you again the next-next day? Love doesn’t work that way.

    Second, what is the spreadsheet supposed to calculate? Does the “marry” symbol appear only when there are N or more love symbols in a row? In other words, you use a spreadsheet to identify potential marriage candidates based on the number of “love days”, whatever they mean. I’m afraid marriage doesn’t work that way either.

    Third, the “marry” icon is a combination of the female and male symbols, which is a subtle example of heteronormativity. I see that there are both male and female names in the first column, and the two-symbol marriage icon only appears next to male names. Therefore it appears that the author of the spreadsheet in this example is supposed to be female. But in that case, what would appear in the marriage column if the “love threshold” was exceeded for one of the female names?

    Update: As I’ve been pointed out, Tiffiny is not a male name. My bad. Still, that looks the icon even odder in context. A simple pair of interlocking rings would be better.

  3. Shawn: That’s a different feature and one I thought wasn’t as interesting as showing people how to change the way forms look, but not the data involved.

    After all, it’s now easier to do AVERAGE and other mathematical operations without delving into complex conditionals.

  4. LucidFox: This is the website for you http://www.overthinkingit.com/ 🙂 The example is a nonsense, fun example of something slightly more complex. It uses negative numbers (swords) and positive numbers (hearts). I could also point out that love has not much to do with romance, I can love my brother or friend quite easily. And to be fair, the marriage symbols are taken from the charmap too.

  5. So instead of addressing genuine concerns I raise about the example, you prefer to laugh it off and deny the existence of the problem. For the record, it’s not clever, it’s just smug and annoying.

  6. LucidFox: I refuse to remove my jokes from my blog pages, regardless of how bad the humour is or how hard they are to understand. Of course just to make sure I’ve asked several people about your concerns and each of them appreciates my humour and thought that you might have missed the joke.

  7. LucidFox: Of course it is, if you can’t laugh your heart will always be too fragile to have long lasting love. Laughter is the cure for heart break, the temper for malice and the ultimate communicator for understanding. Dismissing it is a worrying sign.

Comments are closed.