Goal Seek is a powerful What-If Analysis tool that helps you find the input value needed to achieve a specific goal in a formula.
This demo demonstrates how to use the GC.Spread.Sheets.CalcEngine.goalSeek API to find the value needed to reach your target.
Example Scenarios
This demo includes two practical examples:
Example 1: Revenue Calculation
Unit Price (B3): Starting at $100
Units Sold (B4): 50 units
Total Revenue (B5): =B3*B4 (calculates to $5,000)
If you want to achieve a revenue of $10,000, Goal Seek can automatically determine what Unit Price or Units Sold value is needed.
Example 2: Loan Payment Calculation
Loan Amount (B9): $10,000
Term in Months (B10): 18 months
Interest Rate (B11): 5.00% (initial value)
Monthly Payment (B12): =-PMT(B11/12,B10,B9)
If you want a monthly payment of $600, Goal Seek can find the required interest rate (approximately 10%).
API
Method Signature
Parameters
Name
Type
Description
changingSheet
GC.Spread.Sheets.Worksheet
Worksheet that contains the cell to be adjusted.
changingRow
number
Zero-based row index of the cell to be adjusted.
changingColumn
number
Zero-based column index of the cell to be adjusted.
formulaSheet
GC.Spread.Sheets.Worksheet
Worksheet that contains the formula cell.
formulaRow
number
Zero-based row index of the formula cell.
formulaColumn
number
Zero-based column index of the formula cell.
desiredResult
number
Target value that the formula cell should achieve.
options
IGoalSeekOptions (optional)
Optional settings that control the goal seek iteration behavior and accuracy.
options.maximumIterations
number (optional)
Maximum number of iterations. Default: 200.
options.tolerance
number (optional)
Maximum acceptable difference between the formula result and the desired result. Default: 0.001.
options.callback
(info: IGoalSeekStepInfo) => boolean | void | Promise<boolean | void> (optional)
A callback invoked after each iteration. Receives the current trial solution and returns true to stop goal seek early or false/void to continue. When the callback returns a Promise, goal seek waits for that promise to settle before proceeding to the next iteration. The callback can be declared as an async function to perform asynchronous operations such as pausing for visualization.
Return Value
boolean: For synchronous calls (no incremental calculation and no asynchronous callback), indicates whether a solution was found.
Promise<boolean>: For incremental-calculation scenarios or when options.callback is provided, returns a promise that resolves to true if a solution is found, or false otherwise.
Sample
Notes
After calling this API, the changing cell is updated to the found value. If no solution is found, the original value of that cell is restored.
If a callback is provided, it is called after each iteration with the current step information. If it returns true (or resolves to true), goalSeek stops early and treats the current value as the final result.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
initSpread(spread);
};
function initSpread(spread) {
var sheet = spread.getSheet(0);
// Set up sample data
sheet.setValue(0, 0, 'Goal Seek Example');
sheet.getRange(0, 0, 1, 2).font('bold 14px Arial');
sheet.setValue(2, 0, 'Unit Price:');
sheet.setValue(2, 1, 100);
sheet.setFormatter(2, 1, '$#,##0.00');
sheet.setValue(3, 0, 'Units Sold:');
sheet.setValue(3, 1, 50);
sheet.setValue(4, 0, 'Total Revenue:');
sheet.setFormula(4, 1, '=B3*B4');
sheet.setFormatter(4, 1, '$#,##0.00');
sheet.getCell(4, 1).backColor('#e3f2fd');
sheet.setColumnWidth(0, 120);
sheet.setColumnWidth(1, 100);
// Add PMT example
sheet.setValue(6, 0, 'Loan Payment Example (PMT)');
sheet.getRange(6, 0, 1, 2).font('bold 14px Arial');
sheet.setValue(8, 0, 'Loan Amount:');
sheet.setValue(8, 1, 10000);
sheet.setFormatter(8, 1, '$#,##0.00');
sheet.setValue(9, 0, 'Term (Months):');
sheet.setValue(9, 1, 18);
sheet.setValue(10, 0, 'Interest Rate:');
sheet.setValue(10, 1, 0.05);
sheet.setFormatter(10, 1, '0.00%');
sheet.setValue(11, 0, 'Monthly Payment:');
sheet.setFormula(11, 1, '=-PMT(B11/12,B10,B9)');
sheet.setFormatter(11, 1, '$#,##0.00');
sheet.getCell(11, 1).backColor('#fff3cd');
// Add instructions
sheet.setValue(13, 0, 'Instructions:');
sheet.getCell(13, 0).font('bold 12px Arial');
sheet.setValue(14, 0, '1. Example 1: Use B5 as formula cell to find revenue target');
sheet.setValue(15, 0, '2. Example 2: Use B12 as formula cell with target 600');
sheet.setValue(16, 0, ' and B11 as variable to find required interest rate');
sheet.setValue(17, 0, '3. Enter your values and click "Run Goal Seek"');
// Goal Seek implementation
document.getElementById("runGoalSeek").addEventListener('click', function() {
var formulaCellRef = document.getElementById("formulaCell").value;
var targetValue = parseFloat(document.getElementById("targetValue").value);
var variableCellRef = document.getElementById("variableCell").value;
var maximumIterations = parseInt(document.getElementById("maximumIterations").value) || 100;
var tolerance = parseFloat(document.getElementById("tolerance").value) || 0.001;
var resultDiv = document.getElementById("result");
try {
var formulaCell = parseCellReference(sheet, formulaCellRef);
var variableCell = parseCellReference(sheet, variableCellRef);
var seekResult = GC.Spread.Sheets.CalcEngine.goalSeek(
variableCell.sheet, variableCell.row, variableCell.col,
formulaCell.sheet, formulaCell.row, formulaCell.col, targetValue,
{
maximumIterations: maximumIterations,
tolerance: tolerance
});
if (seekResult) {
resultDiv.innerHTML = '<div class="success">Goal Seek succeeded!<br/>Found value: ' + formulaCell.sheet.getValue(formulaCell.row, formulaCell.col).toFixed(2) + '</div>';
} else {
resultDiv.innerHTML = '<div class="error">Goal Seek could not find a solution.<br/>Try a different target value.</div>';
}
} catch (e) {
resultDiv.innerHTML = '<div class="error">Error: ' + e.message + '</div>';
}
});
}
function parseCellReference(sheet, address) {
var ranges = GC.Spread.Sheets.CalcEngine.formulaToRanges(sheet, address, 0, 0);
if (ranges && ranges.length === 1 && ranges[0].ranges.length === 1 && ranges[0].ranges[0].rowCount === 1 && ranges[0].ranges[0].colCount === 1) {
return {
sheet: sheet.getParent().getSheetFromName(ranges[0].sheetName),
row: ranges[0].ranges[0].row,
col: ranges[0].ranges[0].col
};
} else {
throw new Error('Invalid cell reference: ' + address);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<label>Set Formula Cell:</label>
<input type="text" id="formulaCell" value="B5" placeholder="e.g., B5" />
</div>
<div class="option-row">
<label>Target Value:</label>
<input type="number" id="targetValue" value="10000" placeholder="e.g., 10000" />
</div>
<div class="option-row">
<label>Variable Cell:</label>
<input type="text" id="variableCell" value="B3" placeholder="e.g., B3" />
</div>
<div class="option-row">
<label>Maximum Iterations:</label>
<input type="number" id="maximumIterations" value="100" placeholder="Default: 100" />
</div>
<div class="option-row">
<label>Tolerance:</label>
<input type="number" id="tolerance" value="0.001" step="0.0001" placeholder="Default: 0.001" />
</div>
<div class="option-row">
<input type="button" value="Run Goal Seek" id="runGoalSeek" />
</div>
<div class="option-row">
<label>Result:</label>
<div id="result" class="result-container"></div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height:100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
margin-bottom: 12px;
}
.option-row label {
display: block;
margin-bottom: 4px;
font-weight: bold;
font-size: 12px;
}
input[type=text],
input[type=number] {
width: 100%;
padding: 6px;
border: 1px solid #ccc;
border-radius: 3px;
box-sizing: border-box;
}
input[type=button] {
width: 100%;
padding: 8px 6px;
margin-bottom: 6px;
background: #007acc;
color: white;
border: none;
border-radius: 3px;
cursor: pointer;
font-weight: bold;
}
input[type=button]:hover {
background: #005a9e;
}
.result-container {
padding: 8px;
border-radius: 3px;
min-height: 40px;
}
.success {
background: #d4edda;
color: #155724;
padding: 8px;
border-radius: 3px;
}
.error {
background: #f8d7da;
color: #721c24;
padding: 8px;
border-radius: 3px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
