In this tutorial, you will learn the basics of powerful IF, nested IF, AND, OR conditions in LibreOffice Calc.
Table of Contents
Summary
The IF Function is a logical test at your cell which returns TRUE when a condition is met; otherwise, it returns FALSE. Hence, you can use it to return any different value in the target cell based on your test. You can also extend your test using nested-IF conditions 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, return the string “TRUE”/”FALSE”.
Syntax
=IF(Test, value_when_true,value_when_false)
Examples
In the example below, when the temperature crosses 40 degrees C, return Hot in the corresponding cell otherwise, return Normal.
=IF(A2>40,"Hot","Normal")
The 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"))
The above example contains two IF statements 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.
The AND function returns TRUE when both conditions are true.
Usage Notes
- The maximum number of levels in nested IF is 42. (ref this).
So, this is how you can use the IF function in Calc. Drop a note below if you have any questions.
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.
Include a row with the year as column headers and set the chart characteristics to include the first row as headers try 2017 Jan, 2017 Feb, 2017 Mar…….. or 2017 Jan, Feb, Mar…..Dec, 2018 Jan……
Set the data as Rows. and the Y axis to AUTO. Libre will then scale the y Axis to suit the temperature range.
Do you have any sample sheet which you can share?
Is there a maximum number of if statements that can be nested? if yes, what?
“The maximum number of nested expression levels is 42, no matter if IF() or other functions”, which includes “And” criteria:
https://ask.libreoffice.org/en/question/238242/max-number-of-nested-if-statements-in-calc/
Thank you. Updated as usage notes.
The AND() Function in this example is superfluous. If the first IF() -15 the TRUE condition is executed and the function exits. Then it’s only necessary to test for one of the remaining conditions with another IF() with the appropriate TRUE or FALSE response.
IF(<15,"Cold",If(<45,"Normal","Hot")
How about using different functions in the same statement such as IF & AND & OR? I am having a problem resolving this statement:
If A1 is greater than 0 and A1 is less than 250, or B1 is greater than 0 and B1 is less than 250, then 25, else 0.
Either A1 or B1 must be between 1 and 250 in order to return a value of 25.
Also, if either A1 or B1 is blank, 0, or greater than 250 then the result must be 0.
Please help!
Thanks!
Use this:
=IF(AND(A1>=1,A1<=250),25,0)Any other thoughts to my reply below??
Try this:
=IF(AND(A1>0,A1<250,B1>0,B1<250),25,IF(AND(B1>0,B1<250),25,0))It seems working as per your logic in my test.
YESSS! Thank you! I thought for sure there needed to have an “OR” function present!
Thank you, but not quite. I already have this solution. There also needs to have an OR function present. I need to compare A1 OR B1 with a result in C1. If A1 is greater than 0 and A1 is less than 250, or B1 is greater than 0 and B1 is less than 250, then 25, else 0 (C1).
A B C
Shipping units Receiving units Bonus
1 251 0 $0.00
2 0 249 $25.00
3 251 249 $25.00
4 251 251 $0.00
5 249 249 $25.00
A maximum of $25 bonus is given in any scenario. In this example, on day 1, greater than 250 units were shipped and no units were received, therefore no bonus. However on day 5 less than 250 units were shipped and less than 250 units received therefore a max bonus of $25. A single $25 bonus can only be given even if both shipped and received are less than 250.
I hope this clarifies it a bit more.
Sorry, my macro skills are horrible! 🙂
Thanks again!
I want to nest a isblank with an If statement
If A2 is equal to T(check to see if B2 is blank, if it is return C2, if not return B2.) otherwise ” ” nothing.
IfA2=T,IF(isblank(B2),C2,B2),” “)
this is the best I could come up with but it’s not working.
Any help would be greatly appreciated.
Its worked for me.
=IF(A2=”T”,IF(ISBLANK(B2),C2,B2))
Make sure you put T in the quotation in the IF condition. Also make sure your B2 doesn’t contain spaces.
Hello,
I have a question regarding IF. Recently I’ve been tasked with cleaning up some formulas. I found that the logic used in some is:
=IF(A2=A21)
Which is to say that if A2 is equal to A21 then True. However, can’t this be written as:
=A2=A21
Yes, you can write with brackets: =(A2=A21)
I’m having issues with it no matter what I do the nested IF is having issues when it goes over 7 nested IF’s and I don’t know why it’s not a error at first it just put the wrong value when over 7 nested IF’s but now after I fixed that it just outputs as “False” I isolated parts of the code and then those parts all worked fine I’m so confused why it won’t work there must be something I’m missing. Any and all help would be appreciated thx 😉
That is a complex IF-ELSE. I would suggest you to break it down in multiple columns or sections.
Ah sorry, does this help? Also for a little more detail, I basically having it first check if the first If command is equal to the lookup and if it is, then it checks if one of the other 6 are equal but if it isn’t, it goes to the next IF, at least that is what is supposed to happen, before that I had it check two IF’s at once with “AND” but that took a lot more code and wouldn’t work right anyway.