I do a lot with spreadsheets. And when I work in spreadsheets I work in OpenOffice. Most people know the ins and outs of spreadsheets, but many don’t realize just how powerful spreadsheets can be. Openoffice Calc holds a lot more bang for the users’ buck than you might think. For instance, did you know that Calc can do If/Else statements? In this spreadsheet tutorial you will learn how to use IF/ELSE statements in OpenOffice Calc.
Let’s say you have two columns of numbers and you want to add a third column based on the other two data. That third columns’ entry will depend upon the information in the first two. IF entry A is greater than B THEN C is X ELSE C is Y.
But how does one do this? It’s actually quite easy. Let’s start out with a more simple example and move on to a more complex example.
The first example will illustrate a basic IF/ELSE statement that uses only two columns of data. We’ll do a simple spreadsheet that deals with how many persons each employee will bring to a company picnic. To make things easy if the person enters “0″ that means they are not bringing a guest (the employee, however, IS required to come) and if they enter “1″ that means they are which means that employee equals two attendees. The first column will represent the name of the employee and the second column is how many guests the employee will bring. The formula to enter into the third column would look like:
=IF(B1=1;2;1)
What this will do is enter a “2″ in the third cell is the employee is bringing a guest and a 1 if they are not.
Now let’s make this a bit more complex. For this example we’ll have three columns. The first column is a number that represents how many hours an employee worked. The second column will represent how many hours over 40 they worked. The third column will represent how much overtime pay they made. But let’s say you also have to account for zero hours worked. The formula for the second column would look like this:
=IF(A9<=40;0; IF(A9>40;(A9-40)))
For the third column let’s continue with this idea. Say if an employee worked over 5 hours of overtime they would gain an extra 100 dollar bonus in their check. So we’ll add a fourth column for bonuses. For simplicity’s sake all employees make $10.00 an hour. Overtime is standard time and a half pay. So the forumla for the third column would be a standard:
=SUM((40*10)+(B9*15))
And the formula for the fourth column would be:
=IF(B9>=5;100;0)
Now for the final total for employee salary a fifth column would be added that would look like:
=SUM(C9+D9)
Final Thoughts
The examples may be very simple but the idea can be applied to many instances. Using IF/ElSE statements in OpenOffice Calc can quickly take your spreadsheets to a much higher level of usability.
Read Related Posts
-
How to create a database with OpenOffice Base
-
Creating forms to aid OpenOffice.org Base data entry
-
Installing new templates in OpenOffice 3
-
Open and edit PDF files in OpenOffice
-
Install Writers Tools for a much more powerful OpenOffice Writer
-
Use OpenOffice Impress for flash card learning
-
Openoffice 2.0 is here
-
How To Open Microsoft Excel Spreadsheets Without Excel
8 Responses to “If-Else Statements in OpenOffice Calc”
Trackbacks/Pingbacks
-
[...] link : If-else Statements in OpenOffice Calc Publié dans GNU/Linux. Mots-clefs : tips, OpenOffice, if/else. Laisser un commentaire [...]
Excel supports the same functionality.
This isn’t some unique function for OpenOffice Calc only as the title implies.
i was about to ask the same thing jojo answered.. :P
Excel does support it yes, there is a slight difference in the syntax though. I didn’t mean to imply OO was the only spreadsheet that supported it, but it is good for those wanting to migrate to know that it does support higher functions.
Boy this brings back memories. My first paying job when I was 16 years old was to create all of the budget spreadsheets in Lotus 1-2-3 for a regional chain of department stores. Using advanced functions in a spreadsheet is a bit like Karate – if you don’t practice and keep in good form, you’ll quickly lose a lot of the knowledge.
Can this be done with text cells. Im using Calc to do a budget spreadsheet & am putting in a sheet which tracks & will chart all electricity bills, for instance, through the year. Can you have an IF statement that says, for January, if A1=Electricity print the value in B1. Ive tried but couldnt get it to work.
Thanks
IF(A1=”Electricity”;B1)
I read you article, and mostly understand :)
I usually play in Photoshop…not spreadsheets help
I have a sheet that list
Employee / Score 1 / Score 2 / Score 3 / Score 4 /Score 5 / Tier
What i am trying to do, is
if (score 1) is 1.1 +
if (score 2) is 80 +
if (score 3) is 20 +
if (score 4) is 0
if (score 5) is empty (no content in cell)
then (Tier ) is 1
Then the 2nd Tier would be ranges
if (score 1) is 0.7 to 1.0
if (score 2) is 70 to 79
if (score 3) is 12 to 19
if (score 4) is 1-3
if (score 5) is X
then (Tier ) is 2
So each score would need to all meet the tiers requirements and the tier level would be auto generated.
is it do able… could you please tell me how :)
any help would be great
Thank you, I hope I gave enough info