Excel Basics: Formulas and Functions Using a spreadsheet to organize tables of numbers and text is just scratching the surface of Excel's potential. The real power is doing calculations on the entries automatically. Formulas: Basic Arithmetic Formulas begin with an equals sign, and refer to cells on the worksheet by their column letter and row number. The upper left-hand cell is cell "A1"; the lower right-hand cell is "IV65536". Add, subtract, multiply, divide Add C6 to B6 =B6+C6 Subtract C6 from B6 =B6-C6 Multiply B6 by C6 =B6*C6 Divide B6 by C6 =B6/C6 You can work on more than two cells at a time: =B6+C6-D6+E6 Use parentheses to group things together: =B6*(C6-D6+E6) Copying simple formulas When working with a whole table of things, you only have to type the formula once for the first row or column of the table, then copy the formula for the others. • Edit -> Copy, then Edit -> Paste • Edit -> Fill -> Down or Right Excel automatically adjusts all the cell addresses for their new location. Absolute addresses Sometimes you want to refer to a "constant" cell in formulas; then you need an absolute address. Put a dollar sign in front of the column letter and row number: =E21*D6 becomes =$E$21*D6 Then Excel will leave that address alone when it's copied. Why two dollar signs? Because Excel can also have mixed addresses, where it won't change the column letter (like $E21) or the row number (like E$21) when the formula is copied but will change the other. While entering a cell address, pressing <Ctrl/T> will cycle through the possibilities. Functions: Mathemagical Black Boxes Functions are tools to be used in formulas. Think of them as a black box: put something in one end of the box and something else comes out the other end. There are hundreds of functions, for doing things from trigonometry to text manipulation to statistics to looking up values in a table. You can see the whole selection with the Insert -> Function command. Functions can be used in formulas just like numbers or cell addresses. Parameters in parentheses The things we put into the function are called parameters. They show up after the function name in parentheses. If the function wants or allows more than one parameter, you separate them with commas. Ranges of cells Some functions operate on groups of cells. We describe those groups in the formula by their upper left-hand and lower right-hand cells separated by a colon like this: B1:D15 (you can also enter this as B1.D15) When you're entering a range you can also click, hold and drag on the range of cells. SUM, AVERAGE To add a range of cells, use =SUM(B1:B15) To average the values in a range of cells, use =AVERAGE(B1:B15) Making decisions with IF The IF function lets you choose one formula if some condition is true, and another formula if the condition is false. The function looks like this: =IF(condition,truevalue,falsevalue) Conditions These are just a few of the possibilities, . D6 is greater than zero D6>0 B6 is greater than or equal to C6 B6>=C6 A6 contains the text "Male" A6="Male" True and false choices The "truevalue" and "falsevalue" choices can be numbers, formulas or text in quotes. (Formulas don't have to start with an equals sign here.) Referencing cells from another sheet or file Cells from another sheet in the same worksheet can be used in a formula by beginning the cell reference with the name of the sheet, followed by the address or range: =Sheet2!B6:B17 Cells from another file work the same way, adding the filename in front of the sheet name in square brackets: =[FirstQuarter2007.xls]Sheet1!$D$2 2