Using Conditional Statements in LibreOffice and OpenOffice.org Calc on Linux

Just under the surface of LibreOffice or OpenOffice.org Calc, you’ll find a ton of powerful features — many of which are often overlooked. In particular, Linux users are often unaware that they can use conditional statements to make life much easier. If you’re using Calc in LibreOffice or OpenOffice.org, read on to learn how to use IF/ELSE statements in your spreadsheets.

Spreadsheets are an integral part of the day to day business world. People use them for tracking products, time, employee information, payroll… the list goes on and on. And most office users are fairly confident in their skills with the spreadsheet. But many users don’t realize just how powerful the spreadsheet can be. In fact, like most tools in an office suite, users tend to only use a fraction of the ability. And of all the tools in an office suite, this is most evident in the spreadsheet.

LibreOffice Calc (and OpenOffice.org Calc) is no exception. One powerful feature in Calc is the IF/ELSE statement. This feature enables the spreadsheet user to create conditional statements where the results of a cell will be dependent upon the answer to another cell. Let me explain.

Let’s say there is a spreadsheet with two columns of numbers. The goal is to add a third column that is based on the comparison of the data in the individual cells of the other two columns. A statement might look like:

IF A is greater than B THEN C is X ELSE C is Y

So if A is greater than B the results of C will be X, otherwise the results of C will be Y.

Here’s an example using numbers following the above statement. The goal is if the number in Column A is greater than the number in Column B, then the resultant number in Column C will be 20, otherwise the resultant number will be 10.

But how can this be translated into a spreadsheet entry? Actually it’s not that difficult. The only trick is knowing the correct formula to use. Let’s use a very simple example first.

The structure of the IF/ELSE statement (for the purposes of LibreOffice Calc) will be =IF(A=B;C;D), which translates to IF A = B THEN C, ELSE D. Now, it’s time to put that to the test.

Starting with a fairly basic example, the IF/ELSE statement can be used for very basic employee wages. To be more specific, the following example shows how the IF/ELSE statement is used to list how much an employee earns, based on the number of years an employee has worked. If the employee has worked two or less years, the hourly wage is 10.00 per hour. If the employee has worked more than two years, the hourly wage is 15.00 per hour. That statement would look like:

=IF(B1<=2;10;20)

The above IF/ELSE statement only requires one row of data to work, but (in order to make sense) needs a second column to indicate the employee name. Figure 1 illustrates this IF/ELSE statement in action. As with any spreadsheet formula, it is only necessary to enter the formula for the first cell in the row or column and then copy the formula (by clicking and dragging) down the column (or across the row.)

Here is the formula for the basic IF/ELSE in action.

In the example in Figure 1, the formula would be entered in C2 and then copied down the column into C3 through C6.

Advanced IF/ELSE Statements

Of course the IF/ELSE statement isn’t very helpful if all that can be done with it is basic calculations. Fortunately, IF/ELSE statements can be more complex. In fact, the IF/ELSE statement can become a very complex tool that can be used for many, helpful purposes. Another example would be calculating overtime pay and bonus pay for individual employees. This IF/ELSE would require three columns:

• Column A: Number that represents how many hours an employee worked.
• Column B: Represents how many hours over 40 the employee worked.
• Column C: Represents how much overtime pay the employee is to be paid.

This IF/ELSE gets more complicated because it might be necessary to account for zero hours worked. This would be represented in the third column and in the fourth column. The first formula is used to calculate how many hours over 40 the employee worked. This formula looks like:

=IF(B2<=40;0; IF(B2>40;(B2-40)))

The third column formula is even more complex. To keep this as simple as possible, all employees are paid $10.00 per hour and over time is standard time and a half (or $15.00 per hour). The formula for calculating overtime pay looks like:

The IF/ELSE statement being used for more advanced purposes.

=IF(B2<40,B2*10,40*10)+(C2*15)

This formula has to take into consideration working less than 40 hours, otherwise anyone working less than forty hours gets paid more than they should. To make this even more complicated, add a fourth column can be added that awards employees a bonus of $50.00 if they work more than five hours of overtime. The formula for the fourth column looks like:

=IF(C2>=5;50+D2;0+D2)

Now you have five total columns:

• EMPLOYEE
• HOURS WORKED
• OVERTIME WORKED
• PAY
• PLUS BONUS

Figure 2 shows how these new formulas work.

Final Thoughts

The IF/ELSE statement can be used for many other scenarios. But as is illustrated in the examples above, careful consideration must be taken when designing spreadsheets that take advantage of this powerful feature. Not only must the formulas be carefully planned out, the usage of the formulas must be done wisely. But when designed properly, and executed exactly, the IF/ELSE statement can make your LibreOffice Calc spreadsheets work in ways you never thought possible.