Formula Overview

Formulas in VisualStat include operators and functions that follow certain syntax rules and allow you to perform a range of calculations. These topics introduce the concepts you need to make full use of the built-in functions and extensive capability of formulas:

 

 

What is a Formula?

Formulas can consist of values, operators, and functions. Data can be from other cells, a combination of data in another cell and hard-coded data (for example, A1 + 2), or simply hard-coded data (for example, SUM(4,5)). Formulas can perform mathematical operations, such as addition and multiplication, on values in other cells or they can compare values in other cells.

Formulas can refer to cells in the same sheet by their absolute cell location or relative to the cell with the formula in it. If the values in the referenced cells change, then the value of the formula cell changes.

If a new row is added right before or after a cell range in a formula then the range does not include the new row.

Each cell can be referenced by a combination of its column letter (A through Z, then AA to ZZ, AAA to ZZZ, etc.) and row number (1 and beyond) for a total of 2,147,483,648 rows and columns. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range.

 

 

Formula rules

Enclose specific text values, such as "green," with double quotes. Indicate missing values as a pair of double quotes with no space inside.

If an existing variable contains a space or another reserved character as an operator, this variable must be enclosed by apostrophes, for example 'Education Rate' = 3.

You do not need to enclose numbers with any character.

If you operate on a date/time variable with a number, for example NOW() + 30, VisualStat assumes the number is in units of days.

Some numeric functions (for example, SUM) ignore non-numeric values in a column. For example, if the a column named 'Count' contains the values {1, "2", 3}, then the formula SUM('Count') evaluates to 4 because the SUM function ignores the string "2".

 

 

Cell References

A formula can refer to constant values or cell references. If a value in any of the referenced cells changes, the result of the formula changes. If you use constant values in the formula instead of references to the cells, the result changes only if you modify the formula (or values in the formula).

 

 

Cross-Sheet Referencing

A formula can have references to cells on the same sheet or to cells on other sheets, as well as ranges of cells on sheets.

An example of cross-sheet referencing in a formula that uses the addition operator would be:

(FirstRoundData!A2 + SecondRoundData!A2)

where the name of one sheet is "FirstRoundData" and the name of another sheet is "SecondRoundData".

Sheet names precede the cell reference with the name of the sheet followed by an exclamation point (!). As long as the sheet name conforms to normal variable name rules then the formula can use just the sheet name followed by the exclamation point. Otherwise, the sheet name needs to be enclosed in single quotes. If the sheet name itself contains a single quote, then use two single quotes in the formula. If the sheet name has a space, use single quotes around the sheet name. In the following example the sheet name is East Coast Sales.

('East Coast Sales'!A2 + 'West Coast Sales'!A1)

 

 

Operators

The following table lists the available operators. For each operator, an example is given of the syntax of using a literal value as well as a cell reference. The type of value returned is given for each type of operator.

 

Binary Operators

Operator

Description

Example

Type Returned

+

Add

5 + 3, A1 + 3

double

Subtract

5 – 3, A1 – 3

double

*

Multiply

5 * 3 , A1 * 3

double

/

Divide

5 / 3 , A1 / 3

double

^

Exponent

5 ^ 3, A1 ^ 3

double

&

Concatenate

"F" & "p", A1 & "p"

string

=

Equal

A1 = 3

boolean

< >

Not Equal

A1 <> 3

boolean

<

Less Than

A1 <3

boolean

>

Greater Than

A1 > 3

boolean

<=

Less Than Or Equal

A1 <= 3

boolean

>=

Greater Than Or Equal

A1 >= 3

boolean

 

Unary Operators

Operator

Description

Example

Type Returned

-

Negate

-(5/3), -(A1/3)

double

+

Plus

+(5/3), +(A1/3)

double

%

Percent

(5/3)%, (A1/3)%

double

 

 

Operators specify the type of calculation that you want to perform on the elements of a formula. Most of the operators return double-precision floating point values for mathematical operations and boolean (or logical) values for comparison operators.

In DataSheet, all arithmetic operators (including the unary +) check their arguments and return a #VALUE error if any of the arguments are strings that can not be converted to a number. This is mathematically correct behavior and can not be overridden. For example, the three formulas +B5 and 0+B5 and --B5 should all produce the same result and, in DataSheet, they do.

 

 

Resultant Error Values

The values that can be displayed in a cell as a result of an invalid entry or invalid formula are as follows:

 

Value

Description

#DIV/0!

This displays when a formula includes a division by zero or when a formula uses, in the divisor, a cell reference to a blank cell or to a cell that contains zero.

#N/A

This displays when a value is not available to a function or formula or when an argument in an array formula is not the same size as the range that contains the array formula.

#NAME?

This displays when text in a formula is not recognized or when the name of a function is misspelled, or when including text without using double quotation marks. This can also happen when you omit a colon (:) in a cell range reference.

#NULL!

This displays when you specify an intersection of two areas that do not intersect. Possible causes include a mistyped reference operator or a mistyped cell reference.

#NUM!

This displays when a number in a formula or function can not be calculated, when a formula produces a number that is too large or too small to represent, or when using an unacceptable argument in a function that requires a number. If you are using a function that iterates, such as IRR or RATE, and the function cannot find a result, this value is displayed.

#REF!

This displays when a cell reference is not valid or when you deleted cells referred to by a formula.

#VALUE!

This displays when the wrong type of argument or operand is used, such as using text when the formula requires a number or a logical value, or using a range instead of a single value.

 

 

 

See Also:


Compute Formula | Formula Functions