Step by step guide for using goalseek and solver in spreadsheet

In this article, you will learn about the step by step guide for using goalseek and solver in electronic spreadsheet (Advanced). The learning outcome is analyzing data using scenarios and goal seek.

A spreadsheet software supports many features to analyze data and make a trustworthy decision according to results. If you missed the previous articles read from below given links:

    1. What is GoalSeek?
    2. Steps to apply GoalSeek in MS Excel
    3. GoalSeek in OO Calc
    4. Solver in OO Calc

Topics Covered

What is GoalSeek?

Goalseek is an advanced spreadsheet feature that allows us to provides the values for the target based input. Just decide the target value and you can select which cell should be changed in goalseek dialog box.

Steps to apply goal seek in MS Excel

To apply goalseek in MS excel worksheet, enter the data, and perform the calculations as you want. I have prepared the following data to perform goalseek in MS Excel.
Goalseek Data
GoalSeek Data
In the above screenshot, Rate and Quantity are given and the amount is computed accordingly. Now I want to achieve the target amount 500, so what should be rate can be or how many quantities required for the same, goalseek helps to do the same.
    1. Select the cell where the formula is written. For my example, I have placed the cursor in B4.
    2. Click on Data → What-if Analysis → Goal Seek option from the forecast group.
      Data - What if Analysis - GoalSeek
      Data – What if Analysis – GoalSeek
    3. A GoalSeek dialog box will appear as displayed in the following screenshot:
      GoalSeek in MS Excel 2019
      GoalSeek in MS Excel 2019
    4. Type the desired value i.e. 500 in To Value box and select the cell address for by changing cell option as displayed in the following screenshot.
      GoalSeek Input
      GoalSeek input
    5. Click on OK button the values will be displayed in your worksheet as displayed in the following screen shot.
      GoalSeek Output
    6. Click on OK button to accept the result, click cancel to return with original data.

Goal Seek in OO Calc

    1. Place the cursor in B4 cell and click on Tools → GoalSeek option.
    2. A Goal Seek dialog box appears as displayed in the following screenshot.
      GoalSeek dialog box in OO Calc
    3. Type the desired value for target value and select the variable cell to display the changed value. Observe this dialog box.
      Setting up GoalSkeet input in OO Calc
    4. Now click on OK button. A result will be displayed in a new dialog box. Observe the following screenshot.
    5. Click on Yes button to accept the value. The result will be displayed as below given screen shot.
      Final result GoalSeek in OO Calc

Solver in OO Calc

GoalSeek allows to display the target based results on single value or condition. Whereas the solver is used to optimize the result based on conditions. Follow the given steps to use solver:
    1. Place the cursor in the cell where formula is entered.
    2. Now Click on Tools → Solver.
      Solver Option in OO Calc
    3. Now solver dialog box will appear as displayed in the following screen shot.
      Solver dialog box in OO Calc
    4. Select the optimize result to option, select the by changing cells and provide the value in limiting conditions. Observe the following screen shot what I have done!
      Solver input and result optimization`
    5. Click on Keep result button to accept the changes otherwise click on restore previous.
I hope you enjoyed the reading. Thank you for reading the complete article. Share with your friends and feel free to ask your doubts in comment or like our social media pages for discussion.
Watch the complete video lesson on you tube from here:

Comment Your Views

%d bloggers like this: