Goal Seek Operation

Goal Seek operation provides the input value for the formula according to the desired or known result. To perform Goal Seek operation, GcExcel provides GoalSeek method in IRange interface that uses goal and changingCell parameters to perform the operation.

goal parameter specifies the desired output, whereas changingCell parameter specifies the cell whose value will change.

GoalSeek method is a boolean-type method and returns true when the corresponding value is found. If the value is not found, the method returns false, and the value of changingCell will remain the same.

You must adhere to the following guidelines to ensure the correct result when using GoalSeek method:

• IRange for calling this method or changingCell parameter must be a single cell.

• IRange for calling this method must contain a formula.

• Formulas within the IRange for calling this method must result in a numeric value.

• changingCell parameter cannot contain a formula.

Refer to the following example code to find the missing expense for the month of March using GoalSeek method by setting the goal to 150000 and changingCell to B3:

// Create a new workbook.
var workbook = new Workbook();

// Set MaximumIterations and MaximumChange.
workbook.Options.Formulas.MaximumIterations = 1000;
workbook.Options.Formulas.MaximumChange = 0.000001;

var activeSheet = workbook.ActiveSheet;
            
// Add row header titles.
activeSheet.Range["A1:A4"].Value = new string[] { "January Expense", "February Expense", "March Expense", "Average" };

// January Expense.
activeSheet.Range["B1"].Value = 100000;
activeSheet.Range["B1"].NumberFormat = "$#,##0";

// February Expense.
activeSheet.Range["B2"].Value = 180000;
activeSheet.Range["B2"].NumberFormat = "$#,##0";
            
// March Expense.
activeSheet.Range["B3"].NumberFormat = "$#,##0";
            
// Average of all three monthly expenses.
activeSheet.Range["B4"].Formula = "=AVERAGE(B3,B2,B1)";
activeSheet.Range["B4"].NumberFormat = "$#,##0";

// Calculate the value of cell B3 using the GoalSeek method.
activeSheet.Range["B4"].GoalSeek(150000, activeSheet.Range["B3"]);

activeSheet.Range["A1:B4"].AutoFit();

// Save the Excel file.
workbook.Save("GoalSeek.xlsx");

Limitations

There may be minor inconsistencies with Excel calculations in some cases.