Advanced Excel Module 1 PDF

Advanced Excel Module 1: 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.  What is a spr...

Advanced Excel Module 1: 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.  What is a spreadsheet? A program that displays data (text & numbers) in a table called a worksheet  Uses of Spreadsheet Prepare budgets Financial statements Inventory management Create charts  What is a worksheet? A grid with columns & rows; the term worksheet is used interchangeably with spreadsheet  What is a cell? The intersection of a row and column What 3 things can you type into a cell? Label – words or letters Value – numbers Formula – statement that performs a calculation  What is a cell range? A group of cells that are closely together  What are columns? Vertical arrangements of cells; identified by letters  What are rows? Horizontal arrangement of cells; identified by numbers  What is a worksheet tab? A tab that identifies each open worksheet in a spreadsheet program, located in the lower left corner of the screen FORMULAS : FUNCTIONS  FORMULAS : FUNCTIONS Being able to work with Excel Formulas can take your experience with the program to a new level. Formulas are the basic foundation of Excel. For the beginning of the class, we are going to review the basics of formulas. Formulas are the ways you can calculate cells, numbers, etc. in your workbooks. OPERATIONS PRECEDENCE OF OPERATIONS CELL REFERENCE  FUNCTIONS Functions are the built‐in algorithms that are incorporated into formulas (usually in a form of prefixes) to perform a variety of calculations. FOUNDATION FUNCTIONS & FORMULAS  BASIC FUNCTION ARGUMENT A formula prefix is essentially a function argument. Being able to use the correct prefix is the biggest challenge for many Excel users.  Exercise For this exercise, let’s use the PRODUCT prefix. This argument is simple multiplication. 1.Enter 9 in cell B3, 15 in cell B5, and 25 in cell B7. 2.Click any empty cell. 3.Click Formulas --‐> Insert Function. 4.In the Insert Function dialogue box, select Math & Trig. 5.In the Select a function menu, select Product. 6.Click on the first RefEdit control button. This is the spreadsheet looking button at the end of the box. 7.Click on cell B3. You will see B3 enter into the Number 1 box. 8.Repeat this for Numbers 2 & 3. 9.Once done, click Ok. You should get the results in the selected cell. *The prefix PRODUCT is a function that multiplies data from multiple cells throughout a spreadsheet or even workbooks.  NESTING FUNCTIONS Nesting functions allows you to insert at least two functions within one formula. A huge majority of nesting functions are used for conditional and IF formulas. 1. Place a set of random numbers in cells C1:C5 and D1:D5. 2. For this exercise, we are going to find the average of the numbers in cell C1:C5 and D1:D5. 3. In a blank cell, type: =AVERAGE(MAX(C1:C5),MAX(D1:D5)) 4. Press the Enter key to get the answer. 5. The MAX function is nested within the AVERAGE formula in this example.  IF Formulas IF formulas are set up to provide a true or false statement after a calculation is performed. With IF statements, you can add multiple arguments to produce different results.  IF/AND Formulas Now let’s say you want to add an additional argument to an IF statement. Exercise 1: 1. In any blank cell, type: =IF(AND(D1