Tag: libreoffice

Jonas Salk Middle School Career Day

A presentation about information technology with demonstrations.

I volunteered to create a presentation for career day at school. Actually, my daughter asked me and I said “okay”. Then career day presentations were changed from in person to online because of corona virus.

It would have been so much easier for me to do in person. I’m certain the total time spent would be less than what I needed to produce the video! Everything I wanted to present could have been done live. Timing would be easier and adjustments could be made in each session depending the interest of the previous audience and questions during the presentation.

That wasn’t to be.

The good thing about the video is I was able to produce it. The bad things are obvious in review. There are several parts where the dialog is disjointed and not flowing with events on the screen. Arrangement of some screen elements blocks others in an undesirable way. And I need to think more of the audience. This is likely much better for high school seniors than eighth graders. Work more on the script and be EXPRESSIVE!

Making this video was an enjoyable and challenging experience. I had to learn things I’d never known to make the video. And watching myself and the content I can see how it could easily be improved. Information I’ll tuck away to use if and when there’s a next time.

If you’d like to check out the video, here it is.

At the end of the video is a list of the software used to produce it. That same list, with working links, is below.

Ubuntu 18.08 runs the laptop used to create this video (it’s an alternative to Windows, OS X, and Chrome OS). https://ubuntu.com/

OpenShot video editor was used to create the video. https://www.openshot.org/

vokoscreen made the screen video captures that got edited in OpenShot. https://linuxecke.volkoh.de/vokoscreen/vokoscreen.html

GIMP, GNU Image Manipulation Program, was used to create or edit some of the images in the video and to obscure and alter some portions of the video images. https://www.gimp.org/

Cheese was used to record my head shot and voice.
https://wiki.gnome.org/Apps/Cheese

Pick and OpenShot’s chroma key effect were used make the background behind my head transparent rather than appear in a box that blocked the background. https://www.kryogenix.org/code/pick/

I used LibreOffice Writer to take notes and make plans as I developed the video and for the scripts I used to guide narration. LibreOffice Calc helped calculating how to adjust length of some clips to fit the target time. https://www.libreoffice.org/

Technical support

Technical support. Not quite “Vanilla Sky” level stuff but still thought provoking.

I provide some limited technical support on Apache OpenOffice and LibreOffice forums.

Why not Microsoft Office? Because that’s what I do at work. At home I use, and have used for many years, LibreOffice and OpenOffice. Why? Because they save me money, the support forums are generally more congenial and providing support lets me give back a bit for the value I get.

One of the things I really enjoy about providing support is seeing all the ways people are using software to do things they need to do. Things I would never have conceived of. It is a real eye opener to get a handle on someone else’s requirement. And then very gratifying to help someone meet their need.

One of the recent support requests was for a bowling league score sheet kept in Apache OpenOffice. The requestor wished to have the latest match value always be shown in a particular cell. The league has 32 weeks in its season and for each week the bowler’s score is entered for each of three games. The game total and average are calculated and displayed for each week.

The latest week’s match value always needs to show in a particular cell. The method being used was to update the cell manually each time a new week’s scores were entered.

I came up with a solution, posted it. Then, as I often do, rethought the whole idea. What I realized was the way I designed the solution it would always show the value for the 36th week, the last week of the season, rather than the most recently entered week! Not good.

After some deeper inspection I recognized this happened because the formula to show the score for the latest week actually just checked to see if there was anything in the referenced cell. And it counted a formula in the cell as something, even if the formula displayed nothing.

This needed to be fixed! I couldn’t provide a solution that didn’t work.

After some thinking I realized a formula to show the latest value needed to recognize whether the formula on each row displaying the value was showing a numeric value, a blank “”, or a label “DNB”. DNB, Did Not Bowl, was a label indicating the bowler hadn’t bowled that week.

Again, the season is 36 weeks. Scores are entered week by week. This meant the rows showing score and average were always followed by rows showing nothing. Even though cells in the rows contained formulas, the formula result was “” for each week after the latest one entered.

My original formula was detecting a formula in a cell as something. I needed to come up with a formula that could identify the row before the first row with formula result “”. Finally an idea struck. I could use a function that counted the rows where the formula result is “”. A blank. These are always the weeks of the season that are not yet played. The season is 36 weeks. Subtract the number of blank rows from 36 and that’s the last row with a score. Problem solved!

That formula is…

=INDEX($Sheet1.G6:G41,36-COUNTBLANK($G$6:$G$41),1)

With that part of the problem solved I saw that some formulas I hadn’t touched were returning #VALUE! errors. These were formulas that calculated total pins week by week. These errors were happening because of changes I’d made to the formulas to sum the pins for each week and to produce the pin average for each week.

My fixes created the problem so I was determined to resolve it.

I created what I call a “dynamic formula”. A formula that changes based on where it is in the sheet or what it reads from a value elsewhere in the sheet. It didn’t work. It has been some time since I created such a formula in OpenOffice or LibreOffice.

There’s quite a bit of compatible functionality between Microsoft Office, LibreOffice and OpenOffice. For the most part spreadsheets created in one work in the other without modification. For the most part.

As it turned out, I was creating the “dynamic formula” as if writing it in Excel. Dynamic formulas are one of the things that are a bit different between the Microsoft and OpenOffice/LibreOffice spreadsheets. Once I recognized that, I was on the way to developing a solution.

The below dynamic formula, “the solution”, totals values in a column beginning at a specific row and continuing to the row the formula is in.

=IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")

The formula needs to calculate a sum from a fixed starting row to whatever row the formula happens to be in. And in the case of the bowling league it needs to do that for thirty-six rows. If the formula couldn’t tell which row it was in and sum from the first row to the formula’s row then thirty-six different formulas would need to be entered. One for each row.

Entering the same formula in thirty-six rows is much easier in my opinion. And easier to maintain and easier to modify.

By tackling this person’s question I:

  • Helped solve a problem
  • Familiarized (again) with the difference between Calc and Excel dynamic formulas
  • Learned about a process, a “functional requirement”, I wasn’t familiar with and provided a way to support it

For me, this was a win all around. What could be better than the warm glow of finding the solution to a previously unknown use case?