Formulas & Functions in Microsoft Excel

1 Formulas & Functions in Microsoft ExcelTheresa A Scott, MSBiostatistician IIDepartment of BiostatisticsVanderbilt of Contents1 Using Excel for Analysis .. 22 Formulas and Entering Formulas .. Entering Functions .. Reference Operators .. Autosum .. Function Wizard .. A Function instead of a Formula .. 83 Copying Formulas / Functions and Cell Relative Cell References .. Absolute Cell References .. Mixed Cell References .. 124 Other Topics to Automatic Calculation .. Order of Operations .. Using Named Ranges Formulas and Functions .. Linking Worksheets .. 15iPrefaceAll of the lecture notes and supplementary sample data files are located you have any questions, feel free to contact me drop by my officeD-2217 for this lectureThe following references were used to compile this lecture: The Excel 2003 Module information available from Carnegie Mellon Uni-versity s Computer Skills Workshop Class ( ).

2 The BayCon Group Microsoft Excel Online Tutorial ( ). The Florida Gulf Coast University Excel 2000 Tutorial ( ).1 Section 1 IntroductionThe distinguishing feature of a spreadsheet program such as Excel is that itallows you to create mathematicalformulasand executefunctions. Otherwise,it is not much more than a large table displaying text. Recall, your spreadsheet environment will become adynamicandrespon-sivework environment when you use Formulas and Functions ; they auto-matically updating when you change your 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 youand your intended audience to work with the following are some guidelines on How to enter research data in acomputer spreadsheet for optimal statistical analysis , from Daniel , Enter all or most of the data as numbers.

3 Avoid entering letters, words, or string variables ( , ?, *, NA, 22%,< ). In Excel , all columns except names, and text comments should beformatted as numbers or dates (not as general or text).2. Give each column a unique, simple, 1-word name, 8 characters or less withno spaces, beginning with a letter, and place this name in the first 1. INTRODUCTION23. Put only one variable in a column. Do not combine variables in the same Enter each patient (or unit of analysis) on a separate line, beginning onthe second Give each patient a unique case number (1,2,3, etc.) in the first column. Delete patient name, SS#, MR#, and any identifying informationbefore sending it to a Enter cases and controls in the same spreadsheet. Use one variable to define the control group (TREATED: 0 = no, 1= yes or GROUP: 1 = Drug A, 2 = Drug B).7. Quantify enter continuous measurements when Create a simple guide (or key) using a word processor to explain variablesabbreviations, and value coding, and how missing values were entered be Think through the analysis before collecting any Have a biostatistician review the coding before data are examples of a Spreadsheet from Heaven and a Spreadsheetfrom Hell.

4 Using Excel for AnalysisExcel is a great tool to use for data collection and entry, and even to use forsome derivation of other columns. However, ExcelIS NOTthe best tool touse to conduct advanced analyses, especially statistical valuescan be very dangerous in Excel . In Formulas and Functions , missing values ( blank cells) are sometimestaken as zeros, when they should represent data that are truly missing. Recommend consulting with an experienced statistician when wanting toconduct advanced and/or statistical analysis, or use packages like R, SPSS,or STATA. Tip: Be aware of Excel sfaultsand, if possible, set up a simple exampleto test the function s handling of blank 2 Formulas and FunctionsAs mentioned, the ability to perform calculations is one of the purposes of usinga spreadsheet application. Some examples of the types of calculations that canbe done are: totals subtotals average standard deviationIn Excel ,the calculation can be specified using either aformulaor afunction.

5 Formulasareself-definedinstructions for performing calculations. In contrast,functionsarepre-definedformulas that come with either case, all Formulas and Functions are entered in a cell and must be-gin with an equal sign = . Entering FormulasAfter the equal sign, a formula includes the addresses of the cells whose valueswill be manipulated with appropriateoperandsplaced in between. The operandsare thestandard arithmetic operators:OperatorMeaningExample+Additio n=A7+A9-Subtraction=A7-A9*Multiplication =A7*A9/Division=A7/A9 Exponents=A7 A93 SECTION 2. Formulas AND FUNCTIONS4 Practice Exercise:Enter the following information into a blank worksheet(ignore any formatting) in columnsA,B, andC, and in rows1through6. Thencalculate theTotal Costfor the theFallsemester using aformulato add up theindividualCosts(Tuition,Housing, etc.).CostsFallSpringTuition1000010000 Housing50005000 Books1000700 Spending15001000 Total Cost The most logical solution would be to type the formula =B2+B3+B4+B5 into can also enter Formulas by using thepoint mode, where you either clickon a cell with your left mouse button or you use the arrow keys.

6 To enter =B2+B3+B4+B5 into cellB6using the point: Left click on cellB6to make it active. Type = . Use the up arrow key to move to cellB5, or left click on cellB5. Type + . Use the up arrow or mouse to add cellsB4,B3, andB2in the samefashion. PressEnterwhen you are finished entering the ll notice that the calculation executes immediately after the formula istyped into the cell and entered by pressing either theEnterorTabkey or byclicking the check mark in the formula bar. Theresultof the calculation is displayed in the cell ( , inB6). The formula itself is now visible in the formula happens if you enter B2+B3+B4+B5 without the leading equal sign = into cellB6? REMEMBER:All Formulas and Functions must begin with a = . The text of the formula or function will be displayed in the cell if you donot use an = and the calculation will not be 2. Formulas AND Entering FunctionsFunctions differ from regular Formulas in that, after the equal sign, you supplythe cell addresses but not the arithmetic operators.

7 Functions perform calcu-lations by using specific values, calledarguments, in a particular order calledsyntax. When using a function, remember the following: Use an equal sign to begin the function. Specify the function name. Enclose all of the function s arguments within parentheses. Use a comma to separate the function s individual arguments. Practice Exercise:In theCostsfor theFallandSpringsemester spreadsheet,calculate theAverage CostofTuitionfor theFallandSpringsemesters using theAVERAGE function. The most logical solution would be to type =AVERAGE(B2, C2) into cellD2. The equal sign begins the function. AVERAGEis the name of the function. B2, andC2are the arguments. Parentheses enclose all of the arguments. Commas separate each of the Reference OperatorsReference operatorsrefer to a cell or a group of cells. There are two main typesof reference : Refers to all of the cells between and including the reference.

8 Consists of two cell addresses separated by a colon. EXAMPLE: A1:A3 includes cellsA1,A2, andA3. EXAMPLE: A1:C3 includes cellsA1,A2,A3,B1,B2,B3,C1,C2, : Includes two or more references. Consists of two or more cell addresses separated by a comma. EXAMPLE: A7, B8, C9 refers to cellsA7,B8, 2. Formulas AND FUNCTIONS6 EXAMPLE: A7, B8:D9, E4 refers to cellsA7,B8,B9,C8,C9,D8, the function practice exercise, we used a union reference when we typed =AVERAGE(B2, C2) in cellD2, but we could have used a range reference in-stead: =AVERAGE(B2:C2) . AutosumYou can use theAutosumicon on the standard toolbar, which automaticallyadds the contents of a cluster of adjacent cells. Select the cell that the sum will appear in that is outside the cluster ofcells whose values will be added. Click theAutosumbutton (Greek letter sigma, ). Highlight the group of cells that will be summed. Press theEnterkey on the keyboard or click the green check mark on theformula bar.

9 Practice Exercise:In theCostsfor theFallandSpringsemester spreadsheet,calculate theTotal Costfor the theSpringsemester using theAutosumicon. Click on cellC6to activate it. Click theAutosumbutton. Highlight cellsC2throughC5. Function WizardYou can access all of the available Functions in Excel using theFunction Wizard. Select the cell where the function will be placed and click theFunctionWizardbutton on the standard toolbar. Other ways of starting theFunction Wizardare: SelectFunctionfrom theInsertdrop menu. Click on the drop down arrow next to theAutosumicon button.*You will first see the commonly used Functions in Excel , and atthe bottom of the menu, theMore Functionsoption.*Clicking onMore Functionswill give you an alphabetical andcategorical listing of all available function in 2. Formulas AND FUNCTIONS7 From thePaste Functiondialog box, browse through the Functions by click-ing in theFunction categorymenu on the left and select from theFunction namechoices on the right.

10 As each function name is highlighted a description and example of use isprovided below the two select the function, opens theFunction Argumentsdialog box,which allows you to choose the cells that will be included in the function. As in the last previous function, we can type the cell addresses in thenecessary argument boxes. We can also enter the cells using the point mode ( , the left mousebutton). Click and drag across a group of cells to enter a range of cells ad-dresses. Use theCtrlkey and mouse to enter a union of cells addresses. Excel may automatically select cells for you, but you can delete thoseselected cells from the argument box and enter the desired cell all the cells for the function have been entered into the nec-essary argument boxes. Practice Exercise:In theCostsfor theFallandSpringsemester spreadsheet,calculate the averageHousingcost for theFallandSpringsemesters using theFunction Wizardand theAVERAGE function.