"IEL4 Worksheet Formulas"
IEL4 Worksheet Formulas Please take notes 11/26/2008 Formulas 1 Operators • + Addition • - Subtraction • * Multiplication • / Division • ^ Exponentiation (squares and square roots • What is an operand? 11/26/2008 2 Order of Evaluation 1 • What is the answer to the following math problem- 3+4*3/6-3 • Because the above problem can be calculated in different ways computers follow rules on evaluating math problems. The rule is called Order of Evaluation. • The textbook is wrong- copy down the correct order from the next slide • check Excel Help for verification 11/26/2008 3 Order of Evaluation 2 • The order in which formulas are calculated in Excel • 1 () Parenthesis • 2 - Negative number like (-1) • This is not subtraction • 3 % Percent • 4 ^ Exponentiation • 3 Multiplication and Division • Order of Evaluation with * and / happens from left to right. Thus if / comes first its calculated first and if * comes first its calculated first • 4 Addition and Subtraction • Order of Evaluation with + and - happens from left to right. Thus if - comes first its calculated first and if + comes first its calculated first 11/26/2008 4 Order of Evaluation 3 • If you can’t remember what is calculated first use parenthesis to force what you want calculated first • Parenthesis work from the inside out. The inner most parenthesis are calculated first and then you work further out • 3+((4*3)/6)-3 = 2 but • 3+(4*3/(6-3))= 7 11/26/2008 5 Relative, Absolute or Mixed Cell References • Cell Reference and Cell Address are two words for the same thing- A4 or C11 • This is only important if you are copying or moving formulas. • Relative cell references are the ones we have been using. The cell reference changes when it is copied or moved 11/26/2008 6 Absolute Cell References • Indicated by a $. Thus A4 becomes $A$4 and C11 becomes $C$11 • Keeps the formula the same when moved to a new location 11/26/2008 7 Mixed Cell References • Part of the Cell Reference is Absolute and Part of the Cell Reference is Relative $A4 or C$11. • Using C$11 above if C$11 is copied to cell D12 the copied cell address could change to D$11 but never to D$12 or D12 11/26/2008 8 Showing Formulas • Use Ctrl + backtick to see the formulas in a spreadsheet. • The backtick button is to the left of the 1 key and is with the tilde ~ key on most keyboards. • It works like a toggle switch. Ctrl + ` to see the formulas, then Ctrl +` to hide the formulas. 11/26/2008 9 Parts of a Formula 1. = the equal sign is the first part of a formula 2. A3, C7 the cell reference or cell address is the second part of a formula or a literal number such as 3 or 79 3. +, -, ^, *, / the operand is the third part of a formula 11/26/2008 10 Entering a formula • = A7 * A8 is the format for a formula- equal sign, cell reference, operator, cell reference • Put formula in cell where you want answer to show • Can have complex formulas =A1+A2+A3+A4 *C6/79 • Don’t forget Order of Evaluation- use parenthesis to require formulas to be entered correctly =(2+5)*6 is not the same as = 2 + (5*6) 11/26/2008 11 Enter a Formula 2 • Open Excel and enter 2 numbers in cell B2 and C2 • In Cell D2 enter the formula = B2 + C2 • Your answer should be the sum of the two numbers you entered. • Change the number in cell B2. Your answer should still be the sum of B2+C2 • Use all the different operators -, *, /,^ in your formula and see what happens 11/26/2008 12