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