Advanced Formulas and Functions 2010
[Not for Circulation]
Advanced Formulas and Functions in Microsoft Excel
This document provides instructions for using some of the more complex formulas and functions in Microsoft Excel, as well as using absolute references in formulas.
Opening Comments
Formulas are equations that perform calculations on values. A formula starts with an equal sign (=) and follows the order of operations (parentheses, exponents, multiplication & division, addition & subtraction).
For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
A function is a preset formula. Like formulas, functions begin with an equal sign ( = ) followed by the function’s name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.
For example, the most used function in Excel is the SUM function, which is used to add together the data in selected cells. The SUM function is written as =SUM(A1:A6)
Here the function adds the contents of the cell range A1 to A6.
Formulas and functions can be entered directly into a cell or into the Formula Bar.
Enter a formula in the Formula Bar
Enter a formula directly into the cell
Information Technology Services, UIS
1
[Not for Circulation]
Formulas That Span Multiple Worksheets [3-D References]
There are often times when we want to create formulas that apply to data on multiple worksheets. For example, if each department in an organization has its own worksheet, it might be helpful to have a worksheet that totals or averages the budgets for the entire organization. A reference that refers to the same cell on multiple sheets is called a 3-D reference. A 3-D reference is a useful and convenient way to reference several worksheets that follow the same pattern where cells on each worksheet contain the same type of data.
To create a 3-D reference,
1. Click the cell where you want to enter the function.
2. Type = (equal
Advanced Formulas and Functions in Microsoft Excel
This document provides instructions for using some of the more complex formulas and functions in Microsoft Excel, as well as using absolute references in formulas.
Opening Comments
Formulas are equations that perform calculations on values. A formula starts with an equal sign (=) and follows the order of operations (parentheses, exponents, multiplication & division, addition & subtraction).
For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
A function is a preset formula. Like formulas, functions begin with an equal sign ( = ) followed by the function’s name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.
For example, the most used function in Excel is the SUM function, which is used to add together the data in selected cells. The SUM function is written as =SUM(A1:A6)
Here the function adds the contents of the cell range A1 to A6.
Formulas and functions can be entered directly into a cell or into the Formula Bar.
Enter a formula in the Formula Bar
Enter a formula directly into the cell
Information Technology Services, UIS
1
[Not for Circulation]
Formulas That Span Multiple Worksheets [3-D References]
There are often times when we want to create formulas that apply to data on multiple worksheets. For example, if each department in an organization has its own worksheet, it might be helpful to have a worksheet that totals or averages the budgets for the entire organization. A reference that refers to the same cell on multiple sheets is called a 3-D reference. A 3-D reference is a useful and convenient way to reference several worksheets that follow the same pattern where cells on each worksheet contain the same type of data.
To create a 3-D reference,
1. Click the cell where you want to enter the function.
2. Type = (equal