Fundamental of Excel Formulas Outline 1) Introduction: 2) The Title Bar (and Quick Access Toolbar): 3) The Ribbon: 4) Using the Excel Help System: 5) More Formula Auditing: 6) Formulas: Standard Operators: 7) Standard Functions: 8) Translating Mathematical Formulas to Excel Formulas: 9) Summary: 1) Introduction: In this lecture we will focus on how to write formulas in Excel, using the standard mathematical operators and functions. In later lectures we will expand the number of functions that we use, going beyond the standard mathematical ones. But first, we will look at some features of Excel that help us navigate in to the operations we want to perform on worksheets in a workbook, including how to get help within Excel. We we also explore these features in more detail in later lectures. 2) The Title Bar (and Quick Access Toolbar): The Title Bar is at the top of the screen. It holds four different areas: a) The Office Button (top-left) b) The Quick Access Toolbar (QAT): Save, Undo, and Redo icons (to its right) c) The name of the workbook (centered) d) The Excel window controls: Minimize, Maximize & Terminate icons(top right) a) The Office Button gives us access to some Excel commands: New, Open, Save, Save As, Print, Prepare, Send, Publish, and Close. At the bottom we can set defaults for Excel Options as well as Exit (Terminate) Excel. You are welcome to explore these options, although we won't be using the Office Button much. You might want to surve someof the Excel Options, just to see what is there; later in the quarter we will explore some of these options. b) We have seen how to click icons on the QAT to save workbooks, and undo/redo commands. We can customize the contents of the QAT by moving its location and by adding or removing icons (and changing their relative position). -->Click the down-triangle (disclosure) to the right of the QAT -->Select "Show Below the Ribbon" and watch where the QAT moves downward -->Click the down-triangle and select "Show Above the Ribbon" again to restore --> its original location -->Click the down-triangle again -->Select "Sort Ascending" (notice there is no "check" to it right). --> Notice a fourth icon is added to the QAT: A/Z followed by a down-arrow -->Click the down-triangle again -->Select "Sort Ascending" (notice there is a "check" to it right). -->Notice the fourth icon has been removed from the QAT We can put an icon for any of Excel's hundreds of commands into the QAT and also position these icons in any order. -->Click the down-triangle again -->Select "More Commands" -->In the "Choose commands from" pulldown menu, select All Commands -->Scroll down the list underneath it to see the icons we can add to the QAT, --> which are in alphabetical order -->Scroll down to "Custom Sort" and click on it (it becomes highlighted) -->Click the "Add>>" button between the lists, and watch "Custom Sort" copied to the list on the right. -->Use the up/down arrow to the right of this list to move "Custom Sort" to --> come after "Save" but before "Undo" -->Click "Save" in the right list (it becomes highlighted) and use the up/down --> arrows to the right of that list to move it to the bottom -->Click OK and observe the new contents/order of the QAT To illustrate the use of the Custom Sort icon briefly -->Enter the numbers 1, 3, 5, 4, and 2 into cells A1:A5, and select these cells -->Click the Custom Sort icon -->Click OK and watch the numbers in these cells become sorted -->Click the Custom Sort icon again -->In the last pull-down menu select Largest to Smallest and click OK, and --> watch the numbers in these cells become sorted in reverse order -->Delete the values in cells A1:15 Now let's restore the QAT by removing Custom Sort and moving the Save icon. -->Click the down-triangle again -->Select "More Commands" -->Click "Custom Sort" in the right list (it becomes highlighted) -->Click the "Remove" button between the lists, and watch "Custom Sort" --> disappear from the list on the right. -->Click "Save" in the right list (it becomes highlighted) and use the up/down --> arrows to the right of that list to move it to back to the top Besides icons corrsponding to commands in the left list, there is a icon you can choose and add/remove/reposition in the right list. If you put many icons in the QAT, you can use seperators to group them meaningfully. There is another way to move the QAT, and add/remove/reposition the icons on it. Just right-click the QAT (or the specific icon you want to remove) and select the option you want. So, using these commands we can customize Excel's QAT to quickly perfrom any Excel command. Different users use different commands, so each user of Excel can configure the QAT to his/her preferences. As we learn more commands, you can put the ones you find useful in your QAT. Finally, when we update the QAT, the change is "sticky": the new QAT will appear whenever we restart Excel. 3) The Ribbon: Beneath the Title Bar is the Ribbon, which contains icons/menus for most of the Excel commands. The Ribbon is divided logically into several sections, each headed by a tab indicating the class of commands in that section. Under each tab, the commands are grouped by subclasses. The Home tab is the most important, so we document it the most detail. We show the other tabs in less detail and will explore them later (in fact, we will explore parts of the Formulas tab in this lecture). a) Home: 1) Clipboard: Cutting, Coying, and Pasting Data 2) Font: Font, Size, (Bold, Italic Underlined), Border, Fill, Color 3) Alignment: Vertical, Horizontal, Angle, Indenting, Wrapping, Merging 4) Number: Formating Numbers 5) Styles: Condition Formating, Table Formating, and Cell Styles 6) Cells: Insertion, Deletion, Formating 7) Editing: Autosum, Fill, Clear, Sort & Filter, Find & Select b) Insert: Tables, Illustrations, Charts, Links, Text c) Page Layout: Themes, Page Setup, Scale to Fit, Sheet Options, Arrange c) Formulas: Function Library, Defined Names, Formula Auditing, Calculation d) Data: Get External Data, Connections, Sort & Filter, Data Tools, Outline e) Review: Proofing, Comments, Changes f) View: Workbook Views, Show/Hide, Zoom, Window, Macros e) Developer: Code, Controls, XML e) Add-Ins: Menu Commands The top of the Ribbon (where the tab names are) at the right also holds a question mark icon (Help - discussed next) and the workbook window controls (which minimize, maximize & terminate not the entire Excel application, but just the current workbook: an Excel application can be working on several workbooks at the same time). 4) Using the Excel Help System: "Give a man a fish and feed him for day; teach a man to fish and feed him for his life". I cannot teach you everything that you need to know about Excel to solve all your mathematical problems. But I can teach you about the most important features and also teach you about the "help system". By learning to use the help system in Excel, you will be able to explore the system by yourself and get help automatically when you run into problems. The more you use the help system, the better you will get at using it. So, you might even want to practice using it to learn more about topics that I teach you this quarter. Although you cannot always find relevant answers to your questions there, it is always an excellent place to start looking for answers. And the more you know about Excel, the easier it is to interpret the information from the help system. To start the Help system, click the Help icon (the question mark in a blue circle) on the top right of the Ribbon. If this location is too obscure, you can always add the Help icon to the QAT using the tools you learned above. A window whose Title is Excel Help will pop up, with its own icon-containing toolbar. There are two main options at this point. 1) Type some keywords related to the problem into the text box to the left of the magnifying glass icon (labeled search) and then click this icon. 2) Click one of the 36 topics (18 on the left and on the right) in the window. These are like book chapters; clicking any chapter shows either its Subcategories or Topics: if it shows Subcategories, clicking one will lead to a further refinement of Sub-Subcategories (which eventually lead to Topics) and Topics. We can recognize Topics because they are always preceeded by the blue questionmark icon. Clicking a Topic will show text related to help on that topic. We can see the Chapters, Subcategories and topics information in another way in the help system. 3) Click the blue closed book icon (meaning show Table of Contents; that icon changes to an open book) at the second to last position on the toolbar. Select a Chapter and see its Subcategories (shown with another blue book icon) and Topics (again, shown as a blue questionmark icon). As above, clicking a topic will show text related to help on that topic. If you click the open book icon, it will remove the table of contents and the icon again looks like a closed book. Recall this icon "toggles" between seeing and hiding the table of contents. -->Start the help system -->Type "sum function" in the search text box and click Search --> You will see the top 1-25 matches of 100 found in the help system. -->Click on the purple "SUM" to get help on understanding/using the SUM --> function that we briefly discussed in lecture 1 -->Click the back button (the left-arrow icon first on the Excel Help toolbar) --> to return to the results. -->Click "Math and trigonometry" (after "Help>Function reference") to see the --> general Category in which help for this particular function is found. --> It shows many topics, each a function that you can get help on (try one) -->Try ctrl/f key and enter SUM to find functions that contain the word SUM -->Click the next button in the Find pop-up window a few times; then cli -->Click the back button -->Click "Function reference" (after "Help") to see the general Chapter in --> which help for this particular Category is found. Note that there are --> some Subcategories (like "Math and trigonometry") and some Topics (like --> a "List of worksheet functions (by category) In fact, we would reach this same "page" if we started help and then clicked Function reference (5th down on the left in Browse Excel Help). Likewise, if we click the blue closed book icon on the toolbar and then click the "Function reference" book (9th down: the Browse Excel Help reads left to right, top to bottom, when showing book chapters) and then click the "Math and trigonometry" book and then click the SUM function topic we get to the same explanation we reached more quickly via searching. Of course, if we didn't know the exact name of a function, seeing them all on the left might remind us which one we are looking for. So, we can use whichever of these two different forms help seems most useful. You should become adept at using Excel's help system. As you learn and use Excel, you will often find that you remember in broad strokes a function or feature that is useful for the problem at hand, but will need to be reminded of its details. I often find myself using the help system to remember/fill in those details. So my advice is that when you learn some Excel feature in lecture, find it in the help system, both to examine its details again (maybe the explanation there will help you understand the feature better), and to feel more comfortable using the help system. 5) More Formula Auditing: We have already seen one mechanism to examine the formulas in a spreadsheet: the ctrl/` command toggles all the cells between "value" display mode and "formula auditing" display mode. There are other mechanisms that give you a finer control over forumula auditing and a more graphical representation of what cells use what other cells in their calculations. -->Click the Formulas tab and notice that there are six icons and labels on the --> left side of the group named "Formula Auditing" on the Ribbon These icons are Trace Precendents, Trace Dependents, Remove Arrows, Show Formulas, and Evaluate Formula. As we saw before, clicking Show Formula is just like typing ctrl/` which toggles whether the spreadsheet is showing values or formulas. If you have problems remembering the control sequence, you can put the icon for this command on the QAT). For the first two (Trace Precendent/Dependents) we first select a cell about which we want information and then click either of these icons. The "precedents" of a cell that contains a formula are all the other cells the formula refers to (whether relative or absolute or mixed); it is simpler to call these the "inputs" to the cell rather than the "precedents". If you click this icon after selecting a cell WITHOUT a formula, Excel will pop-up an error. The "dependents" of a cell are all the other cells that contain formulas that refer to (depdend on the value of) this cell; it is simpler to call these the outputs of the cell rather than the "dependents" Inputs and Outputs are symmetrical: if cell X is an input to cell Y then cell Y is an output of cell X. We can just look at a formula in a cell and see all its precendents (they are each named explicitly or are part of a range in the formulat there), but to find all the dependents, you would have to look at every other cell on the worksheet. If we select a cell and click the... a) Trace Precedents icon: Excel will draw a blue circle in each cell or the first cell in each range whose value is used in the selected cell and draw a blue arrow from that cell/range to the selected cell. b) Trace Dependents icon: Excel will draw a circle in the selected cell and draw blue arrows to every cell that has a formula that refers to the selected cell. If a cell has a range of values as dependents, Excel will draw a blue box around the entire range and put one circle in the range with an arrow leading to the selected cell. Likewise for precedents. If we select a cell, we can also click the... c) Remove Arrows icon: Excel will erae all its circles and arrows. d) Remove Arrows pull-down list (the down-arrow to the right of Remove Arrows) and then select Remove Precedent Arrows): Excel will erase just the circles and all arrows leading to the selected cell(s), those arrows created by the Trace Precedents command. e) Remove Arrows pull-down list (the down-arrow to the right of Remove Arrows) and then select Remove Dependents Arrows): Excel will erase just the circle and all arrows leading from the selected cell(s) to other cells, (those arrows created by the Trace Dependents command. Without selecting any cells, if we click the Remove Arrows icon, Excel will erase all circles and arrows on the current worksheet. Sometimes when exploring a spreadsheet we will trace the precedents of a cell, then trace the precedents of each of those cells, going "backwards" in the calculation to find cells containing values (not formulas) on which the computation is based (likewise for tracing dependents multiple times forwards to find the ultimate results). This is especially useful if we are debugging (finding an error in) a complicated worksheet. -->On the Fibonacci workheet for lab 1, select cell B13 (it contains 144) -->Trace it precedents (cells B11 and B12) -->Trace it dependents (cells B13, B14, C13, C14, and B33) -->Click Remove Arrows to remove all these arrows Finally, there is yet another method to audit formulas: double-clicking on cell will show the formula in the cell and highlight the cells that are Precedents of that cell. -->Double-click cell B13 and observe the formula =B11+B12 and that Excel --> highlights in blue these two cells -->Press the Esc(ape) key to "deselect" that cell -->On the Lecture 1 workheet for lab 1, select cell B31 (it contains 55) -->Click Trace Precedent (Cell B31 adds up all the values in B26:B30) -->Click Trace Precedents again: each of the cells B26 to B30 show it --> depends on the cell to its left (it squares that value) -->Click Show Formulas and observe the arrows remain while the formulas now --> appear in the celss -->Click Show Formulas again (it toggles back to showing values) -->Click Remove Arrows to remove all these arrows Finally, formula auditing can help us to understand a model in Excel (A model is just a bunch of interconnected cells on a worksheet). It is useful when we are trying to debug a model that we are building, or extend a model that already works to be more useful: maybe we built it, maybe somebody else did. 6) Formulas: Standard Operators: We will now begin discussing how to convert formulas written in standard mathematical notation into Excel formulas. Primarily the problem is converting a formula written in two-dimensions into a one-dimensional representation (just a sequence of characters). Along the way we will discuss operator precedence and the use of parentheses to alter precedence. First, recall that cells can store values that are text or numbers. They can also store "boolean" values of which there are only two: TRUE and FALSE. We will learn that relational operators (e.g., = and <) produce boolean values as results and some functions (e.g., AND, OR, and NOT) use boolean values to calculate results that are other boolean values. We will find very interesting uses for text and boolean values soon, but for now most of our formulas will be numerical. Formulas are built from text/numbers/cell names and operators and functions. We classify Excel's operators as follows arithmetic operators: + - * / ^ (add subtract multiply divide power) textual operators: & (catenate) relational operators: < <= = <> >= > (note that <> means "not equal") The symbol ^ is called the caret symbol and is used for raising a number to a power; it is found over the 6 key on the keyboard. In all the following examples, instead of using cell names (like A12) we will instead just use letters (like a). This makes discussing this topic simpler. Of course, when we actually enter formulas in spreadsheets, we will need to refer the the values by specifying their cell name. Likewise, we will sometimes leave out the = from our formulas (whereas in Excel ALL FORMULAS MUST START WITH =). First, note that multiplication in Excel is EXPLICIT. In math we may write 2a but in Excel we must write 2*a (two times a, the * operator means multiply). Also, in math we may write 2(a+b) but in Excel we must write 2*(a+b). If we leave out the multiply operator, Excel will typically display an error message. --> Enter the formula =2(1+2) into Excel and observe what happens Sometimes Excel will suggest a correction, but don't depend on it being the correct one. Look carefully at what it suggests to determine if that fix is really what you wanted. Likewise, to raise a number to a power (say square b) we write b^2 (b raised to the power of 2). In mathematics we write the 2 as a superscript, like 2 b but in Excel formulas there are no superscripts (formulas are all written on one, possibly very long, line), so we use the ^ operator, which means to raise its left operand to the power of its right operand: b^2 means to "raise b to the power of 2". We can use non-integer powers: b^.5 raises b to the .5 power (which calculates the square root of b). We can use negative powers: b^-2 is the reciprocal of b squared (1/b)^2 - one divided by the quantity b squared). This convention follows standard algebra. --> Enter the formula =2^-2 which evaluates to (1/2)^2 (one half squared), or --> just .25 Before exploring formulas further, we should understand a bit about operator precedence. This topic determines which operators Excel applies to the operands in its formulas first, second, ... last. You should memorize the following table, which classifies operator precdence highest to lowest. Operators on the same line have the same precedence (so * and / have the same precedence). Most of this information should be familiar to you from your mathematics background; it is important to know about precendence when talking to a human, but it is critical to know about precdence when talking to a computer. Most programming languages use the same precedence that Excel uses. Precedence - Highest at top - prefix (as in -b meaning "negative b"; the - below is subtraction) ^ * / + - & < <= = <> >= > (all relational operators) Precedence - Lowest at bottom Excel applies the highest precedence operators first. So, in the simple formula 2*b^2 Excel knows that ^ has a higher precedence than *, so to calculate the value of this formula, it first applies the ^ operator (raising b to the 2nd power) and then multiplies the 2 by that value. Another way to write the same formula is to use redundant parentheses. 2*(b^2) These parentheses are redundant, because operator precedence requires that Excel apply ^ before * anyway. But they are useful to illustrate the point I am trying to make here. In Excel we typically use parentheses only when they are necessary; not when they are redundant. But, in large/complicated formulas, we might sometimes use redundant parentheses to make the formulas easier for humans to read. For now, know when parentheses are needed; the more we work with complicated formulas the more we will be able to judge how to write them simply. Suppose that we wanted to multiply 2 by b first, and then raise that product to the power of 2. Here we must use parentheses (to override operator precedence). (2*b)^2 The parentheses force Excel to apply the * operator before the ^ operator, finishing the calculation in the parentheses before squaring that value: Excel cannot square something in parentheses until it knows what to square. Note that putting spaces in formulas DOES NOT change operator precedence. So writing 2*b ^ 2 is no different than writing 2*b^2; sometimes we to use extra spaces to emphasize (for humans) the actual precedence, and would write this formula as 2 * b^2 putting spaces around the lower precdence multiply operator, visually indicating it is evaluated after the power operator. But such spaces DO NOT change which operator is done first. Likewise, if we want to add and subtract a bunch of values and then cube the result, we need parentheses to write (a + b - c)^3 because writing a + b - c^3 would first cube c, then add b to a, then subtract c^3 from that previous sum. Look inside the parentheses. The two operators, + and -, have the same precedence. When operators in a formula have the same precedence, Excel applies them left to right, so it applies the + first and then applies the - second, and then finally applies the ^ operator to the entire parenthesized formula. Using this "left to right" application of equal precdence operator, how does Excel calculate 2^3^4? Excel calculates 2^3 first (which is 8) and then it calculates 8^4 second (which is 4096). It is just as if we had written (2^3)^4. The equal precedence rule often causes students to make mistakes converting formulas with products in denominators, like a ----- 2b into Excel formulas. Students want to write this as a/2*b (or as a / 2*b but remember spaces are irrelevant), but because / (division) and * (multiply) have the same precedence, if we write a/2*b in Excel first applies the / operator and second applies the * operator -as if we wrote (a/2)*b which is mathematically more like a ab ----- b or ----- 2 2 To convert the original two-dimensional mathematical formula into a one-dimensional Excel formula correctly, taking into account left-to-right evaluation of equal precedence operators, we must write instead a/(2*b) which forces Excel to evaluate the entire denominator first, before dividing the numerator by that amount. This is the MOST COMMON mistake made by students when writing formulas in Excel (that and forgetting to write explicit multiply operators). You should practice writing simple formulas that you know into Excel and check them (using a calculator) against the values calculated by Excel. In the upcoming lab, you will have to do this for a variety of complicated formulas. Enter the information below to see some examples of non-mathematical operators. -->Enter fred and astaire in cells A13 and B13 respectively -->Enter ginger and rogers in cells A14 and B14 respectively -->Enter eric and blore in cells A15 and B16 respectively -->Enter the formula =B13&", "&A13 in cell C13 and fill that same --> formula in C14 and C15 -->Enter the number 5 in cell A16 -->Enter the formua ="I am "&A16&" years old" in cell C16 -->Widen column C to show the values in this column completely We can widen/narrow column B by positioning the cursor over the separator between the C and the D (the cursor changed to a vertical line with arrows going right and left from it) and double-clicking (it makes the column large enough to contain all the information in it) or dragging (pressing, moving, releasing) the cursor left (narrow it) or right (widen it). The & (catenate) operator creates one large text value from smaller text values and numbers. These smaller texts and numbers can be in cells (as the reference A13, B13, and A16 are), or enclosed in " -read as the quote mark (as is ", " or "I am" or " years old" are). All the smaller text values and numbers appear in the result in the order that they are listed. -->Change the value in cell A16 to 6 and see how Excel recalculates the formula --> in C16; change the value to eighteen and see the formula recalculated; --> change the width of column C to accomodate the longer result -->Change the value in cell A16 back to 5; change the width of column C back Now let us look at simple uses of the relational operators. We will use these operators quite frequently during the class, but not for a few lectures. But now is a good time to learn a bit about these operators. Each relation operator calculates a boolean value (TRUE or FALSE) based on its operands. They really are a lot like arithmetic operators, calculating a value from their operands, but while the arithmetic operators calculate a number from two numbers, the relational operators calculate a boolean value, typically from two numbers. -->Enter 5 in cell A17 and 6 in cell B17 -->Enter the formula =A17Observe the boolean value; then edit the C17 cell to try each of the other --> relational operator -->Enter the formula =2*A17 < B17 in cell C17 -->Observe the boolean value; then change the values stored in A17 and A18 --> and watch the values in cell C17 change -->Restore the values 5 and 6 in cells A17 and B17 Note in the forumala =2*A17 < B17 the * operator has higher precedence than the < operator (relational operators have the lowest precedence), so Excel first multiplies 2 by A17, and then calculates the < operator on the result. Again, I have included spaces for clarity, puting space around < which is the lower precedence operator, as I did above when writing 2 * b^2, putting space around * which is the lower precdence operator. We can use the "Evaluate Formula" icon (in the "Formula Auditing" group of the Formulas tab on the Ribbon) to help us undestand how Excel is evaluating our formula. We first select any cell containing a formula, then click Evaluate Formula, and then watch Excel compute its value, step by step, by continually clicking on the Evaluate button in the pop-up window. -->In any cell, enter the formula =(1+2*3)^2 -->Select that cell, click Evaluate Formula, then click Evaluate continually --> Watch as each operator is applied (the one to be applied next, along with --> its arguments, is underlined). Ultimatlely the entire formula is reduced --> to a single value, using rules of precendence and parentheses. -->Remove the formula from that cell. Thus, if a formula is computing the wrong answer, we can use the Evaluate Formula icon to help us understand why and then correct the formula. As the formulas we use get more complicated, using Evaluate Formula becomes more important to help us correct our mistakes. Some students find the Evalute Formulate icon so useful, they put it on the QAT. You already know one way to do this. Another way is to right-click any icon on the Ribbon and selecet the "Add to quick access toolbar" option, which adds the clicked icon at the end of the QAT; if you want to move it elsewhere do so by clicking the QAT's disclosure triangle and follows the instructions we've studied before. 7) Standard Functions: Excel knows about hundreds of different functions (see the section above about Help to explore them). Among the most useful and common mathematics functions are ABS Absolute value SQRT Square Root LN Natual logarithm (base e) LOG10 Logarithm to base 10 MOD Modulus/remainder COS Cosine SIN Sine TAN Tangent and there are many many more. The operands of the trigonometric functions are in radians. The functions RADIANS and DEGREES convert back and forth: RADIANS takes an operand in degrees and returns the equivalent radians; DEGREES takes an operand in radians and returns the equivalent degrees. So functions have names, and we calculate with them by always writing the name followed by (). We put their operands in these parentheses. We often talk about the operands of operators and the "arguments" of functions: argument just means the value the function uses to calculate its value. The MOD function might be new to you. It takes two arguments, the first is a number, the second is a divisor). The result of the MOD function is the REMAINDER when you divide the number by the divisor. So MOD(17,5) is 2, because when 17 is divided by 5, the quotient is 3 (5 goes into 17 3 times for 15) and the remainder is 2 (17-15). -->Use help to find the MOD function; read about it and try some examples. We also briefly examined the SUM function, whose argument is a range of cells, and whose result is the sum of all those cells. It is much simpler and less prone to error to to write =SUM(A1:A10) than =A1 + A2 + A3 + A4 + A5 + A7 + A8 + A9 + A10 (did you even notice that I forgot to write A6?). Another way to use the SUM function is to separate numbers, cells, or ranges by commas: =SUM(1, A2, A5:A10) which computes 1 + A2 + A5 + A6 + A7 + A8 + A9 + A10. There is also a function named PI. It is a STRANGE function, because it has no arguments in its parentheses. To calculate the area of a circle whose radius is in cell A1, we write the Excel formula =PI()*A1^2 There is a function name PROPER that has an argument that is text and calculates a result that is text: by capitalizing the first letter in each word in the argument text (as in a "proper name"). So PROPER("john smith") computes the string "John Smith". -->Copy and paste the 3 names in A13:B15 into the cells A20:B22 -->Enter the formula =PROPER(B20)&", "&PROPER(A20) into cell C20 and fill --> that same formula in C21 and C22 -->Observe the result of a formula that use the PROPER text function and the --> & (catenate) text operator. Notice the names are capitalized in the computed cells. Finally, Excel includes the logical (also know as boolean) functions AND, OR, and NOT. These functions take arguments that are boolean values and calculate boolean values as results. 1) AND takes one or more arguments (each a cell or range), separated by commas. It calculates TRUE if all its arguments are TRUE and FALSE if at least one is FALSE 2) OR takes one or more arguments (each a cell or range), separated by commas. It calculates TRUE if at least one of its arguments is TRUE and FALSE if all its arguments are FALSE 3) NOT takes one argument: NOT(TRUE) calculate FALSE and NOT(FALSE) calculates TRUE -->Enter 5 in cell A24 -->Enter the formula =AND(0<=A24,A24<=10) into cell C24 This formula computes whether or not both 0<=A24 and A24<=10, that is, whether A24's value is between 0 and 10 inclusive. -->Enter other values in A24 (negative numbers, large positive numbers, numbers --> 0-10, etc. and observe the result calculated by the formula in cell C24. -->Use the Evaluate Formula icon to watch how this formula is computed with --> various values in cell A24 I encourage you to use Help in Excel to explore some of Excel's functions, and by clicking on them trying to learn/understand what they do. As the quarter progresses, you should feel more and more comfortable exploring Excel and experimenting with it. For now, enter various functions in Excel to see what results they produce. As a final comprehensive example, let's look at a formula for calculating one root of the quadratic equation. This formula exhibits most of the differences between mathematical and Excel formulas: special square root sign which is a function, powers using superscripts, implicit multiplication and a product in the denominator (the most common mistake students make with operator precedence). In two-dimensional notation we would write this formula as __________ / 2 -b + \/ b - 4ac --------------------------- 2a The simplest translation of this formula in Excel is (-b + SQRT(b^2 - 4*a*c)) / (2*a) All the parentheses are necessary. Here I have again put in extra spaces to clarify the operator precdence for humans, but recall that adding/removing these spaces does not change how Excel calculates the formula. Note that the negate symbol in front of the first b is the highest precedence operator (even higher than ^). So, for example, if we wrote -b^2 Excel would calculate -b first and then square it, as if we wrote (-b)^2. -->Enter the text a, b, c, and Quadratic Root into cells D1, E1, F1, and G1 -->Enter the values 2, 6, and 2 into cells D2, E2, and F2 -->Enter the formula (-E2 + SQRT(E2^2 - 4*D2*F2)) / (2*D2) into cell G2 -->Format the Alignment of all these cells to be Ceneter for Horizontal -->Format cell G2 to display five decimal digits (it should show -0.38197) -->Use the Evaluate Formula icon to watch how the formula in G2 is computed Finally, note that there are functions for some of the mathematical operators: e.g., SUM, PRODUCT, POWER. For example we can write the formula a*x^2 + b*x + c as SUM(a*x^2 , b*x , c) Here the SUM function has as many arguments as needed, one for each summed value. In fact, we could even remove the * operators and write it as SUM( PRODUCT(a,x^2), PRODUCT(b,x), c) and finallly, we could remove the ^ operator and write SUM( PRODUCT(a, POWER(x,2) ), PRODUCT(b,x), c) Is there an advantage to using these functions instead of their equivalent operators? I can think of one: we do not need to know operator precedence. Excel always calculate the innermost function first. Also, we can use ranges, as in SUM(A1:A10). Is there a disadvantage? There is an obviouis one: the functions make the formulas larger and often are harder to read than the version using only operators, especially with all the parentheses needed in the functional form. So, use whatever notation seems simplest to you for solving the problem at hand. 8) Translating Mathematical Formulas to Excel Formulas: One method to construct an Excel formula from a mathematical one is look at the mathematical formula and determine which operator is applied LAST. Then write that operator in the Excel formula and translate its left and right operand into Excel formulas. Breaking one big problem into two smaller ones. So, for the root of the quadratic equation, the division betweent the numerator and the denominator is applied last, so we would start with numerator/denominator The denominator is just 2a, so we rewrite the formula as follows, putting the denominator in paretheses to avoid the common operator precdence mistake. numerator/(2*a) Now, the last operator applied in the numerator is the +. So we rewrite the formula as follows, putting the numerator in parentheses to force the + to be applied before the /. (left + right) / (2*a) left + right/ (2*a) would compute incorrectly The left is just -b, so we rewrite the formula as follows. We don't need to put -b in parentheses, because the negate operator (the -) has a higher precedence than + so it will be done earlier anyway.. (-b + right) / (2*a) The right requires using the SQRT function, so we rewrite the formula as follows, using the Excel name for this formula and writing "body" for its argument. (-b + SQRT(body)) / (2*a) Inside the SQRT function (body) the last operator applied is -. So we rerwite the formula as follows. (-b + SQRT(left - right)) / (2*a) The left formulation is just b^2, so we rewrite the formula as follows. We don't need to put b^2 in parentheses, because the ^ operator has a higher precedence than - so it will be done earlier anyway.. (-b + SQRT(b^2 - right)) / (2*a) The right formula performs two multiplications. Since adjacent multiplications are done left to right, the left operand is 4a and the right is c, so we can rewrite the formula as follows. We don't need to put left or right in parentheses, because the * operator has a higher precedence than the + operator. (-b + SQRT(b^2 - left*c)) / (2*a) Finally, we can rewite left as just 4*a and we are done convering the mathematical formula fo an Excel formula. (-b + SQRT(b^2 - 4*a*c)) / (2*a) We can carefully apply this method to convert any mathematical formula to an Excel formula, no matter how complicated it is. Finally, if we are having trouble understanding why a formula is producing incorrect results, we can break up the formula into pieces and calculate each piece in its own cell, and then combine these cells simple to compute the entire result. Of course, we could also use Evaluate Function to help us understand why. For example, if we are having trouble with the formula for one root in a quadratic equation, we can enter a into cell A1, enter b into cell B1, enter c into cell C1, and then we can enter the formula -SQRT(B1^2+4*A1*C1) into cell D1, and enter the formula =-B1 + D1 into cell E1, and enter the formula =2*A1 into the cell F1, and finally enter the formula =E1/F1 into the cell G1. G1 should compute the root, and all the other cells show partial results. I have done this in the Excel spreadsheet that goes with this lecture, but in column 27. In this approach we have used extra cells to calculate different parts of a formula, keeping each part small, and then combining these cells to compute the entire formula. Sometimes using the approach will help us to better understand the pieces of a complicated formula, and allow us to figure out how to write it as one large formula, in which case we can get rid of all the intermediate columns and write the entire formula in one cell. 0) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel. Know how to add, remove, and rearrange icons on the Quick Access Toolbar Know how to choose tabs on the Ribbon to navigate to most of Excel's commands Know how to use the help system, both to search for keywords and to explore Chapters, Subcategories, and Topics. Practice using help! Know about formula auditing beyond ctrl/`: how to trace the precedents/inputs and dependents/outputs of formulas in cells, using the Formulas tab and by double-clicking a cell. Know about numeric, boolean, and text types: values of these types (as well as formulas) can be entered in any cell. Know about the operators (symbols and operator precendence) and functions (with arguments in parentheses) in Excel and how to convert arbitrarily complicated mathematical formulas into Excel formulas. So far, this includes understanding the mathematical operators and functions, and textual operator (&) and functions (e.g., PROPER) and boolean functionse (AND, OR, and NOT). Know how to make Excel show how it evaluates formulas, using the Evaluate Formula icon.