LibreOffice Tutorial – Part 3 – nested ‘IF’ and ‘AND’ function

This is the 3rd part of my LibreOffice Tutorial to learn about simple useful formula and a tiny bit of macro to make a simple daily agenda.

In the first part we looked at renaming a sheet, entered  some random data, formatted cells and started to build the logic of the agenda.

In the second part of this tutorial we wrote some time and if formulas.

---

So we have in A2 the following formula that we can drag down to match with the number of cells in column B:

=IF($A$1>B2,1,0)

In Column A, your formulas should look like these:

Row \ Colum	A	B	C	D
1	=TIME(HOUR(NOW()),MINUTE(NOW()),0)	Start	Ends	Activity
2	=IF($A$1>B2,1,0)	00:00:00	06:30:00	Zzzzzzz
3	=IF($A$1>B3,1,0)	06:30:00	07:30:00	Wake up
4	=IF($A$1>B4,1,0)	07:30:00	09:00:00	Breakfast and Children to School
5	=IF($A$1>B5,1,0)	09:00:00	15:00:00	Make a time schedule using LibreOffice!
6	=IF($A$1>B6,1,0)	15:00:00	21:00:00	Kids
7	=IF($A$1>B7,1,0)	21:00:00	22:00:00	Finally free!
8	=IF($A$1>B8,1,0)	22:00:00	24:00:00	Zzzzzzz

The results from the formula at this time of the day (21:58:00) for me are as follow:

Row / Column	A	B	C	D
1	21:58:00	Start	Ends	Activity
2	1	00:00:00	06:30:00	Zzzzzzz
3	1	06:30:00	07:30:00	Wake up
4	1	07:30:00	09:00:00	Breakfast and Children to School
5	1	09:00:00	15:00:00	Washing-up, laundry, sport, lunch, meditation.
6	1	15:00:00	21:00:00	Kids
7	1	21:00:00	22:00:00	Finally free!
8	0	22:00:00	24:00:00	Zzzzzzz

Only Cell B2(22:00:00) is greater than cell A1(21:58:00) but not for long…

So our formula (=IF($A$1>B2,1,0)) is only comparing if the current time stored in cell A1 is greater than the ‘Start’ time in B2.

It should also check that A1  is smaller than the ‘Ends’ cell C2 that stores the time when the activity in cell D2 ends.

The formula to check if A1 is smaller than C2 is very similar to the one we already have, we just need to use the ‘smaller’ sign instead of greater sign and C2 instead of B2:

=IF($A$1<C2,1,0)

And in order to test both greater and smaller we are going to do what is called a nested IF. It is simply an IF within another IF.

We can reuse our first IF formula to test if A1 is greater than B2 (=IF($A$1>B2,1,0)) but instead of the 1, which is what to display when the condition is true, we put the other IF that test if A1 is greater than C2!

This is what it look like:

=IF($A$1>B2,IF($A$1<C2,1,0),0)
The formula is doing the following:
If $A$1 is greater than B2 then If $A$1 is smaller than C2 then display 1 , else display 0.

There is actually another prettier way (I think) to do the same thing which is using a AND function in the condition of the if:
AND  function simply do a logical AND between 2 conditions (or more) with the following syntax: AND(condition 1 , condition 2)
If we apply AND to our logic we should use the following formula in A2:

=IF(AND($A$1>B2,$A$1<C2),1,0)
This time the formula goes like that:
If $A$1 is greater than B2 AND $A$1 is smaller than C2 then display 1 else display 0.
I like it because it is shorter and looks more like a real statement.

To make it simpler I also like an even shorter formula to achieve the same result, which does not involve any IF statement but use the fact that the AND function does return a result:

=AND($A$1>B2,$A$1<C2)
This formula using function AND is giving back a ‘TRUE’ if the two conditions are true and a ‘FALSE’ in any other situations. So to get a ‘TRUE’, conditions $A$1>B2 AND  $A$1<C2 have to be true indeed.
This is great because it is shorter to type and faster for LibreOffice to calculate (even if it does not really matter as we are not manipulating millions of rows of data).

Another way to get the same result is just doing the same formula with this time using the IF in both conditions within the AND without specifying any result to display:

=AND(IF($A$1>B2),IF($A$1<C2))

So now you can pick up your favourite formula between these 4!

When you write a formula (and the same goes for macro), ask yourself if some other people will have to look and understand your spreadsheets. You may even want yourself to look at your work later in your life and need to understand these formulas. My point is that it is OK to use a very short and effective formula like the simple AND() if you remember what it does, but it may be better to use the IF(AND()) function as it sounds and look a bit more like ‘human’ language and is easier to understand.

In the next part of this tutorial we will be looking at a very useful function called VLOOKUP to help us with finding the current activity in order to display.