Excel Formulas
*******Information taken from Microsoft Office website*********
Formulas are equations that perform calculations on values in your worksheet. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
For a list of available functions, click a cell and press SHIFT+F3.
Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), error values such as #N/A, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.). The argument you designate must produce a valid value for that argument. Arguments can also be constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), formulas, or other functions.
Entering formulas
When you create a formula that contains a function, the Insert Function dialog box helps you enter worksheet functions. As you enter a function into the formula, the Insert Function dialog box displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.
Nested functions
In certain cases, you may need to use a function as one of the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
Valid returns
When a nested function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.
Nesting level limits
A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on.
References in formulas
A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.
By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.
To refer to |
Use |
The cell in column A and row 10 |
A10 |
The range of cells in column A and rows 10 through 20 |
A10:A20 |
The range of cells in row 15 and columns B through E |
B15:E15 |
All cells in row 5 |
5:5 |
All cells in rows 5 through 10 |
5:10 |
All cells in column H |
H:H |
All cells in columns H through J |
H:J |
The range of cells in columns A through E and rows 10 through 20 |
A10:E20 |
You can use the AND, OR, NOT, and IF function to create conditional formulas.
The IF function uses the following arguments:
=IF(logical test, value if true, value if false)
Example =IF(A2=15, "OK", "Not OK")
To create a conditional formula that results in a calculation or values that is TRUE or FALSE. Use the AND, OR, and NOT functions, and operators to do this task, as presented in the following example.
Formula |
Description (Result) |
=AND(A2>A3, A2<A4) |
Is 15 greater than 9 and less than 8? (FALSE) |
=OR(A2>A3, A2<A4) |
Is 15 greater than 9 or less than 8? (TRUE) |
=NOT(A2+A3=24) |
Is 15 plus 9 not equal to 24? (FALSE) |
To create a conditional formula that results in another calculation or values other than TRUE or FALSE. Use the IF, AND, and OR functions to do this task, as presented in the following example.
Formula |
Description (Result) |
=IF(A2=15, "OK", "Not OK") |
If the value in cell A2 equals 15, then return "OK". (OK) |
=IF(AND(A2>A3, A2<A4), "OK", "Not OK") |
If 15 is greater than 9 and less than 8, then return "OK". (Not OK) |
=IF(OR(A2>A3, A2<A4), "OK", "Not OK") |
If 15 is greater than 9 or less than 8, then return "OK". (OK) |
Count days before a date
Use the TODAY function to do this task.
Examples:
="5/19/2005"-TODAY()
=C15-TODAY()
Please note, to view the date as a number, select the cell and click Cells on the Format menu. Click the Number tab, and then click Number in the Category box.
Calculate the number of days between two dates
Use the subtraction (-) operator or the NETWORKDAYS function to do this task.
If this function is not available, install and load the Analysis ToolPak add-in.
How
- On the Tools menu, click Add-Ins.
- In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
- If necessary, follow the instructions in the setup program.
Formula |
Description |
=A3-A2 |
Days between the two dates |
=NETWORKDAYS(A2,A3) |
Weekdays between the two dates |
To view the dates as numbers, select the cell and click Cells on the Format menu. Click the Number tab, and then click Number in the Category box.
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, 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.
Arithmetic operator |
Meaning (Example) |
+ (plus sign) |
Addition (3+3) |
– (minus sign) |
Subtraction (3–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 result is a logical value either TRUE or FALSE.
Comparison operator |
Meaning (Example) |
= (equal sign) |
Equal to (A1=B1) |
> (greater than sign) |
Greater than (A1>B1) |
< (less than sign) |
Less than (A1<B1) |
>= (greater than or equal to sign) |
Greater than or equal to (A1>=B1) |
<= (less than or equal to sign) |
Less than or equal to (A1<=B1) |
<> (not equal to sign) |
Not equal to (A1<>B1) |
Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
Text operator |
Meaning (Example) |
& (ampersand) |
Connects, or concatenates, two values to produce one continuous text value ("North"&"wind") |
Reference operators Combine ranges of cells for calculations with the following operators.
Reference operator |
Meaning (Example) |
: (colon) |
Range operator, which produces one reference to all the cells between two references, including the two references (B5:B15) |
, (comma) |
Union operator, which combines multiple references into one reference (SUM(B5:B15,D5:D15)) |
(space) |
Intersection operator, which produces on reference to cells common to the two references (B7:D7 C6:C8) |
The order in which Excel performs operations in formulas
Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
Operator precedence
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence— for example, if a formula contains both a multiplication and division operator— Excel evaluates the operators from left to right.
Operator |
Description |
: (colon) (single space) , (comma) |
Reference operators |
– |
Negation (as in –1) |
% |
Percent |
^ |
Exponentiation |
* and / |
Multiplication and division |
+ and – |
Addition and subtraction |
& |
Connects two strings of text (concatenation) |
= < > <= >= <> |
Comparison |
Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)