Advanced Excel Formulas & Functions PDF

Advanced Excel Module 1: Part 2 Advanced Formulas and Functions  Learning Objectives Participants should be able to: Create well-structured formulas using relative and absolute referencing, and using named ranges. Understand and use functions in formulas. ...

Advanced Excel Module 1: Part 2 Advanced Formulas and Functions  Learning Objectives Participants should be able to: Create well-structured formulas using relative and absolute referencing, and using named ranges. Understand and use functions in formulas.  SUMIF/SUMIFS The SUMIF function tells Excel to add only cells that meet criteria you choose. For example, let’s open the commission spreadsheet. 1. Click in a blank cell. Type: =SUMIF(A2:A5,">160000",B2:B5) 2. This will give us the total commissions for property values over $160,000. In other words, we are using the SUMIF function to tell Excel to add all the values in Column B where Column A contains property value >160k  SUMIF/SUMIFS The SUMIF function tells Excel to add only cells that meet criteria you choose. For example, let’s open your inventory spreadsheet. 1. Click in a blank cell. Type: =SUMIF(B4:B13,"Bumpers",E4:E13) 2. This will give us the quantity of all Bumpers suppliers. In other words, we are using the SUMIF function to tell Excel to add all the values in Column E where Column B contains “Bumpers.”  SUMIF/SUMIFS The SUMIFS allows you to use two or more “if” conditions to the SUM function.  SUMIF/SUMIFS The SUMIFS allows you to use two or more “if” conditions to the SUM function. In a blank cell, type: =SUMIFS(G4:G13,B4:B13,"Bumpers",C4:C13,"Continental") In this exercise, we are saying to give us the sum of how much are the bumpers at Continental. The G4:G13 is the SUM that will be added. The B4:B13 is the parts criteria we are setting telling Excel we want that parts. The C4:C13 has the categories or criteria we want results from.  SUMIF/SUMIFS Function SUMIF(range, criteria, [sum_range]) SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Common Functions & Formulas  Arrays! Arrays are methods on completing a function on a whole set of values. In other words, arrays allow you to run a function on multiple cells at the same time. Most arrays will have the { and } in the formula.  Simple Array 1. Click in cell H15 in the worksheet. 2. Type =SUM( *Note you do not type { 3. Click on cell E4 and drag your mouse down to E13. Your formula should look like =SUM(E4:E13 4. Type * 5. Click on cell G4 and drag your mouse down to G13. Your formula should look like =SUM(E4:E13*G4:G13 6. Close the formula with the ) 7. Press Ctrl+Shift+Enter to run the array. *Note, you do not press the Enter Key to run the array. Your formula should now look like: {=SUM(E4:E13*G4:G13)}  VLOOKUP VLOOKUP is vertical lookup. VLOOKUP is a useful function when you need to perform calculations that reference a table with a range of values. VLOOKUP searches down the first column of a table to find a value you want. Then it moves across a specified number of columns and returns the value in the target cell. This feature is frequently used when cross referencing incomes with income tax ranges or cross referencing sales revenues with commission ranges.  Note: when entering a VLOOKUP formula remember you will need: 1. A specified value in the leftmost column 2. A range or area that is named 3. Column offset from the leftmost column 4. A logical statement, or in other words a true or false value For this exercise, go to the spreadsheet and click in cell C11:  Select the cell of the first commission rate, select the Formulas ribbon and then Insert Function. In the search field, type VLOOKUP and select it from the search results. Click OK. In the Lookup_value field, select the cell with revenue amount.  In the Table_array field, highlight A3 through C7 in the table. You can also just type A3:C7in the Table_array field. *Note, don’t highlight the table headers. Important: The “Table_array” range must then be converted to absolute values by entering a$ before each column letter and each row number. However, lookup functions must use Absolute functions. In the Table_array field, change the data to: $A$3:$C$7  In the Col_index_num field, enter the relative column number of the omission data. This table has three columns and the Commission data is in the third column, so enter 3. Excel then cross references the salesperson’s revenue with the revenue/commission table and determines the appropriate commission rate. Select OK in the Function Arguments box.  HLOOKUP HLOOKUP is horizontal lookup. This function will work exactly the same as VLOOKUP except it will search across the first row in the cell range we select.  What we want to accomplish is getting the RATE in cell C2. The formula we will use is: =HLOOKUP(B2,F1:K3,3) We will use the absolute reference $  To get the Level of the Sales Marketing in cell D2:D5. The formula we will use is: =HLOOKUP(B2,F1:K3,2)  Concatenate The Excel CONCATENATE function is used to join up to 30 text items and return the result as a text. The Excel Concatenate formula is as follows: =CONCATENATE (text1, text2, [text3],...)  Date Formulas & Functions Planning Ahead The most commonly used date function is the =TODAY() function. In a blank cell in the Date spreadsheet, type: =TODAY() You should get today’s date after pressing the Enter key. However, what if you wanted to enter the date seven days from today? The formula you will use is: =TODAY()+7 Note* The TODAY function is not static. Excel will update the function with the current date that you open the spreadsheet. A static function is the =NOW() function. Go ahead and try =NOW()  Error Handling and Debugging Formulas Imagine you have created a super long formula in Excel. While you are proud of yourself, there is a minor glitch. It is returning an error or an unexpected value. Now you have two options: Either recreate it again or go through it manually one step at a time, OR Use the 2nd formula debugging techniques.  Debugging Here’s how this works: Select the cell that has the formula Click on Formula Tab Click on Evaluate Formula (Keyboard Shortcut – Alt + TUF). This will open the Evaluate Formula Dialogue Box Press Evaluate One by One to sift through the steps and their results