If-Else Statements in OpenOffice Calc - gHacks Tech News

If-Else Statements in OpenOffice Calc

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.

We need your help

Advertising revenue is falling fast across the Internet, and independently-run sites like Ghacks are hit hardest by it. The advertising model in its current form is coming to an end, and we have to find other ways to continue operating this site.

We are committed to keeping our content free and independent, which means no paywalls, no sponsored posts, no annoying ad formats or subscription fees.

If you like our content, and would like to help, please consider making a contribution:

Comments

  1. Jojo said on January 26, 2009 at 5:22 am
    Reply

    Excel supports the same functionality.

    This isn’t some unique function for OpenOffice Calc only as the title implies.

  2. marcelo said on January 26, 2009 at 6:30 am
    Reply

    i was about to ask the same thing jojo answered.. :P

  3. jack said on January 26, 2009 at 3:19 pm
    Reply

    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.

  4. RogueSpear said on January 27, 2009 at 5:08 pm
    Reply

    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.

  5. Dermot said on April 14, 2009 at 2:12 pm
    Reply

    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

  6. Hitchhiker said on June 28, 2009 at 8:41 am
    Reply

    IF(A1=”Electricity”;B1)

  7. Cozma said on July 26, 2009 at 6:03 pm
    Reply

    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

    1. Dae said on May 31, 2010 at 6:39 am
      Reply

      Cozma:
      Let’s assume that the titles you gave are in row 1, starting with A1, and ending with G1. Additionally, let’s assume you leave a space in row 2, and are working in A3:G3, just because that’s the way my mind worked when I thought of this for you.

      Now that we have that out of the way, what you need is a nested IF statement (An IF statement within an IF statement). For you, I came up with this:

      =IF(AND(B3>=1.1;C3>=80;D3>=20;E3=0;F3=””);1;IF(AND(0.7<=B3<=1;70<=C3<=79;12<=D3<=19;1<=E3<=3;F3="X");2;"Whatever you want all other results to be."))

      As you can see, I put that 'X' in Score 5 in as text, via " " marks. If you would like to make that a specific number, don't use the quotation marks. The same goes for the "Whatever…" value. For text, use quotes; for numbers, no quotes. I have tested this out with different values, and it works fine for me. I hope this was helpful. :-)

      1. Dae said on May 31, 2010 at 6:45 am
        Reply

        Since the line looks broken to me, I’m reposting with shorter lines, broken at the semicolons in the IF statements. And since I forgot to mention it, this all goes into G3 in my scenario:

        =IF(AND(B3>=1.1;C3>=80;D3>=20;E3=0;F3=””);
        1;
        IF(AND(0.7<=B3<=1;70<=C3<=79;12<=D3<=19;1<=E3<=3;F3="X");
        2;
        "Whatever you want all other results to be"))

  8. BigCat said on October 6, 2010 at 4:14 pm
    Reply

    I have used your examples to create a Calc sheet to compute various values and then plug them into a spit out information I need for my work. I have run into a problem, I am need to determine what range the values I just calculated are and then spit out a single number for each range. ie: for Values less than or equal to 1.0, 60. For values greater than 1.0 but less than or equal to 10.0, 45. And, for values greater than 10, 30.
    Now what I have is “=IF(CALC<=1;60;IF(1<CALC<=10;45;30))" and this seems to work, except that for values greater than 10 I still spit out 45. I have tried moving the final "else" (30) to the outer most set of (), but that doesn't seem to work either.
    How do I fix this?

    1. BigCat said on October 6, 2010 at 4:15 pm
      Reply

      sorry, “CALC” is the input field I am referencing.

      1. Dae said on October 10, 2010 at 10:40 am
        Reply

        In your internal IF statement, you have 1<CALC<=10. Remove '1<', as it is redundant and messing up the syntax. I have tested this and can confirm it to work. Also, be aware that this will also return 60 if CALC is blank. Any other questions about this, feel free to send to [email protected] and we can kill this old thread.

  9. BigCat said on October 10, 2010 at 5:57 pm
    Reply

    Thanks, I will give this a try…

  10. BigCat said on October 11, 2010 at 5:14 pm
    Reply

    Dae – THANK YOU!! Removing the “1<" and all the lower portions of the equality statemtents for all of my conditions worked!! I really appreciate it!

  11. Dae said on October 12, 2010 at 2:29 am
    Reply

    No problem. I might suggest adding a last condition, for when fields are blank, unless that doesn’t matter to you. In that case, you would have it display an error message or something when the CALC field is blank. The value for a blank cell is two double quotes in succession (“”).

  12. Niels Beekes (Aniforce) said on March 5, 2011 at 8:27 pm
    Reply

    I’ve been trying to do this for ages, but somehow it always gives me an error.

    if the number in E4 > 0, the number in D4 = 1
    if the number in E4 > 20, the number in D4 = 2
    if the number in E4 > 45, the number in D4 = 3
    if the number in E4 > 75, the number in D4 = 4
    if the number in E4 > 110, the number in D4 = 5
    if the number in E4 > 150, the number in D4 = 6

    etc. (The number keeps increasing with 20, plus 5 per step, I don’t mind if this is a manual input though).

    Anyone know how to do this?

    Cheers,

    Niels

  13. User said on March 30, 2011 at 4:28 pm
    Reply

    Finally I made it!!!

  14. Brent said on May 26, 2011 at 6:41 pm
    Reply

    Hello All, I’m very new to Calc and am trying to create a sheet that will have a column of dates, with two blank rows for weekend days. By following examples on this and other sites, I’ve come up with this:
    =IF(WEEKDAY(A9+1;2)<6;(A9+1);"")

    A9 has a date in it. When I use the auto feature to fill in the remaining column values, it displays the rest of the week, but includes Saturday, and then the rest of the column values are empty.

    Any help with this would be greatly appreciated.

    Thanks!

  15. Jepp said on October 11, 2011 at 7:43 pm
    Reply

    hi… can you do this?, if i would d10 would be 00:00 the E10 will put a text “absent”

  16. Jeff said on December 22, 2011 at 8:18 am
    Reply

    is there a way to use this if then statement to change the background color of the cell, I know you can use conditional formatting but that only works for three values, can’t i do something like =if(a1=n;cellcolor=red;cellcolor=white)
    and use multiple if thens for different values of n. thanks

  17. livan said on February 5, 2012 at 1:17 pm
    Reply

    Hi, I’m trying to do simple addition/substraction, but i’m kind of stuck with the formula.

    I have six coloumn, let’s just say coloumn A, B, C, D, E, & F
    Coloumn A consist of texts, and coloumn B is a number. Coloumn C, D, E, & F is where i want to put the formula, is a number type also.
    For example, The data in coloumn A is P, Q, R, & S. How do i enter the formula in coloumn C so that when the text in coloumn A is P, the value in coloumn C is the value from the previous row add/minus the value in coloumn B, else it’s the same value as previous row?
    For illustration:
    A B C D E F
    10 10 10 10
    p 1 11 10 10 10
    q 1 10 10 11 10
    r 1 10 9 11 10
    s 1 10 9 10 11

    Thank you so much for the help!

    1. livan said on February 5, 2012 at 1:25 pm
      Reply

      sorry, the spacing kinda mess, i’ll try to put the illustration again:
      —–A———-B———-C———-D———-E———-F—–
      -(blank)—(blank)—–10———10———10——–10—-
      —pppp——-1———-11——–10———10———10—-
      —qqqq——-1———-10——–10———11———10—-
      —rrrrrrr——-1———-10———-9———11———10—-
      —ssss——-1———-10———-9———10———11—-

      Thanks

  18. David said on August 24, 2012 at 11:12 am
    Reply

    Please help me to wright a formula of “If” in open office calc

    if the value is less than 59 it should type Inadequate,
    Equal to 60 and less than 79 it should type To be improved
    Above 80 and less than 100 should wright In line with expectation
    Equal to and Above 100 should wright above expectaton

    IF(J2100; “Above expectation”; J2>6080<100; "In line with Expectation")

  19. djr said on August 24, 2012 at 6:50 pm
    Reply

    This is a great forum and I think you might be the people to answer this question for me. I have a spreadsheet that lists 2 character country codes. I want to generate an if statement that evaluates these codes and values them. Here is an example:

    — E — F
    2- AU –
    3 – GB –
    4 – PL –

    IF(E2=’AU’,’GB’;1;0)

    I would like to be able to use a formula like that for each line to find how many “AU” and “GB” there are. I get an error when I try the above, how might I do that where I search for 1 of several character strings and return a value if one is found?

  20. Brian said on December 8, 2014 at 9:12 pm
    Reply

    I just wanted to say thank you to you folks. I’m always looking for help with calc formulas and functions, and today I was fortunate to come across your site here. I read the entire sheet, and I learned so much along the way reading your replies to others. I think this is a fantastic web-site for calc problems and now my favorite. Not all of us know these things, you guys are doing a really great job and a good thing by helping others. Thanks again. I’m also gonna sign up for your emails you send out.

  21. hannie said on February 1, 2015 at 4:02 pm
    Reply

    Hello i just need a little help from you guys how can i give nested formula to these
    If 74 below, it should appear “Needs Improvement
    75-80 becomes “Good”
    81-85 “satisfactory”
    86-90 “outstanding”
    91-95 “excellent”

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

Please note that your comment may not appear immediately after you post it.