Editing Spreadsheet (section)

=== Formulas ===
[[File:Spreadsheet animation.gif|thumb|right|279px|Animation of a simple spreadsheet that multiplies values in the left column by 2, then sums the calculated values from the right column to the bottom-most cell. In this example, only the values in the <code>A</code> column are entered (10, 20, 30), and the remainder of cells are formulas. Formulas in the <code>B</code> column multiply values from the A column using relative references, and the formula in <code>B4</code> uses the <code>SUM()</code> function to find the [[Summation|sum]] of values in the <code>B1:B3</code> range.]]

A formula identifies the [[calculation]] needed to place the result in the cell it is contained within. A cell containing a formula, therefore, has two display components; the formula itself and the resulting value. The formula is normally only shown when the cell is selected by "clicking" the mouse over a particular cell; otherwise, it contains the result of the calculation.

A formula assigns values to a cell or range of cells, and typically has the format:
{|class="wikitable"
|-
|<code>=''expression''</code>
|}
where the [[Expression (programming)|expression]] consists of:
*[[Value (computer science)|values]], such as <code>2</code>, <code>9.14</code> or <code>6.67E-11</code>;
*[[Reference (computer science)|references]] to other cells, such as, e.g., <code>A1</code> for a single cell or <code>B1:B3</code> for a range;
*[[Operator (programming)|arithmetic operators]], such as <code>+</code>, <code>-</code>, <code>*</code>, <code>/</code>, and others;
*[[relational operator]]s, such as <code>>=</code>, <code><</code>, and others; and,
*[[Function (programming)|functions]], such as <code>SUM()</code>, <code>TAN()</code>, and many others.

When a cell contains a formula, it often contains references to other cells. Such a cell reference is a type of variable. Its value is the value of the referenced cell or some derivation of it. If that cell in turn references other cells, the value depends on the values of those. References can be relative (e.g., <code>A1</code>, or <code>B1:B3</code>), absolute (e.g., <code>$A$1</code>, or <code>$B$1:$B$3</code>) or mixed row– or column-wise absolute/relative (e.g., <code>$A1</code> is column-wise absolute and <code>A$1</code> is row-wise absolute).

The available options for valid formulas depend on the particular spreadsheet implementation but, in general, most arithmetic operations and quite complex nested conditional operations can be performed by most of today's commercial spreadsheets. Modern implementations also offer functions to access custom-build functions, remote data, and applications.

A formula may contain a condition (or nested conditions)—with or without an actual calculation—and is sometimes used purely to identify and '''highlight errors'''. In the example below, it is assumed the sum of a column of percentages (A1 through A6) is tested for validity and an explicit message put into the adjacent right-hand cell.

:=IF(SUM(A1:A6) > 100, "More than 100%", SUM(A1:A6))

Further examples:
:=IF(AND(A1<>"",B1<>""),A1/B1,"") means that if both cells A1 and B1 are not <> empty "", then divide A1 by B1 and display, other do not display anything.
:=IF(AND(A1<>"",B1<>""),IF(B1<>0,A1/B1,"Division by zero"),"") means that if cells A1 and B1 are not empty, and B1 is not zero, then divide A1 by B1, if B1 is zero, then display "Division by zero", and do not display anything if either A1 and B1 are empty.
:=IF(OR(A1<>"",B1<>""),"Either A1 or B1 show text","") means to display the text if either cells A1 or B1 are not empty.

The best way to build up conditional statements is step by step composing followed by trial and error testing and refining code.

A spreadsheet does not have to contain any formulas at all, in which case it could be considered merely a collection of data arranged in rows and columns (a [[database]]) like a calendar, timetable, or simple list. Because of its ease of use, formatting, and [[hyperlinking]] capabilities, many spreadsheets are used solely for this purpose.