Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]Controls and Libraries
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Expand]Scheduler (new)
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Formulas
    [Expand]Functions
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting
    Printing
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Formulas

A spreadsheet formula is an equation that performs a calculation on the numbers, functions and values of one or more cells. A formula is associated with a cell or a cell range. It is accessed using the Range.Formula property.

Expanded Formula Types

Formula Type

Description

Normal Use the Range.Formula property to assign a formula to a cell or to each cell in a range. Refer to the Creating Formulas document for examples of creating formulas.
Shared Shared formula is created automatically when required without user intervention when you assign a formula string to an array of cells. A specified formula is associated with each cell contained within the specified cell range. Shared formulas are used internally to optimize calculations and file size. Refer to the How to: Create Shared Formulas document for more information.
Array An array formula is a special kind of formula used to perform calculations with arrays of cells. Use the Range.ArrayFormula property or ArrayFormulaCollection.Add methods to include array formulas in your worksheet. An array formula for a cell or a range is accessible via the Range.ArrayFormula property. To find an array formula range that includes a particular cell, use the Cell.GetArrayFormulaRange method. For more information on array formulas, see the Array Formulas document.

Expanded Formula Syntax

A formula is a string expression that begins with an equal (=) sign. A formula can contain the constants, operators, cell references, calls to functions, and names.

Consider the following formula, which calculates the mass of a sphere.

=4/3*PI()*(A2^3)*Density

  • “4” and “3” are numeric constants. Although they are written as integers, the division operator (/) interprets them as being real numbers, i.e., 4.0 and 3.0. As a result, the calculation produces a precise result that is not rounded to an integer.
  • “/” is the division operator.
  • “PI()” results in a call to the PI function, which returns the value of p.
  • “A2” is a cell reference, which returns the value within that specific cell.
  • “3” is a numeric constant.
  • “^” is the caret operator, which raises the left operand to the power of the right operand.
  • Parentheses are used for grouping and changing the operator precedence.
  • “*” is the asterisk (*) operator, which performs multiplication.
  • The Density is a defined name within the worksheet that can represent a cell range, a function or a constant.

The formula is calculated from left to right, according to the operator precedence. To change the order of calculation you can enclose a portion of the formula in parentheses.

Expanded Parts of a Formula

Expanded Calculation

To recalculate all formulas in a workbook, call the IWorkbook.Calculate method. The DocumentSettings.Calculation property provides access to calculation options. Calculation results are placed in the Range.Value property of corresponding cells. See the Calculation topic for more information.

You can also calculate a formula and leave the document unchanged by using the IWorkbook.Evaluate method.

Expanded Formula Engine

The FormulaEngine is an object that provides the capability to calculate and parse worksheet formulas. It includes a built-in formula parser, as well as the flexibility to evaluate formulas in any range of any worksheet. See the Formula Engine topic for more information.

How would you rate this topic?​​​​​​​