# Working with Formulas

9 Apr 20214 minutes to read

Formulas are entries in Excel that have equations, by which values are calculated. A typical formula might contain cell references, constants, and even functions.

## Enable Calculation

To perform calculation in an Excel workbook, it is recommended to invoke enableSheetCalculations() method of Worksheet. Enabling this method will initialize CalcEngine objects and retrieves calculated values of formulas in a worksheet.

The following code sample illustrates on how to enable worksheet formula calculations.

• dart
• ``````// Create a new Excel Document.
final Workbook workbook = Workbook();

// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets;

//Formula calculation is enabled for the sheet
sheet.enableSheetCalculations();

// Save and dispose workbook
final List<int> bytes = workbook.saveAsStream();
workbook.dispose();

File('Output.xlsx').writeAsBytes(bytes);``````

## Apply Formula

In a worksheet, formulas can be entered by using the setFormula() method of the Range instance.
Following code example illustrates on how to write a formula.

• dart
• ``````// Create a new Excel document.
final Workbook workbook = Workbook();

//Accessing worksheet via index.
final Worksheet sheet = workbook.worksheets;

//set the value to the cell.
sheet.getRangeByName('A1').setNumber(10);
sheet.getRangeByName('A2').setNumber(20);

//Setting formula in the cell.
sheet.getRangeByName('A3').setFormula('=A1+A2');

//Save and dispose a workbook.
final List<int> bytes = workbook.saveAsStream();
workbook.dispose();

File('Formula.xlsx').writeAsBytes(bytes);``````

## Accessing a Calculated value

To evaluate formula, it is must to enable sheet calculation in prior. After enabling the sheet calculation, the formula can be evaluated using calculatedValue of Range, which returns a string value.

The following code shows how to access a calculated value.

• dart
• ``````// Create a new Excel document.
final Workbook workbook = Workbook();

// Accessing worksheet via index.
final Worksheet sheet = workbook.worksheets;

// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();

// set the value to the cell.
sheet.getRangeByName('A1').setNumber(10);
sheet.getRangeByName('A2').setNumber(20);

// Setting formula in the cell.
sheet.getRangeByName('A3').setFormula('=A1+A2');

// Returns the calculated value of a formula using the most current inputs
String calculatedValue = sheet.getRangeByName('A3').calculatedValue;

// Save and dispose a workbook.
final List<int> bytes = workbook.saveAsStream();
workbook.dispose();

File('Formula.xlsx').writeAsBytes(bytes);``````

## Formula with Nested Functions

Using a function as one of the arguments in a formula is known as Nested Function.

The following code shows how to use nested functions.

• dart
• ``````// Create a new Excel Document.
final Workbook workbook = Workbook();

// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets;

// set the value to the cell.
sheet.getRangeByName('B3').setText('Team A');
sheet.getRangeByName('B4').setNumber(47);
sheet.getRangeByName('B5').setNumber(43);
sheet.getRangeByName('B6').setNumber(40);
sheet.getRangeByName('B7').setNumber(51);
sheet.getRangeByName('B8').setNumber(53);
sheet.getRangeByName('B9').setNumber(50);

sheet.getRangeByName('D3').setText('Team B');
sheet.getRangeByName('D4').setNumber(72);
sheet.getRangeByName('D5').setNumber(43);
sheet.getRangeByName('D6').setNumber(84);
sheet.getRangeByName('D7').setNumber(90);
sheet.getRangeByName('D8').setNumber(42);
sheet.getRangeByName('D9').setNumber(56);

// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();

// Setting formula in the cell.
final Range range = sheet.getRangeByName('B11');
range.setFormula(
'=IF(SUM(AVERAGE(B4:B9), MAX(COUNT(B4,D4), MIN(B5,D5))) > 50, \"PASS\", \"FAIL\")');

// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('NestedFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();``````

## Supported Formulas

In flutter XlsIO, we have support for Range reference and basic function formula listed below:

 SUM Adds its arguments AVERAGE Returns the average of its arguments MAX Returns the maximum value in a list of arguments MIN Returns the minimum value in a list of arguments COUNT Counts how many numbers are in the list of arguments IF Specifies a logical test to perform AND Returns TRUE if all of its arguments are TRUE OR Returns TRUE if any argument is TRUE NOT Reverses the logic of its argument CONCATENATE Joins several text items into one text item TRIM Removes spaces from text LOWER Converts text to lowercase UPPER Converts text to uppercase NOW Returns the serial number of the current date and time TODAY Returns the serial number of today's date INDEX Uses an index to choose a value from a reference or array MATCH Looks up values in a reference or array PRODUCT Multiplies its arguments SUMPRODUCT Returns the sum of the products of corresponding array components SUMIF Adds the cells specified by a given criteria VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell COUNTIFS Counts the number of times all criteria are met. MAXIFS Returns the maximum value among cells specified by a given set of conditions or criteria MINIFS Returns the minimum value among cells specified by a given set of conditions or criteria SUMIF Adds the cells specified by a given criteria SUMIFS Adds all of its arguments that meet multiple criteria