The Power of Excel Formulas and Functions
Unleash the potential of Excel with a comprehensive guide on formulas, functions, and operators. Learn the syntax, order of operations, cell references, and more to streamline your calculations and data manipulation. Dive into the world of nested functions, logical tests, and the essential Ribbon and Status Bar features. Master the art of creating efficient spreadsheets and boost your productivity with Excel's powerful tools.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Excel Formulas & Functions Hamden Public Library info@hamdenlibrary.org www.hamdenlibrary.org/computerlab
Agenda Basic Functions Function Library Formulas Formula Syntax Mathematical Order of Operations Mathematical Operators Simple Formulas Practice Other Formula Ribbon Status Bar (sums stuff for you) Naming Ranges and Constants Copying Formulas Cell References in Formulas Ranges Absolute Partial Absolute Relative IF Statements IF Statement Syntax Uses Nested IF Statements Functions Function Syntax Nested Functions
Glossary Formulas: instructions that tell Excel to perform a calculation of some type (can be mathematical, comparative, referral, or logical). Functions: Predefined formulas that perform calculations on user defined arguments. Syntax: arrangement of data and symbols in a formula. Argument: Data being input into a function. Operators: symbols like + - % <> that tell Excel to perform certain calculations. Cell Reference: Notation telling Excel to insert the data of a particular cell into a formula/function. Range: A group of cells being referenced in a formula, e.g. A1:A10 is a range containing cells A1 through A10. String: a series of alphanumeric characters, e.g. I love Cats is a combination of letters and spaces, all of which are characters. Input: the data and arguments that are used by a formula/function to perform calculations. Output: the data or text that is produced by a formula and displayed in the cell. Constant: A value in a formula that is not calculated or referenced from another cell. Nested Functions: Functions that have other functions as arguments. Ribbon: Menu bar at the top of Excel s window, divided into a number of Tabs. Status Bar: bar at the bottom of Excel s window. Tells you the status of Excel and whether any calculations are being performed of what action is occurring Logic Test: Expression that produces a true or false output. Can be an independent formula or part of a function (like an IF statement).
Formulas Formulas are a set of instructions that tell Excel how to calculate the value of the cell they are in. Most often this is used to perform mathematical equations and display an answer. Can also be used to display text or check/compare the contents of other cells. All Excel Formulas follow a few rules: All formulas begin with an equals sign = . Without it, Excel will not recognize the entry as a formula. Numbers and cell references (A1 for example) are Values. These are data that are being calculated/evaluated by the formula. Symbols like + - * / <> are Operators, these tell Excel what calculations/evaluations to perform. Operators have a set Order of Operations where certain calculations will always be performed before others. You can use parentheses to change which calculations are performed first. Other cells (or ranges of cells) can be referenced in a formula; these are added by typing the cell address into the formula, or by using the mouse to click (or click and drag for a range) on the cell you want to reference. e.g. =A1+B1 will use the values in A1 & B1 to perform the calculation.
Operators Special symbols within the formula that tell Excel which calculations to perform. Operators are used in mathematical, comparative, text & reference calculations and in Concatenating (combining) strings of text. Mathematical operators: have Excel perform math ( + to add, - to subtract, * to multiply, etc ). Comparative operators: compares two values using criteria set by the operator and returns a true or false output. > is greater than , =1>2 would return as False because 1 is not greater than 2. <= is less than or equal to , =10<=10 would return True because 10 is less than or equal to 10. <> is not equal to , = Tom <> Dave would return True because Tom and Dave are not the same string. Text operators: tells Excel how to deal with letters and words in a formula. Reference operators: used when specifying ranges of cells to tell Excel which cells to utilize.
Operators List Arithmetic: + Addition - Subtraction (can also denote negative numbers. e.g. =2--1 would be 2 minus -1) * Multiplication / Division ^ Exponentiation (raise to the tenth power, etc.) % Percentage (divides by 100) Text: tells excel all characters appearing between the quotation marks are a string of text Combines multiple strings of text together & Reference: : Range, tells Excel to use all cells between two points (E.G. A1:A10 is all cells between A1 & A10). And, used when giving multiple values or criteria. SUM(A1,A3,A5) will add cells A1, A3 & A5 together. Space Intersection, tells excel to only use cells that appear in all ranges given. e.g. A1:B10 A4:B6 will only count cells A4, A5, A6, B4, B5 & B6 because they appear in both ranges. , Comparative: > < >= <= = <> is greater than is less than is greater than or equal to is less than or equal to is equal to is not equal to
Order of Operations Operators have a set order where certain operators are always calculated before others. If two operations share a step (multiplication/division or addition/subtraction) Excel will calculate them in order from left to right. The Order of Operations is as follows: 1. Reference Operators (: Space ,). 2. Negatives (-). 3. Percentages (%). 4. Exponentiation (^) 5. Multiplication & Division (* /). 6. Addition & Subtraction (+ -). 7. Concatenate (&). 8. Comparison (< <= > >= <> =) For example, =M21+M21*6.35% would first turn 6.35 into 0.0635, then multiply cell M21 by 0.0635, then add M21 with the result of M21*0.0635. If M21 was 10 the result would be 10.635 (10*0.0635=0.635, 10+0.635= 10.635). This order of operations can be changed by using parentheses; Excel will always calculate operators in parentheses before other operators. Normally, for instance =6+10/5-1 will equal 7 (10/5=2, 6+2-1=7). Using parentheses =(6+10)/(5-1) will change the answer to 4 (6+10=16 and 5-1=4, 16/4=4) as the addition/subtraction steps will now occur before the division step.
What is a Cell Reference? When creating a formula you might not always know what value you will want to calculate ahead of time. To get around this, Excel lets you reference the values in other cells in a formula, which allows you to change the values used in a given formula on the fly. If you have a formula that references a cell, A12 for example, Excel will take the value of that cell and insert it into the formula. If A12 = 10 and you have a formula that has =A12/2, Excel will read the formula as =10/2 If you change A12 to = 100, Excel will now read the formula as =100/2 without you having to make any changes to the formula itself. Multiple cell references can be used in a single formula; in fact, every value in a formula can come from a cell reference. E.G. =A1+A2*A3 If A1=2, A2=4, A3=2 =A1+A2*A3 would be =2+4*2 which would equal 10
Ranges A range is a group of cells that are referenced for use in a function. Ranges are shown as two cell references with a colon between them. The two cell references are the first and last cells in the range respectively. An example range, A1:B10, includes 20 cells (cells 1-10 in columns A & B). They are designed to save time by condensing the formula into a format that is much easier to read and type. They do this by allowing you to reference a group of cells instead of having to type out cell references one at a time. For example, the following 3 formulas all do the exact same thing: add all the referenced cells together. =A1+A2+A3+A4+A5 =SUM(A1,A2,A3,A4,A5) =SUM(A1:A5) Using a range like the one in the final formula allows for much shorter formulas/functions, which are easier to read and type.
Referencing cells in a formula There are two ways to add references into a formula (or function): manually typing the cell reference & selecting the cells using the mouse. Multiple references can be combined using operators like + or (or inside functions using commas and parentheses). Keyboard When writing a formula, type the address of the cell you want to use (e.g. A1). No special marks like quotations are necessary, you don t even have to capitalize. If you are referencing a range: type the address of the first cell in the range, then a colon, and then the last cell in the range with no spaces between any of these (e.g. A1:B10). If done correctly the cell address will change from black to a color (what color depends on the number of references) and a corresponding outline will appear around the referenced cell, letting you know that Excel is now referencing a cell in the data sheet. Mouse When typing the formula, left-click a cell on the datasheet. Excel will insert a cell reference into the formula where you are currently typing (if you were editing a formula and were in the middle of the formula, the reference will be inserted there). If you left-click and drag when editing a formula, Excel will insert a reference to a cell range. The range will contain all the cells between the cell where you first clicked and the cell where you release the mouse button.
Absolute VS Relative References Excel tries to make copying formulas easy by changing which cells are referenced when you copy & paste a formula to a different cell. This is because the cell reference s position is relative to the cell with the formula. Often this is very useful, but sometimes you want to have certain references to not change between formulas (say multiplying different payments against a tax rate). In that case you can use absolute references to tell Excel to always use a specific cell or range. In this example, if we copied cell B1 (which contains the formula =A1+2) and pasted it into cell B2, the formula pasted into cell B2 would be =A2+2. To make a cell an absolute reference, type dollar signs in front of the column letter and row number in the cell reference (e.g. $A$1 is an absolute reference for cell A1). Alternatively, you can also select an existing cell reference and press the F4 button. What do they Look Like? Relative: Absolute: Partial (Row): Partial (Column): $A1 A1 $A$1 A$1 You can also create partial references that will only prevent either the column or row from changing. Example: copying a formula =$A1+2 means the row number of the reference can change but the column never will.
Functions Functions are essentially pre-made formulas with assigned names. For instance, the SUM function adds together every value or cell reference specified. Function Suggestions All functions all made up of several parts: Function Name: the name of the function you are using. Just start typing the function name and a list of function names will appear. Double left click the one you want or just type out the formula. The Function name must be immediately followed by an open parentheses. Parentheses:Contain a function s arguments. The first (open) parenthesis is immediately after the function name, the second (close) parenthesis is placed after all of a function s arguments. Arguments: The data inputs that go into the function. Arguments can be values, ranges, criteria or other data inputs. Most (but not all) functions have one or more arguments. The arguments are always contained within the function s parentheses. The Function will look something like this: Tooltip Guide =Function_Name(Arguments) If you are not sure what type of arguments go where in a function, a tooltip will appear next to where you are typing to tell you what arguments the function needs.
The Ribbon: Formulas Tab Function library: lets you search for and insert predefined functions Name Manager: lets you define and name ranges of cells (Example: naming cells A15 through A 20 Totals ). Insert Function button: opens the formula wizard (which provides some instructions and descriptions of a function). Formula Management tools like: Error Checking Trace Precedents/Dependents Show formulas
Function Library The function library contains (almost) all the functions Excel has to offer Hovering the mouse over a function gives a brief explanation about what that function does. If you need a more detailed explanation, you can click on the Tell me more button to open the Excel help window. Functions are divided up into various categories, based on what they do. You can even search for the function you need by using the Insert Function button and typing a description of what the function does. To insert a function into the cell you currently have selected, either use the Insert Function button or select one of the function categories and then pick a function from the dropdown list. An Insert Function dialog box will appear and allow you to enter the function arguments.
Insert a Function There are several steps to insert a function into Excel: 1. Select the cell where you want to place the function. 2. Select the function that you want to use either by selecting the function name from the category dropdown lists or by clicking of the Insert Function button and then search for the function that you need, select and press OK. OR
Insert a Function (continued) Excel will insert an equals sign, the function name and open and close parentheses into your selected cell. 3. 4. A Dialog box will appear asking you to fill in the function s arguments. Enter the arguments for the function in the spaces provided. If you need to reference a cell/range, you can press the button, which allows you to use the mouse to select which cells/range you would like to add to the formula The dialog box also provides an explanation of what the function does as well as explain what each argument in a function is At the bottom of the box a preview of the formula result will be displayed 5. Press OK to finish inserting the function. If you made any errors, Excel will let you know about it.
Some Useful Functions Sum: Totals up the values of a range number of decimal places. Count: counts the number of cells in a range Date: enter in year, month and day arguments and Excel will put them in a date format. Average: calculates the average of the values in a range of cells Now: gives the current date and time Max & Min: calculates the largest/smallest value in a range of cells Today: gives the current date Convert: changes values from one measurement system to another (e.g. miles to kilometers) Subtotal: Combines a number of functions like Count, Average, Sum, Min and Max PMT: Determines how much a loan period will be for a given period (months, years. You decide) Vlookup & Hlookup: used to search for and return values DateDIF: compatibility/secret function, returns the difference between two dates in days, months or years Index (& Match): like Vlookup & Hlookup, used to search for and return values. Round (Roundup, Rounddown): Rounds values to a
Nested Functions Nested functions are simply functions that act as arguments for other functions (functions within functions). To do this you simply type a function inside the parenthesis of another function. An example would be if you wanted to use the AVERAGE and ROUNDUP functions to round a cash amount to the next highest cent. =ROUNDUP(AVERAGE(A1:B5),2) The ROUNDUP Function has 2 arguments: what is being rounded and the number of decimal places you want to round to. ROUNDUP(Value,Decimal_Places) In this case the value being rounded is the average of cells A1 through B5. To get that number we use the AVERAGE function and place it right where ROUNDUP s value argument would be.
Named Ranges In Excel it is possible to give a range of cells a specific, custom name. This is done to make it easier to find and reference the range when using them in functions. For instance, in the image below, rather than using the range A2:A10, we can name the range using the name manager and then incorporate that name into a formula (in this case the range is named one_two_threes). Using tables will create named ranges automatically. The table itself will be a range, as will any of the columns in the table (rows are not treated as ranges in tables with the exception of the Totals row). The nice thing about using tables for your named ranges is that since they are dynamic objects the named range will expand with the table as you enter data.
Creating Named Ranges Named ranges are created by either making an Excel table, or using the Name Manager on the Formulas tab to create a named range. First, select the range of cells that you would like to assign a name to. Next, click on the Define Name button on in the Formulas tab of the Ribbon. Finally, assign a name to the range you have selected (the name must begin with a letter or underscore, cannot contain any spaces and must be unique). Press OK and you now have a named range you can refer to in formulas.
IF Statements The IF function and its variants is one of the most powerful functions in Excel. The IF function is one of a number of logical functions in Excel that test and respond to conditions in your Excel workbook. The IF function works by performing a logic test to see if a criteria, specified by the user, has been met and then performing an action based on outcome of the logic test. The IF Function is constructed like this: =IF(Logic_Test,Value_if_True,Value_if_False) Example: =IF(A1>A2, A1 is more , A1 is not more ) This formula checks to see if A1 is Greater than A2. If A1 is larger the Function output will be the phrase A1 is more . If A1 is less than or equal to A2 the output value will be the phrase A1 is not more . The output can be anything from a phrase like the one above to a static value, to other formulas or functions (even other IF functions). You can use this for all sorts of things, depending on what you need.
IF Variants (and some other Logical Functions) COUNTIF, SUMIF & AVERAGEIF: Arguments:(Range,Criteria) these three functions combine the logic test of an IF statement with some of the more popular mathematical functions. These functions will go through a range of data and will only count/average/sum the cells in the range that fit the criteria. multiple criteria. For AND all arguments must be true, for OR any of the arguments can be true. Both functions use comparative logic tests for arguments and can have between 2 and 255 arguments. ISBLANK: Arguments: (Reference) ISBLANK and functions like it (ISNUMBER, ISTEXT) are used to determine what kind of content is in a cell. This can be used with IF statements to perform actions based on the type of content in a cell. COUNTIFS, SUMIFS & AVERAGEIFS: Arguments:(Range,Criteria_Range1,Criteria1, ) same as above but allows you to fit multiple criteria into a single formula. AND & OR: Arguments:(Logical1,Logical2, ) the functions AND & OR are used to combine multiple Logic tests together in order to test for
Nested IF Statements Just like regular functions, it is possible to combine multiple IF statements together, which allows the formula to perform complex tasks. Examples include discounts based on the size of a purchase, checking for errors in cells, assigning values/or categories based on cell content (e.g. pass/fail or grade calculation), or any number of applications that you can think of. Nested IF statements are constructed by simply inserting an IF statement into another IF statement, usually in the value if true or value if false argument positions. Excel will work its way through the statement from left to right, checking each criteria and then executing an action or moving on to the next criterion. One thing to be very careful about when using nested IF statements is to make sure that your logic tests are set up in the correct order. When it comes to comparing values (greater/less than), the order of the values being tested is important. Generally when doing greater than test the larger values first, when doing a less than test the larger values go last.
Example Nested IF Statement For example, you want to calculate the size of a discount based on the size of an order with a 5% discount at $25, a 10% discount at $50 and a 20% discount at $100. An IF statement for that would look like: =IF(price>=100,price*0.2,IF(price>=50,price*0.1,IF(price>=25,price*0.05,0))) Lets break this down into steps: No No No Discount = 0 Move to next criterion Move to next criterion Is the price greater than or equal to 25? Is the price greater than or equal to 100? Is the price greater than or equal to 50? Yes, Discount = price*0.2 Discount = price*0.1 Discount = price*0.05 Yes Yes Yes
