You can find the closest value for a cell that produces a desired formula result in another cell using the goal seeking capability.

Use the GoalSeek method in the FpSpread class to find an input value that produces the desired formula result.

For more information about formulas, refer to the Formula Reference.

Using Code

Use the GoalSeek method to get the required value.

Example

In this example the formula is in cell (1,1). The result that you want to see in the formula cell is 32. The value in C1 is what is required to get a result of 32.

FpSpread1.Sheets[0].Cells[1, 1].Formula = "C1+D1";
FpSpread1.Sheets[0].Cells[0, 3].Value = 2;
FpSpread1.GoalSeek(0, 0, 2, 0, 1, 1, 32);

FpSpread1.Sheets(0).Cells(1, 1).Formula = "C1+D1"
FpSpread1.Sheets(0).Cells(0, 3).Value = 2
FpSpread1.GoalSeek(0, 0, 2, 0, 1, 1, 32)