Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks.

=(B4+25)/SUM(D5:F5)

= denotes a formula

B4 is a cell reference

+ is the Additional Operator

25. Is a Numeric Constant

/ is a Division Operator

SUM(D5:F5) is a Worksheet Function

D5:F5 is a Range Reference

A formula in Microsoft Excel begins with an equal sign (=), followed by what the formula calculates. For example, the following formula subtracts 1 from 5. The result of the formula is then displayed in the cell.

=5-1

The cell that contains the formula is known as a dependent cell -- its value depends upon the value in another cell. Whenever the cell that the formula refers to changes, the cell that contains the formula also changes.

The following formula multiplies the value in cell B15 by 5. The formula will recalculate whenever the value in cell B15 changes.

=B15*5

Formulas can refer to cells or ranges of cells, or to names or labels that represent cells or ranges

To calculate the current balance for the first transaction (cell F7):

=SUM(F6,D7,–E7)

Calculates the running balance in a checkbook register. In this example, assume that cell D7 contains the current transaction's deposit, cell E7 contains any withdrawal amount, and cell F6 contains the previous balance. As you enter new transactions, copy this formula to the cell that contains the current balance for the new transaction.

To display the full name in the format

"first_name last_name":

=D5&" "&E5

To display the full name in the format

"last_name, first_name":

=E5&", "&D5

Joins a first name stored in one cell with a last name stored in another cell. In this example, assume that cell D5 contains the first name, and cell E5 contains the last name.

Increases a numeric value stored in one cell by a percentage, such as 5 percent.

=F5*(1+5%)

In this example, assume that cell F5 contains the original value.

If the percentage amount is stored in a cell (for example, cell F2):

=F5*(1+$F$2)

The reference to cell F2 is an absolute cell reference so that the formula can be copied to other cells without changing the reference to F2.

Creates a total value for one range based on a value in another range.

=SUMIF(B5:B25,"Northwind",F5:F25)

For example, for every cell in the range B5:B25 that contains the value "Northwind", you want to calculate the total for the corresponding cells in the range F5:F25.

Creates a total value for one range based on two conditions.

=SUM(IF(B5:B25="Northwind",

IF(C5:C25="Western",F5:F25)))

For example, you want to calculate the total value of the cells in F5:F25 where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western". Note This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER.

Counts the number of occurrences of a value in a range of cells.

=COUNTIF(B5:B25,"Northwind")

 For example, the number of cells in the range B5:B25 that contain the text "Northwind".

Counts the number of occurrences of a value in a range of cells, based on a value in another range 

=SUM(IF(B5:B25="Northwind",

IF(C5:C25="Western",1,0)))

For example, the number of rows in the range B5:B25 that contain the text "Northwind" and the text "Western" in the range C5:C25. Note This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER.

Arithmetic

operator Meaning Example

+ (plus sign) Addition 3+3

– (minus sign) Subtraction 3–1

Negation –1

* (asterisk) Multiplication 3*3

/ (forward slash) Division 3/3

% (percent sign) Percent 20%

^ (caret) Exponentiation 3^2

(the same as 3*3)

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 operator & (ampersand)

Meaning Connects, or concatenates, two

values to produce one

continuous text value

Example "North" & "wind" produce

"Northwind"

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)

(single space) Intersection operator, which produces one reference to cells common to two references SUM(B5:B15 A7:D7)

In this example, cell B7 is common to both ranges.

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, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references.

By default, Microsoft Excel uses the A1 reference style, which labels columns with letters (A through IV, for a total of 256 columns) and labels rows with numbers (1 through 65536).

To refer to a cell, enter the column letter followed by the row number. 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. The following are examples of references.

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

Depending on the task you want to perform in Microsoft Excel, you can use either relative cell references, which are references to cells relative to the position of the formula (D11), or absolute references ($D$11), which are cell references that always refer to cells in a specific location.

You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows, or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values.

If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Microsoft Excel uses any worksheets stored between the starting and ending names of the reference.

As you enter a function into the formula, the Formula Palette 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. To display the Formula Palette, click the = (equal sign) Edit Formula in the formula bar.

You can use the Formula Palette to edit functions in formulas. Select a cell that contains a formula, and then click Edit Formula (=) to display the Formula Palette. The first function in the formula and each of its arguments are displayed in the palette. You can edit the first function or edit another function in the same formula by clicking in the formula bar anywhere within the function.

For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name.

As you create a formula that contains a function, the Formula Palette will assist you .

You can use the Formula Palette to edit functions in formulas. Select a cell that contains a formula, and then click = to display the Formula Palette. The first function in the formula and each of its arguments are displayed in the palette. You can edit the first function or edit another function in the same formula by clicking in the formula bar anywhere within the function. Worksheet functions are calculation tools that can be used on worksheets to perform decision-making, action-taking, and value-returning operations automatically.

Arguments to a function can be any of the following:

• Numbers - examples are 5.003, 0, 150,266, and -30.05. Numbers without decimals are called integers. Numbers are accurate to 15 digits.
• Text - Text values used in formulas must be enclosed in double quotation marks. Text values can be a maximum of 255 characters long including quotation marks. A text constant that contains no characters is written as "" and is called "empty text."
• Logical values - The logical values are TRUE and FALSE. Logical arguments can also be statements that evaluate to TRUE or FALSE.
• Error values - Examples of error values are #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
• References - References can refer to single cells, ranges, or multiple selections, and can be relative, absolute, or mixed. Examples are $A$10, A10, A$10, R1C2, or R[10]C[-10].
• Arrays - Arrays allow you to customize how arguments and functions are entered in cells.

Database Functions

Some database and list management worksheet functions have names that begin with the letter "D." These functions, also known as Dfunctions, have three arguments – database, field, criteria.

• The database argument is the range that contains your list. You must include the row that contains the column labels in the range.
• The field argument is the label for the column you want to summarize.
• The criteria argument is the range that contains a condition you specify.

Date and Time Functions

Engineering Functions

• Functions for working with complex numbers
• Functions for converting values between different numbering systems, such as decimal, hexadecimal, octal, and binary systems
• Functions for converting values between different systems of measurement

Note The engineering functions are provided by the Analysis ToolPak. If an engineering worksheet function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

Financial Functions

Common arguments for the financial functions include:

• Future value (fv) – the value of the investment or loan after all payments have been made.
• Number of periods (nper) – the total number of payments or periods of an investment.
• Payment (pmt) – the amount paid periodically to an investment or loan.
• Present value (pv) – the value of an investment or loan at the beginning of the investment period. For example, the present value of a loan is the principal amount that is borrowed.
• Rate (rate) – the interest rate or discount rate for a loan or investment.
• Type (type) – the interval at which payments are made during the payment period, such as at the beginning of a month or the end of the month.

Logical Functions

For example, you can use the IF function to determine whether a condition is true or false: One value is returned if the condition is true, and a different value is returned if the condition is false.

Lookup and Reference Functions

For example, to find a value in a table by matching a value in the first column of a table, use the VLOOKUP worksheet function. To determine the position of a value in a list, use the MATCH worksheet function.

Math and Trigonometry Functions

Statistical Functions

For example, a statistical worksheet function can provide statistical information about a straight line plotted through a group of values, such as the slope of the line and the y-intercept, or about the actual points that make up the straight line.

Text Functions

For example, you can change the case or determine the length of a text string. You can also join, or concatenate, a date to a text string. The following formula is an example of how you can use the TODAY function with the TEXT function to create a message that contains the current date and formats the date in the "dd-mmm-yy" number format.

="Budget report as of "&TEXT(TODAY(),"dd-mmm-yy")