Goal Seek SpreadsheetGear API Sample

Range Samples Values and Formulas Goal Seek

Description

Achieve a specific goal from a calculated result using a simple iterative linear search. In this sample we find the loan amount based on a desired payment by entering the desired payment, interest rate and term.

Source Code

// Create a new workbook and get a reference to the active sheet and its cells.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(@"Files\GoalSeek.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Get a reference to the target cell.
SpreadsheetGear.IRange targetCell = worksheet.Cells["TargetCell"];

// Get a reference to the cell to change.
SpreadsheetGear.IRange changingCell = worksheet.Cells["ChangingCell"];

// Set up the desired monthly payment.
double goal = 1000.0;

// Call GoalSeek which returns true if the desired goal is achieved.
bool achieved = targetCell.GoalSeek(goal, changingCell);

// Display the achieved status in the worksheet.
worksheet.Cells["GoalSeekStatusCell"].Value = achieved ? "Achieved" : "Failed";