Overview of Excel Functions
Overview of formulas
Formulas are equations that perform calculations on values in your worksheet. 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
A formula can also contain any or all of the following: functions, references, operators, and constants. Parts of a formula Functions: The PI() function returns the value of pi: 3.142... References: A2 returns the value in cell A2. 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.
Using constants in formulas
A constant is a value that is not calculated. For example, the date 10/9/2008.An expression, or a value resulting from an expression, is not a constant. If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.
Using calculation operators in formulas
Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.
Types of operators
There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.
Arithmetic operators
To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators. ILLUSTRATION
Arithmetic operator Meaning Example
+ (plus sign) Addition 3+3
– (minus sign) Subtraction
Negation 3–1
–1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2
Comparison operators
You can compare two values with the following operators. When two values are compared by using these operators, the
Formulas are equations that perform calculations on values in your worksheet. 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
A formula can also contain any or all of the following: functions, references, operators, and constants. Parts of a formula Functions: The PI() function returns the value of pi: 3.142... References: A2 returns the value in cell A2. 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.
Using constants in formulas
A constant is a value that is not calculated. For example, the date 10/9/2008.An expression, or a value resulting from an expression, is not a constant. If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.
Using calculation operators in formulas
Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.
Types of operators
There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.
Arithmetic operators
To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators. ILLUSTRATION
Arithmetic operator Meaning Example
+ (plus sign) Addition 3+3
– (minus sign) Subtraction
Negation 3–1
–1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2
Comparison operators
You can compare two values with the following operators. When two values are compared by using these operators, the