Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]Welcome to DevExpress .NET Documentation
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Reporting
  [Expand]Chart Control
  [Expand]Grid View
  [Expand]Card View
  [Expand]Vertical Grid
  [Collapse]Spreadsheet
   [Expand]Product Information
    Getting Started
   [Collapse]Concepts
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Expand]Cell Basics
    [Collapse]Spreadsheet Formulas
     [Expand]Functions
      Operators
      Array Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
    [Expand]Charting
     Printing
    [Expand]Mail Merge
     Protection
     Adaptivity
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Image and Data Browsing
  [Expand]Docking and Popups
  [Expand]Site Navigation and Layout
  [Expand]File Management
  [Expand]Multi-Use Site Controls
  [Expand]Scheduler
  [Expand]HTML Editor
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gauges
  [Expand]Data Editors
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Localization
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Spreadsheet 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

Shared A shared formula can be used to optimize calculations and file size. A shared formula is the equivalent of applying the same formula to a range of cells. Shared formula is created automatically 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.
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.

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

Expanded See Also

Is this topic helpful?​​​​​​​