LibreOffice Calc – Conditional Formatting

I haven’t used this feature of spreadsheet software as much as I probably should, but I have a spreadsheet I have been working with a lot lately and conditional formatting has been key to helping me orient myself in the spreadsheet. However, in the process, I realized that I am really a noob when it comes to conditional formatting. To help get myself up to speed, I created the following tutorial so I can reference it again in the future. (NOTE: I’m using LibreOffice 7.2.4.1 for this tutorial.)

Before I begin, what is conditional formatting? The basic idea is that you can change the look of a cell in a spreadsheet (or many cells) based on the contents of those cells (or other cells) without having to manually change the formatting of the cells. Instead, you “program” into the spreadsheet how you want cells to look assuming they meet specific criteria. Then, when you enter information into your spreadsheet, the cell formatting automatically updates to reflect the information in the cell.

Tutorial A – “Formula Is” Conditional Formatting

Let’s start with a very basic illustration. Let’s say I want to use a spreadsheet as a to-do list. Here’s how my spreadsheet looks, to begin with:

What I want to do is pretty simple. If I add an “x” to the Complete column for an item, I want that item to be crossed out. Effectively, I am going to change the font of the tasks to “strikethrough,” which crosses out the text. This way, I can quickly and easily discern which items are completed on my to-do list and which still need to be completed.

How do I set up the conditional formatting?

First, select the first cell (A2) that says “grocery shopping.” Then go up to “Format” on the menu and select “Conditional” -> “Condition.”

You’ll then get this window

An explanation of the window is in order. At the bottom, the range of cells to which the conditional formatting will apply is included. Obviously, this can be set to a range of cells or just a single cell. Right now, I have it applying to just a single cell, A2.

Where it says “Condition 1” is where you will build your condition statement. If you click on the drop-down arrow where it says “Cell value,” you’ll see a couple of additional options:

Those options are “Formula is” and “Date is.” This tutorial will cover two of the three options. For this one, we’re going to go with “Formula is.” The reason why we are going with “Formula is” is because we are going to use the information in a different cell, B2, to change the formatting in the current cell, A2. As the next tutorial will illustrate, you can also use the information in the same cell (e.g., A2) to change the formatting of that cell. So, select “Formula is.” You’ll notice that the window changes and you now just have a single box next to “Formula is” where you can enter your formula:

For our very simple to-do list, our formula will be pretty simple. We’re going to tell Calc to check to see if there is a lower-case “x” in B2. Here’s the formula: B2=”x”. (NOTE: If you want text or a character as part of your formula, it has to be inside quotes.) Here’s how it looks in the window:

Below that, we need to indicate how we want the formatting to change. If you click next to “Apply Style” where it says “Accent,” you’ll see a list of built-in formatting options:

Since we want to have the text crossed out and that isn’t one of the built-in options, we’re going to select “New Style”. We’ll then get this window:

We actually only need to change two things. First, name the style. Since we are going with “Strikethrough,” let’s call this “Strikethrough.”

Next, Strikethrough is a Font Effect, so we click on the Font Effects tab and look halfway down the screen to where it says “Strikethrough.” By default, it says “(Without).” Click on that drop-down and select “Single” (or “Double” if that’s your fancy).

When you’re done, click “OK” and you’ll be taken back to the original Conditional Formatting window.

That’s actually all we need to do for now. Click OK on that window, and let’s test our Conditional Formatting. To test it, type a lower case “x” in B2 and hit “Enter” to see if your formatting works.

Okay, one more thing and the first part of this tutorial is done. We applied that conditional formatting to just one cell, A2. But we want it to apply to all the cells in that list. How do we copy the formatting and apply it to the others instead of having to repeat the process above? Select A2 and right-click. You’ll get a context menu. Look for “Clone Formatting” and select it.

When you do, you’ll notice that the paintbrush icon on your toolbar is selected:

To apply the formatting you just cloned, select the cells you want to apply it to. Your cursor will change to a paint bucket. Click it over the selected cells and the conditional formatting will be applied to those cells. Now, test it. Add an “x” next to one of the other items to see if your conditional formatting is working:

Congratulations! You’ve successfully set up your first conditional formatting in LibreOffice Calc.

(NOTE: You can also apply the conditional formatting by indicating the cells to which the formatting should be applied in the Conditional Formatting window at the bottom. We indicated just A2 above, but you could include a range like A2:A6.)

Tutorial B – Conditional Formatting a Range with Colors

For this next tutorial, we’re going to get a little fancier. Nothing too crazy, but we are going to make the formatting conditional on the values in the cell and then apply background color to the cells. Our sample data are test scores.

First, let’s select the top test score in B2 and go to the “Format” menu, then select “Conditional Formatting” -> “Condition.”

We’ll get the same window as we got in Tutorial A. But things are going to be different from here on. We’re going to set up a color code that reflects test score ranges so we can quickly see which students are doing well and which may need extra help. To do this, in that window, leave “Cell value” as is and select the next dropdown. We’re looking for “is greater than or equal to”:

We want that option because the first condition we are going to enter is our top range of scores – 90 and up. Now, in the box next to that, enter 90. (NOTE: No need for quotes as this is a number, not text.)

Now, the style. We are going to apply a colored background based on these ranges. So, in the dropdown next to “Apply Style” select “New Style”:

Like we did above, we’re going to name our style, something like “grades_best”.

Next, click on the “background” tab then select “Color” and you’ll see this:

You can now pick the color you want to use as the background for that cell. I like green to mean that a student is doing well. So, I’m going with that:

When you’ve picked your color, select “OK.” That will return you to the conditions window. We have our first condition, but we wanted to assign colors based on ranges. That means we need additional conditions. In that window, click on “Add.”

When you click on it, you’ll get “Condition 2.” We’re going to follow what we just did for Condition 1, but we’re going to change a couple of things. First, in the dropdown next to “Cell value,” we don’t want “is greater than or equal to” anymore as our next range will have upper and lower values. So, we want “is between.”

Now, think carefully about how this will work. Our first Condition was equal to or greater than 90. We want this one to range from 70 to 89. Thus, will need to set the two values as 69 and 90 so it includes all the values we want but excludes 69 and 90. Once you have that, follow the same steps above to set the background color you want. I went with blue for doing okay and named that style “grades_good”:

We’re going to set up two more conditions. One is going to be from 60 to 69 and is going to be orange. The last is from 0 to 59 and is red! Like this:

When you have all your conditions in, click “OK.” The background color for B2 should now change to match the conditions you created. You can test it by changing the values in B2 to make sure everything works.

Now, as we did above, we can copy that conditional formatting and paste it to the other cells we want to have that formatting. Right-click B2 and select formatting then select the cells where you want to apply it and click with the paint bucket. All of those cells should now have the same conditions as B2.

There you go. That is how to use multiple conditions to format cells with a range of values.

Some Useful Notes

If you want to change the conditional formatting for a cell, be careful. If you right-click a cell with conditional formatting and select “Conditional Formatting” you’ll get this rather uninformative warning window:

In this version of LibreOffice, whether you select “Yes” or “No” doesn’t seem to matter, it wipes out the current formatting. So, don’t do that. Instead, select the cell you want to modify, then click on the “Format” menu at the top of the window and select “Conditional Formatting,” but don’t select “Condition”! Instead, click “Manage” and that will pull up the existing conditions. You can then modify the existing conditions from that window.

 789 total views,  8 views today

Connecting Linux Laptop and Android Phone to UoT_Secure

My university, The University of Tampa, is great in a lot of ways.  But one thing the university fails at quite miserably is in its support of anything other than Windows.  The IT department does okay in supporting Mac computers, but they don’t support Linux at all.  I had to get permission to wipe my laptop and install Linux on it.  I don’t have a problem managing all my own software and such, since I do that myself anyway and am quite happy that I don’t have to deal with all of the software IT puts on faculty computers (making them run super slow!).  But one area where the lack of Linux support is occasionally problematic is when it comes to logging onto the wireless networks on campus.

There are two wireless networks.  The first doesn’t use authentication to connect to the network, but you have to log in via a webpage once you connect to the network to access the internet.  That network is considered the “unsecure” network and the SSID for it is: “UoT”.  A faster and more reliable wireless network is available for faculty and staff as well.  The SSID for that one is: “UoT_Secure”.  The problem with the UoT_Secure network is that it is WPA protected with PEAP authentication.  The IT department provides information on how to connect to the UoT_Secure network for Windows and Mac computers, but not for Linux computers (or Android phones).  It isn’t that complicated, but figuring it out without any guidance can be a real pain since the process isn’t all that straightforward.  Since I have to make the necessary changes to my network settings every time I reformat my computer (about twice a year), I figured I’d put some instructions on here to remind myself how to do it and to help anyone else who may have this issue (though, apparently, I’m the only Linux user at The University of Tampa).

The first step is to find a spot on campus where you can connect to the UoT_Secure network (it’s in most places, but not everywhere).  Click on your network icon in your taskbar (or use whatever you do to access a list of available wireless networks) and make sure you see UoT_Secure.

I can see UoT_Secure as an available wi-fi network.
I can see UoT_Secure as an available wi-fi network.

Click on “Connect” and you’ll get a window that asks for more information. That window will let you edit the information for the connection.  To connect to the network, click on the Wi-Fi Security tab.  Where the  Authentication drop down menu is, select PEAP (Protected EAP).

This is the "edit" your network connection window.
This is the “edit” your network connection window.

 

Once you select that, you have to enter your UT credentials for logging into your email account.  You enter your username (without @ut.edu) in the “Username:” field and your password for your email account in the “Password:” field.  Then select “OK.”  Once you’ve done all of that, go back to your Network icon in the taskbar and select “Connect.”

Click "Connect" once you've updated your wi-fi settings.
Click “Connect” once you’ve updated your wi-fi settings.

Hopefully, it works!

Now, on to the cell phone.

UT has two wireless networks. Once is a secure network while the other is more of an open network that is a bit easier to access, but doesn’t provide permanent access. It still requires your UT username and password (for your email) that you enter in a web portal, whereas the UofT_Secure network does not require you to log into a web portal once you connect.

Here are the settings for my new Google Pixel Android phone:

Click on the network to connect.
Under EAP method select “PEAP”
Under Phase 2 authentication select “MSCHAPV2”
Under CA certificate, select “Do not validate”
For identity, enter your UT identity without @ut.edu
Leave anonymous identity blank.
Then enter your password and hit “connect”.
That should do it.

 1,209 total views,  1 views today

Ubuntu Linux (KDE): “Chrome didn’t shut down correctly” error

Every so often, Chrome on my Linux based computers (one running Kubuntu and one running Linux Mint KDE) starts having a problem.  I like having Chrome save my tabs from my previous sessions so I can pick back up where I left off.  But for some reason, and I’m not exactly sure what that reason is, Google’s Chrome eventually starts giving me the following error message:

Chrome didn't shut down correctly error
Chrome didn’t shut down correctly error

The error says, “Chrome didn’t shut down correctly” followed by a button that says “Restore.”  By clicking on the Restore button, I’m able to get my tabs back, but it’s kind of annoying that I have to do that.  Also, there is no indication of what the problem is in the Chrome crash log (chrome://crashes), which means I really have no idea what causes this problem.  I tried a bunch of suggestions from various websites to get this error to go away and finally found one that works.  Here’s what you need to do.

First, click on Chrome’s Settings option:

Chrome settings menu
Chrome settings menu

In the tab that opens up, scroll to the bottom and select “Show advanced settings…”

Chrome advanced settings
Chrome advanced settings

Near the bottom of the advanced settings is the option you want: System.  There should be two buttons there.  The first one says, “Continue running background apps when Google Chrome is closed.”  I only get the “Chrome didn’t shut down correctly” error when that button is selected.

Continue running background apps when Google Chrome is closed
Continue running background apps when Google Chrome is closed

Uncheck the box next to that option, like this:

Continue running background apps when Google Chrome is closed
Continue running background apps when Google Chrome is closed

Now try restarting Google Chrome.  If whatever is causing this is the same problem for you as it is for me, it should have solved the problem.  If not, sorry.  Keep googling for an answer.   🙁

 4,499 total views,  6 views today

Limesurvey: How to Randomly Assign Participants to Different Conditions (i.e., Experimental Designs)

I’ve used LimeSurvey for a long time and really like the software.  It’s powerful, yet very easy to use.  But one thing I couldn’t figure out with the software was how to assign participants to one of several conditions within a single survey.  For example, if you want to randomly assign participants in your survey to one of three groups and one of those groups will see some intervention, another group will see a different intervention, and the third group will see a control condition, as in an experimental design, I couldn’t figure out how to do that before.  Turns out, it is possible.  I found this tutorial that explained it, but it wasn’t all that clear, so I’m creating a tutorial here to make it very clear how this works.

What I wanted to do is like the hypothetical scenario I described above.  I am conducting a survey and have two experimental conditions; participants are going to read one of two vignettes that are designed to influence them in specific ways and one control condition in which the vignette is designed not to influence participants in any way.  I want to randomly assign every person who takes the survey to one of these three conditions so they only see one of the three vignettes.  Here’s how you do this.

First, you have to create a variable in LimeSurvey that randomly assigns each participant to one of the groups.  This should basically be the first variable in your survey.  But, the key is that you don’t let participants see the variable.  So, you add a new question to your first Question group:

limesurvey1

You can name that variable whatever you want (this goes in the Code: box), but I named mine “random.”

limesurvey2

Then, you need to choose the type of question.  Go down to where it says “Question type:” and select “Equation”:

limesurvey3

The equation itself goes in the “Question:” box, like this:

limesurvey4

The equation you use is the following:

{rand(1,3)}

What this equation tells LimeSurvey to do is to select a random number between 1 and 3 (the two values in the parentheses) and assign that to this particular participant (if you have more conditions, you can increase the number of groups by increasing the second integer – i.e., change 3 to 4, 5, 6, etc.).

You have to do one more thing before you’re done creating the question.  Go down to “Show advanced settings” and click on it.  It will open the advanced settings options.  Scroll down to where it says, “Always hide this question” and select “Yes” from the drop down menu.  This tells LimeSurvey that you don’t want participants to see this question. Once you’ve done that, click on “Save” and you’ve now created the variable that randomly assigns participants to one of the three conditions.

limesurvey5

You’ve finished the hard part.  Now you have to tell LimeSurvey which variables go with which randomly assigned numbers and their corresponding conditions.  You do this while creating the questions.  So, start creating the questions you want the participants in each of the groups or conditions to see (these should obviously be in a later question group than the above question, or the branching won’t work).  While creating the question, scroll down to where it says, “Relevance equation”.  In that box is where you’re going to add the necessary code to assign each question to a randomly assigned condition.  The code you’ll use is the following:

  • for those assigned to condition 1, add to that box: ((random==1))
  • for those assigned to condition 2, add to that box: ((random==2))
  • for those assigned to condition 3, add to that box: ((random==3))

It should look like this:

limesurvey7

Once you’ve added the Relevance equations and hit “Save”, now you can test the survey.  You should be randomly assigned to a different condition each time and see only the questions that meet the relevance equation criteria – meaning, if you were randomly assigned to condition 1, you’ll only see the questions assigned to that condition, ditto with condition 2 and condition 3.

And there you have it – you have created a survey that includes random assignment to different conditions.

 7,698 total views,  46 views today