LibreOffice Tutorial – Part 9 – ISERROR

This is the part number 9 of my LibreOffice Tutorial to learn about simple useful formulas 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.

In the third part of this tutorial we learned about nested IF and AND function.

In the fourth part of this tutorial we learned about VLOOKUP.

In the 5th part we looked at how to create and use a ‘named range’.

In the 6th part, we learned the INDEX function.

In the 7th part, we learned how to code a (very) simple LibreOffice Macro.

In the 8th part, we used the MROUND function to round the time.

We are now nearly at the end of this LibreOffice tutorial series, we are going to do some debugging and to learn about the ISERROR and NOT functions.


If like me you are still doing some work late in the evening, you may notice that your Activity schedule as it is now (if you followed the tutorial) has an error when we have already reached the last activity of the day. You may get the following error code:

22:14:32
Kids 15:00:00 21:00:00
House Chores 21:00:00 22:00:00
Finally free! 22:00:00 23:00:00
Zzzzzzz 23:00:00 24:00:00
Err:502 Err:502 Err:502

But What is Err:502?

This error code is returned when there is an INVALID ARGUMENT in the function.

(list of useful error codes in LibreOffice : https://help.libreoffice.org/Calc/Error_Codes_in_Calc)

In this tutorial as well as the previous one, to be able to reproduce this error at any time of the day, we can ‘simulate’ the current time by entering a time manually instead of having a function giving us the current time.

First copy the formula that is in cell A1 in sheet ‘planner’, and paste it in another empty cell to keep it safe (the formula has no reference to any other cell so it can go anywhere), and then write in A1 23:00:00 instead of the formula. Remember to copy the right formula back in A1 once you have done your testing.

That should trigger the error code as above.

Let’s analyse this error.

So in in the function in cell C10 I have:

=INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,4)

Our INDEX() function in the formula has 3 arguments:

planner_daddy:

This is the reference to the range where the function is looking at. We are using the named range that we defined (see part 5) in sheet planner to hold the activities, their starting time, their ending time and their order number.

VLOOKUP(1,planner_daddy,5,)+2:
This is the row index to look at in the range defined above (1 would mean that we are looking in the first row of ‘planner_daddy‘ range of cells). We are defining that row as being the addition of the result of a VLOOKUP function call and the number 2. Number 2 is because we wanted to return to activity that is after the activity that is after the current activity, so 2 rows below the row that is returned by the VLOOKUP function. Indeed the VLOOKUP function (explained in part 4) returns the current activity ‘order’.

4:
This is the Column index at in the range ‘planner_daddy‘. We defined it as being number 4 because this where the activity name is.  (you can find here a useful

It is useful to use the formula wizzard (button ‘fx’ at the left of the formula bar) to understand how the formula in a selected cell is calculated and therefore what is causing the error to happen:

We can see that the VLOOKUP function is returning 8. So in our 2nd argument of the function INDEX, the row number calculated is:

8+2=10

So our INDEX() function is trying to return from ‘planner_daddy‘ the content of the cell at Column 4 and Row 10…

OOPS, we only have 8 rows in planner_daddy.

So the error is happening because we want to display an activity that have a row number outside of our range.

To solve this problem there are a few different solutions:

You could include a IF statement in the 2nd argument of INDEX() that checks when the row number is greater than the number of rows in the range of cells for example, and display that last row instead. That mean that we would need to check what is the number of row in the range, which then requires even more logic building if we want to make the formula dynamic (formula that doesn’t need to be changed when then range changes), or to enter the manually the number of Row if we think the range will remain the same size.

So I prefer a solution that is close to Exception Handling in programming. It is to check if the formula is returning an error, and to choose what to display when it is returning an error.

To do that, let me introduce you to function ISERROR().

The ISERROR() has only one argument It takes as argument a cell reference or a formula and checks if that cell or that formula is returning an error, like ERR:502.

Used within an IF() functionwe can decide what to do when the formula we use to check next activities returns an error. Let’s say we want to simply havean empty cell if there is no activity coming after.

The following formula will do exactly what we want:

=IF((ISERROR(INDEX(planner_g,VLOOKUP(1,planner_g,5,)+2,4))),””,INDEX(planner_g,VLOOKUP(1,planner_g,5,)+2,4))

 

Let me explain what the the formula does:

IF function INDEX(Planner_G,VLOOKUP(1,Planner_G,5,)+2,4) returns an error then display the empty string “”, else display INDEX(Planner_G,VLOOKUP(1,Planner_G,5,)+2,4).

You can also use the logic the other way around. Sometime it is better to use the statement ‘If the formula returns NO error then do this, else do that’. It might look a bit more complicated but in some cases it is easier to manage by keeping at the end of the formula what we want to do if there is an error. For example if we wanted to introduce more nested IF statements.

So to do the opposite statement, we can use the NOT() function that is returning the opposite of a boolean. So NOT(TRUE) returns FALSE and NOT(FALSE) returns TRUE.

This is what we get in cell C10:

=IF(NOT(ISERROR(INDEX(Planner_G,VLOOKUP(1,Planner_G,5,)+2,4))),INDEX(Planner_G,VLOOKUP(1,Planner_G,5,)+2,4),””)

 

Make sure you have the correct number of parenthesis or your formula will not work properly.

In our spreadsheet and in our logic it is easier to use the ISERROR rather than the NOT(ISERROR) as you will see later.

In C9 we can use the same formula but instead of +2, we put +1.

Then we can use the same logic for cells D9,D10 and E9,E10. However in these cells you will need to change the number 4 (column reference in the range) to 2 (column 2 of the named range planner_daddy is “Starts”) in D9 and D10, and 3 (column 3 of the named range planner_daddy is Ends) in E9 and E10.

The same error can happen if you are an early bird, wake up very early, switch on you computer and the agenda when it’s the very first activity of the day.

So we can also add the ISERROR logic to our cells in C6,E6,D6 and C7,E7,D7.

A BUG???

While I was testing I realised that an odd behaviour of the Function INDEX() happen when its second argument’s value is equal to zero:

When the formula and the reference where we are looking (planner_daddy) are not in the same sheet the formula does not return an error,it seems to return the value that was there before… The odd things is that if we copy this same formula in an empty cell that was empty all the time the error is produced…

SO I thought I found a bug and that I should raise it to the LibreOffice Team through BugZilla. But I was wrong!

When you put a zero in the argument for row (and it’s the same for column), the function uses the row of the current cell, to the cell where the function is entered.

So typically in our case when we are at the activity 1 or 2 of the day we can get a zero and although there is no row zero in planner_daddy, the function index() does not returns an error code and do returns a value from planner_daddy. The way to avoid that is to add another if clause to check if the result returned by the VLOOKUP()-2 and VLOOKUP()-1 is equal to zero.

So the formula in C7 is:

=IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4))),””,IF((VLOOKUP(1,planner_daddy,5,)-2)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4)))

The only difference is that the last argument that was:

,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4)

is now:

,IF((VLOOKUP(1,planner_daddy,5,)-2)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4))

 

And now it works perfectly.

As the formulas are now very long I am showing them column by column here, so in our “Now!” sheet we have:

Column C:

Row/Column C
6 =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
7
8 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4))),””,IF((VLOOKUP(1,planner_daddy,5,)-2)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4)))
9 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,4))),””,IF((VLOOKUP(1,planner_daddy,5,)-1)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,4)))
10 =VLOOKUP(1,planner_daddy,4,)
11 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,4))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,4))
12 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,4))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,4))

 

In column D:

Row/Column D
8 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,2))),””,IF((VLOOKUP(1,planner_daddy,5,)-2)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,2)))
9 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,2))),””,IF((VLOOKUP(1,planner_daddy,5,)-1)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,2)))
10 =VLOOKUP(1,planner_daddy,2,)
11 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,2))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,2))
12 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,2))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,2))

 

In column E:

Row/Column E
8 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,3))),””,IF((VLOOKUP(1,planner_daddy,5,)-2)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,3)))
9 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,3))),””,IF((VLOOKUP(1,planner_daddy,5,)-1)=0,””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,3)))
10 =VLOOKUP(1,planner_daddy,3,)
11 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,3))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,3))
12 =IF((ISERROR(INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,3))),””,INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,3))

 

Remember to re-assign the re-calc macro to the sheet event ‘Selection change’ as seen in part 7.

Also remember to paste back the correct formula in sheet ‘planner’ in A1 once you have finish testing (as per recommendation at the start of this part).

Next and final part of this tutorial we will wrap-up and make the document look much better with some cosmetic changes!

4 Comments

  1. Nukool Chompuparn

    The structure box is very narrow and is not expandable. It’s not convenient for viewing complicated formula. I have reported and LO team will apply in LibreOffice version 6.3.2.

    https://bugs.documentfoundation.org/show_bug.cgi?id=126911

    Reply
    1. gweno (Post author)

      Awesome, thanks for raising that to LO team.

      Reply
      1. Nukool Chompuparn

        Good news! LO 6.3.2 has been released a few hours ago.

        Reply
        1. gweno (Post author)

          Cool. I will wait until it’s been live for a little while before upgrading I think. Thanks for your comment.

          Reply

Leave a Comment

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.