Calculating Backwards in Excel

When you need numerous input values to achieve a very

specific end result.  In such a scenario, since the value of the end result

is to be set to a specific value, the input value needs to be altered to arrive

at that result.  In Excel, one can use the Goal Seek function to arrive at

a certain end result.  However, the two drawbacks of using Goal Seek are:

it can vary only one input variable at a time and it can't be copied across

cells. Let's first discuss the Goal Seek function and then move on to a

workaround to solve the second problem mentioned above by using the Solver tool.

Goal seeking



The Goal Seek function, available under Tools, allows the user to back

calculate the input or assumption used to arrive at a particular result.

Remember the following things about the Goal Seek function. The input in the

'To value' box has to be hard coded, ie it can't be linked to any cell.

The input in the 'By changing cell' box can be one cell reference only, ie

one cannot specify $C$4,$C$3 or $C$4:$C$3.  Only one of $C$4 or $C$3 can be

put in this box.  One can revert to the original value by using the undo

command.If similar data on price and quantity sold is available in D3:D4, Goal

Seek can't be copied even if the end result of both C6 and D6 has to be the

same.

Multiple goal seeking



Let's now look at a workaround to the problem of not being able to copy

Goal Seek across various cells (refer to screenshots 3 & 4).  For this,

install the Solver tool from Tools>Add Ins.  Next select Solver Add-in

and then click on OK. The constraints mentioned in the caption for screenshots 3

& 4 are applied to avoid a case where the sum of differences ($E$12) is 0

but the individual cells in range $C$10:$E$10 are not.

Clicking on Solve will give the Solver Results and the result in the spreadsheet.  You may now choose Solver

solution or restore original values.  But once you choose the Solver

solution, you can't Undo to get the original one.

Scenario: the user wants the total amount for a certain quantity of a product sold to be Rs 50 and would like to assess its likely impact on its price per unit
In row 8, give the value desired in row 6; row 10 has the difference of values in rows 6 and 8. E12 gives the sum of differences.For Solver, set $E$12 to 0; change cells $C$4:$E$4 subject to the constraints that $C$10, $D$10 and $E$10 are 0

Ashish Mathur, Business Analyst