goalSeek Method

Attempts to find a value for one cell that produces the desired formula result in another cell.

Syntax

Javascript (Usage)
Javascript (Specification)

var value; // Type: any
value = GC.Spread.Sheets.CalcEngine.goalSeek(changingSheet,
                                             changingRow,
                                             changingColumn,
                                             formulaSheet,
                                             formulaRow,
                                             formulaColumn,
                                             desiredResult);

function goalSeek( 
   changingSheet : Worksheet,
   changingRow : number,
   changingColumn : number,
   formulaSheet : Worksheet,
   formulaRow : number,
   formulaColumn : number,
   desiredResult : number
) : any;

Parameters

changingSheet: The sheet that contains the cell that you want to adjust.
changingRow: The row index of the cell that contains the value that you want to adjust.
changingColumn: The column index of the cell that contains the value that you want to adjust.
formulaSheet: The sheet that contains the formula that you want to resolve.
formulaRow: The row index of the cell that contains the formula that you want to resolve.
formulaColumn: The column index of the cell that contains the formula that you want to resolve.
desiredResult: The formula result that you want.

Example

Javascript

// This sample shows how to use the goal seek.
// Loan amount is 10000, term is 18 months and pay 600 each month, evaluate what interest rate you will need to secure in order to meet your loan goal.
sheet.setValue(0, 1, 10000); // Loan Amount
sheet.setValue(1, 1, 18); // Term in Months
sheet.setFormatter(2, 1, "0%"); // Interest Rate
sheet.setFormatter(3, 1, "0.00");
sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment
GC.Spread.Sheets.CalcEngine.GoalSeek(sheet, 2, 1, sheet, 3, 1, -600); // result in B3 is 10%

Reference

CalcEngine type