Export (0) Print
Expand All
ABS
AND
ASC
COS
DAY
DDB
EXP
FV
IF
INT
LEN
LN
LOG
MAX
Me
MID
MIN
MOD
NOT
IS
NPV
ODD
OR
PI
PMT
PV
SIN
SLN
SUM
SYD
T
TAN
VAR
Expand Minimize

About formulas

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Formulas are equations that perform calculations on values in a list. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

You can use a formula in a calculated column and to calculate default values for a column.

A formula can also contain any or all of the following: functions, column references, operators, and constants.

Parts of a formula

=PI()*[Result]^2

Functions The PI() function returns the value of pi: 3.142...

References (or column names) [Result] represents the value in the Result column for the current row.

Constants Numbers or text values entered directly into a formula, such as 2.

Operators The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies.

A formulas might use one or more of the above elements. Here are some examples of formulas (in order of complexity).

Simple formulas (such as =128+345)

The following formulas contain literal values and operators.

Example

Description

=128+345

Adds 128 and 345

=128+345

Adds 128 and 345

=5^2

Squares 5

=5^2

Squares 5

Formulas that contain column references (such as=[Revenue] - [Cost])

The following formulas refer to other columns in the same list.

Example

Description

=[Revenue]

Use the value in the Revenue column

=[Revenue]

Use the value in the Revenue column

=[Revenue]*10/100

10% of the value in the Revenuecolumn

=[Revenue]*10/100

10% of the value in the Revenuecolumn

=[Revenue] > [Cost]

Returns Yes if the value inthe Revenue column is greater than the value in the Cost column

=[Revenue] > [Cost]

Returns Yes if the value inthe Revenue column is greater than the value in the Cost column

Formulas that call functions (such as=AVG(1, 2, 3, 4, 5))

The following formulas call built-in functions.

Example

Description

=MAX(Q1, Q2, Q3, Q4)

Returns the largest value in a set of values

=MAX(Q1, Q2, Q3, Q4)

Returns the largest value in a set of values

=IF(Cost>Revenue, "Not OK", "OK")

Returns Not OK if cost is greater than revenue. Else, returns OK .

=IF(Cost>Revenue, "Not OK", "OK")

Returns Not OK if cost is greater than revenue. Else, returns OK .

=DAY("15-Apr-2008")

Returns a day part of a date. This formula returns the number 15.

=DAY("15-Apr-2008")

Returns a day part of a date. This formula returns the number 15.

Formulas with nested functions (such as=SUM(ROUND([Cost],2),[Profit])

The following formulas specify one or more functions as function arguments.

Example

Description

=SUM(IF(A>B, A-B, 10), C)

The IF function returns the difference between the values in columns A and B, or 10.

The SUM function adds the return value of the IF function and the value in column C.

=SUM(IF(A>B, A-B, 10), C)

The IF function returns the difference between the values in columns A and B, or 10.

The SUM function adds the return value of the IF function and the value in column C.

=DEGREES(PI())

The PI function returns the number 3.14159265358979.

The DEGREES function converts a value specified in radians to degrees. This formula returns the value 180.

=DEGREES(PI())

The PI function returns the number 3.14159265358979.

The DEGREES function converts a value specified in radians to degrees. This formula returns the value 180.

=ISNUMBER(FIND("BD",Column1))

The FIND function searches for the string BD in Column1 and returns the starting position of the string. It returns an error value if the string is not found.

The ISNUMBER function returns Yes if the FIND function returned a numeric value. Else, it returns No.

=ISNUMBER(FIND("BD",Column1))

The FIND function searches for the string BD in Column1 and returns the starting position of the string. It returns an error value if the string is not found.

The ISNUMBER function returns Yes if the FIND function returned a numeric value. Else, it returns No.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft