IF, Nested IF, AND, OR Criteria in LibreOffice Calc

In this tutorial you would learn the basics of powerful IF, nested IF, AND, OR conditions in LibreOffice Calc.

Summary

The IF Function is a logical test at your cell which returns TRUE when a condition is met; otherwise returns FALSE. Hence you can use it to return any different value in target cell based on your test. You can also extend your test using nested-IF condition clubbed with AND, OR operators.

Purpose

Logical test for a condition in any given cell.

Return Value

Returns your values based on TRUE or FALSE. If your return value is omitted, returns the string “TRUE”/”FALSE”.

Syntax

=IF(Test, value_when_true,value_when_false)

Examples

In the example below, when the temperature crosses 40 degree C, return Hot in the corresponding cell otherwise return Normal.

=IF(A2>40,"Hot","Normal")
Simple IF Example
Simple IF Example

Above example depicts a simple IF condition. Let’s see how you can do nested IF with AND condition.

To make it more complex, let’s add more conditions. When the temperature is below 15 deg C it is Cold; when between 15 and 45 deg C – Normal; Otherwise return Hot in the corresponding cell.

=IF(A2<15,"Cold",IF(AND(A2>=15,A2<45),"Normal","Hot"))
Nested IF Example
Nested IF Example

Above example contains two IF statement with a nested IF. The FALSE condition of the outer IF contains another IF statement. I.g. when the first condition is False, we check another IF for further condition.

SEE ALSO:  IF Function in LibreOffice Calc - Basics and Examples

The AND function returns TRUE when both the condition is true.

Usage Notes

  • Maximum number of levels in nestes IF is 42. (via this).

So, this is how you can use IF fucntion in Calc. Drop a note below if you have any questions.


Looking for something else?

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


Related

Support Us


Be an expert on LibreOffice.

Small and quality tutorials. Delivered. Thrice a week.


5 thoughts on “IF, Nested IF, AND, OR Criteria in LibreOffice Calc

  1. Hello all, being an old bloke I tend to forget things. I am trying to put a line graph in libre calc. I am recording daily temperatures (min. & max.) and each month I get the average temperature for the month. After a year, I wish to set a line graph with the average temps. on the Y axis then set the year on the X axis. This, of course goes over many years and I wish to set the years on the X axis. I can set the Y axis successfully, but I just cannot work out haw to get the years on the X axis. Hope someone might have a detailed clue to do this.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Back to top