PDF Formulas & Functions in Microsoft Excel - WebHome
Formulas & Functions in Microsoft Excel
Theresa A Scott, MS Biostatistician III Department of Biostatistics Vanderbilt University
theresa.scott@vanderbilt.edu
Table of Contents
1 Introduction
1
1.1 Using Excel for Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Formulas and Functions
2
2.1 Entering Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2.2 Entering Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.2.1 Reference Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.2.2 Autosum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2.3 Function Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.3 A Function instead of a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3 Copying Formulas/Functions and Cell References
7
3.1 Relative Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.2 Absolute Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.3 Mixed Cell References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4 Other Topics to Consider
10
4.1 Order of Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.2 Using Named Ranges Formulas and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.3 Linking Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
i
Preface
All of the lecture notes and supplementary sample data files are located at . edu/TheresaScott under Current Teaching Material. If you have any questions, feel free to contact me at theresa.scott@vanderbilt.edu or (615) 343-1713, or drop by my office D-2217 MCN.
References for this lecture
The following references were used to compile this lecture: The Excel 2003 Module information available from Carnegie Mellon University's Computer Skills Workshop Class (cmu.edu/computing/csw). The BayCon Group Microsoft Excel Online Tutorial (el0.htm). The Florida Gulf Coast University Excel 2000 Tutorial (fgcu.edu/support/office2000/excel).
1
Section 1
Introduction
The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions. Otherwise, it is not much more than a large table displaying text.
Recall, your spreadsheet environment will become a dynamic and responsive work environment when you use formulas and functions; they automatically updating when you change your data.
The following should be considered when creating a spreadsheet: Which terms are data/numbers that you will type. What terms are data/numbers that you will calculate from the data. Which numbers are constant and are to be used in a variety of calculations. What arrangement of the columns and rows will make it easiest for you and your intended audience to work with the spreadsheet. ? Feel free to check out an additional lecture called "Guidelines to Data Collection and Data Entry" that can also be found on my website. ? You can also find examples of a `Spreadsheet from Heaven' and a `Spreadsheet from Hell '.
1.1 Using Excel for Analysis
Excel is a great tool to use for data collection and entry, and even to use for some derivation of other columns. However, Excel IS NOT the best tool to use to conduct advanced analyses, especially statistical analyses. Missing values can be very dangerous in Excel.
In formulas and functions, missing values (i.e. blank cells) are sometimes taken as zeros, when they should represent data that are truly missing.
Recommend consulting with an experienced statistician when wanting to conduct advanced and/or statistical analysis, or use packages like SPSS, STATA, R, or SAS.
Tip: Be aware of Excel's faults and, if possible, set up a simple example to test the function's handling of blank cells.
1
Section 2
Formulas and Functions
As mentioned, the ability to perform calculations is one of the purposes of using a spreadsheet application. Some examples of the types of calculations that can be done are:
totals subtotals average standard deviation
In Excel,the calculation can be specified using either a formula or a function. Formulas are self-defined instructions for performing calculations. In contrast, functions are pre-defined formulas that come with Excel.
In either case, all formulas and functions are entered in a cell and must begin with an equal sign '='.
2.1 Entering Formulas
After the equal sign, a formula includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. The operands are the standard arithmetic operators:
Operator + * / ^
Meaning Addition Subtraction Multiplication Division Exponents
Example =A7+A9 =A7-A9 =A7*A9 =A7/A9 =A7^A9
Practice Exercise: Enter the following information into a blank worksheet (ignore any formatting) in columns A, B, and C, and in rows 1 through 6. Then calculate the Total Cost for the the Fall semester using a formula to add up the individual Costs (Tuition, Housing, etc.).
Costs Tuition Housing Books Spending Total Cost
Fall 10000 5000 1000 1500
Spring 10000 5000 700 1000
2
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- pdf how virtual gpus are transforming industries
- pdf uniform residential loan application fannie mae
- pdf download stein mart routing guide
- pdf software for the calculation of costs tariffs and rates for
- pdf 2018 tax solutions relyco
- pdf compatible with windows xp vista and version 7
- pdf the mortgage loan originator mlo testing handbook
- pdf margill loan manager 4 margill finance software for
- pdf ti 84 plus and ti 84 plus silver edition guidebook
- pdf reversevision handbook