Click or drag to resize

Defining Formulas in Excel spreadsheets with ExpertXls Library for Excel

Assign a formula to a cell with the Formula property and an array formula to a cell or a range with the FormulaArray property. These properties use the A1 notation; FormulaR1C1 and FormulaArrayR1C1 use the R1C1 notation. Formulas can use named ranges.

The formulas are parsed with the separators and the number format of the workbook: FormulaParamsSeparator, FormulaRowsSeparator and FormulaNumberFormat. The default number format is the format of the current culture; set these properties before the first formula when the code must work with any culture.

Excel calculates the formulas when it opens the workbook. Call UpdateFormulaResult to calculate them in the library, for example to read a result with FormulaResultNumberValue. The results, including the results of the array formulas, are saved in the .xls and .xlsx files for the applications that read the workbook without calculating the formulas.

Code Samples
// parse the formulas the same way with any culture
workbook.FormulaParamsSeparator = ',';
workbook.FormulaRowsSeparator = ';';
workbook.FormulaNumberFormat = System.Globalization.NumberFormatInfo.InvariantInfo;

worksheet["C7"].Value = 100.5;
worksheet["D7"].Value = 50.5;
worksheet["E7"].Value = 89;

// ordinary formulas
worksheet["C9"].Formula = "=SUM(C7:E7)";
worksheet["C10"].Formula = "=AVERAGE(C7:E7)";
worksheet["C11"].Formula = "=IF(C7>E7,C7,E7)";

// the same sum in R1C1 notation: row 7, columns 3 to 5
worksheet["C12"].FormulaR1C1 = "=SUM(R7C3:R7C5)";

// an array constant fills the cells of a range
worksheet["C14:G14"].FormulaArray = "{10,-5,2,21,-20}";
// an array formula: the average of the positive numbers
worksheet["C15"].FormulaArray = "=AVERAGE(IF(C14:G14>0,C14:G14,FALSE))";

// a formula with a named range
worksheet.NamedRanges.AddNamedRange(worksheet["C7:E7"], "Sales");
worksheet["C16"].Formula = "=MAX(Sales)";

// calculate the formulas in the library and read a result
workbook.UpdateFormulaResult();
double sum = worksheet["C9"].FormulaResultNumberValue;
See Also