Usually, you create a formula to calculate a result based upon existing data. LibreOffice Calc includes tools to help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.

What is Goal Seek

Let's examine a simple example. A student tracks his final exam grades in school using a spreadsheet shown in the image below. So far he has received all grades for each subject except the one marked with a question mark.

Currently the average grade is 62 but he needs at least a 65 to pass the class. What is the minimum score he must achieve in the Information Technology exam in order to pass the class? Instead of manually trying random values he can use Goal Seek to find out what grade he needs on the final exam to pass.

Using Goal Seek

Goal Seek tool need three parameters

  • The formula cell. This is the cell contains the formula we want to resolve
  • The target value. The required value that we want our formula to to resolve to (in our example 65)
  • The variable cell. The cell that contains the unknown data that we want to find a value that meets the requirements.

To use Goal Seek:

1. Fill the cell with the unknown data with a 0 (or any other value). The Average will change but ignore it.

Normally we would not need to do this but it seems to be a bug in LibreOffice Calc that crashes Goal Seek tool when the value cell is empty.

2. Select the cell containing the Formula (in our example the Average grade) and Choose Tools > Goal Seek from the main menu.

3. In the Goal Seek dialog window fill in the parameters. The Formula cell is already filled in. Type in the Target value and click the Variable cell to autofill the cell reference value in the dialog.

4. Goal Seek now calculates and displays the Result in another pop up dialog. Choose Yes to insert the result to the variable cell

Now the student knows that he must achieve a grade 84 or greater in the IT exam in order to pass the course.

There is no guarantee that Calc will be able to find a solution. If this happens, you will be notified in the Goal Seek Status dialogue box.

Last modified: Thursday, 11 January 2018, 8:17 PM