/

IF Function in LibreOffice Calc – Basics and Examples

5114 views
7

IF function is one of the powerful in-cell function in LibreOffice Calc. And you can do literally anything with it, if you know the basics. Here’s how.

The basic is very simple. IF function uses conditions to determine results.  If the condition is met then one result is shown and if the condition is not met then another result is shown.

Here are some examples to help you understand.

IF Syntax

IF(Test, Then Value, Otherwise Value)

Simple IF Function

Below example checks whether the price is greater than $500 and based on that we fill High or Low in adjacent cell.

=IF(A2>500,"High","Low")
Simple IF function - example 1
Simple IF function – example 1

Below example also gives exact same result. Note that the operator is changed to less than sign (<) and arguments have been swapped.

=IF(A2<500,"Low","High")

Note:
You can use any comparison operators e.g. = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and <> (not equal to) in IF formula.

SEE ALSO:  SUMIF and SUMIFS Function with Examples in LibreOffice Calc

You can also use text/strings in IF statements with comparison operators. Remember to enclose the text or string using double quotes (“).

Below example checks whether each cell contains ‘Mango’ then it returns ‘Fruit’ in adjacent cell.

IF example with String
IF example with String

Nested IF

You can combine multiple IF statements for multiple conditions in Calc cell. The FALSE or the third operator of IF statement is replaced by another IF statement to evaluate another test or condition.

In the below example, if the score is 50, ‘Good’ is shown. If it is 100, then excellent – otherwise Invalid. You can note that all three possible test added in the single cell with two nested IF statements.

=IF(B2=50,"Good",IF(B2=100,"Excellent","Invalid"))
Nested IF example

Conclusion

This is the most simple way to work in Calc spreadsheet with conditions using IF. You can refer the official guide here.

Drop a comment below, if you have any questions.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.

Related


arindam

IT professional by profession and founder of libreofficehelp.com. Loving Linux and other technologies since 2002. I believe in open source and its philosophy. Follow me on Twitter or email me.

7 Comments

  1. hello. how can i compare two cells with date and time formatting to get the result in: morning or afternoon or evening.
    I know I should use if, but and don’t know the logical terms. If it would be only time, i think it will be simpler, but time is together with the date, even the last one is not important for the result.

    • You can easily do it via – below. Say you have two times in cell A2 and A3: 12AM and 12PM. And you want to check input in C1 as 11AM. The formula would be as below.

      “IF(AND(C1>A2,C1 A3),good morning,good afternoon)”

      You can improvise with your needs.

  2. This simple expression in a cell C1 of a new .ods document latest libre office 64 bit and one of its predecessors:

    =IF(A1>0, A1,0)

    (A1=1,B2=2,C1=expression)

    causes error 509 (missing operator)

    So condition is not working.
    I dont wanna know what detail or secret is wrong, such things should not fail.

  3. Sorry,
    the statement is in the user language.
    You can disable this in the settings or use if in your language, then it works.

  4. I want to spectify the value of a cell like this:
    IF(e3<g3,g3=e3)
    In other words: I want to set cell g3 to the value of cell e3, in case e3 is less than g3. How can I do that?
    Thanks for any hints
    H. Stoellinger

  5. Hello and thanks for the idea
    My genealogical problem is I have a list of 9000 dna ‘relatives’
    I want to identify those with a certain value over 90
    =IF(G5>=90,”cousin”)
    This does the trick, good
    However, how do I find these “cousin” values?
    If I do a find search it returns all the 9000 formula with cousin in them?
    Is there a better way to do this rather than endless scrolling and wrist ache 🙂
    Shay

    • You can refer to a Cell that contains “cousin”. =IF(G5>=90,”cousin”) can be changed with =IF(G5>=90,A1) where A1 will have cousin.

      Also, if you are running this in a single cell, you can drag the cell handle for entire sheet. That way it can work.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.