ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Using Solver

If you want to carry out more complicated changes to obtain a certain result then the Solver add-in can be used.  Because Solver is an add-in it may not be installed on your computer at the moment.  If you cannot see the option Solver on the Tools drop-down menu then you will need to click on the Tools drop-down menu and choose Add-ins to install it.  Click in the box beside Solver Add-in as shown below and click OK.

If you are asked whether you want to install this add-in, click the Yes button.  If Excel is unable to find the source for the add-in you will need to contact the ICT Department for assistance.

When Solver has been installed you can click on the Tools drop-down menu and choose Solver, a window appears as shown below

Click on the icon to the right of the Set Target Cell box at the top of  and click on the cell containing your result value.  Now set the result you would like in this cell on the Equal to line, we are setting the Fuel cost cell to a value of 16000.

The next step is to select the cells you want to change; we have selected both the petrol cost and diesel cost cells.  We want to keep the difference between the cost of diesel and petrol at 2 pence, by clicking on the Add button in the Constraints section we can force Excel to keep our price difference cell value at 0.02.

The example below shows the found solution, the price of petrol is about 89p and the price of diesel is about 91p per litre.  The Solver now gives us the opportunity to keep these values or to restore the original values.

If you want to restore the original values but do not want to lose this solution you can save it as a Scenario.  To do this, click the Save Scenario button and enter a meaningful name for your scenario in the window shown below .

When you click OK to save the scenario you will be returned to the Solver window shown above and can set the bullet back to Restore Original Values.  See the page on scenarios for information on how you can view your saved results.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk