Slide 1



Slide 1 |[pic] |The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions. Otherwise, it is not much more than a large table for displaying text and numbers. This presentation will show you how to create these calculations

Last session we covered some basic skills like – Copying, formatting, Charts and Graphs, etc in Excel

This session we will be covering formulas and functions in detail.

| |

|Slide 2 |[pic] |This is the objective for Session 2 of Excel. This |

| | |presentation will show us how to use formulas and |

| | |functions to perform calculations in Excel. We will |

| | |also be covering the Auto fill feature in Excel. |

|Slide 3 |[pic] |Excel can do many mathematical functions for us and |

| | |makes the job of analyzing numbers easier. |

| | |Basic operators are Add +, subtract -, multiply *, and |

| | |divide / |

| | |Formulas are entered in the worksheet cell where the |

| | |result will appear and must begin with an equal sign |

| | |"=". The formula then includes the cell references |

| | |whose values will be manipulated with appropriate |

| | |operators placed in between. |

| | |After the formula is typed into the cell, the |

| | |calculation executes immediately and the formula itself|

| | |is visible in the formula bar and the result appears in|

| | |the cell. |

| | | |

|Slide 4 |[pic] |Functions are pre-set formulas that can be used for |

| | |calculations. Functions differ from regular formulas in|

| | |that you supply the value but not the operators, such |

| | |as +, -, *, or /. |

| | |Use them by typing in the function name, clicking the |

| | |insert function icon on the formula bar or inserting a |

| | |function from the Insert menu. We will cover this in |

| | |detail later. |

| | |When using functions begin with the equal sign, name |

| | |for formula, open parentheses, the cell references and |

| | |operators, close parentheses. Functions can be nested |

| | |within other functions by using parentheses. There are|

| | |some examples on the next slide. |

| | | |

|Slide 5 |[pic] |Notice that the first formula in each example has been |

| | |typed in using cell references and operators and the |

| | |second example used a function name with parentheses |

| | |and the cell names or ranges. |

|Slide 6 |[pic] |You can use the AVERAGE function to calculate an |

| | |average from a series of numbers. |

| | |You can use the MIN function to find the lowest number |

| | |in a series of numbers |

| | |You can use the MAX function to find the highest number|

| | |in a series of numbers. |

| | | |

| | | |

|Slide 7 |[pic] |Students may open Excel and practice these formulas. |

| | |The answers will pop up separately on a mouse click |

|Slide 8 |[pic] |This slide shows how the formula appears in the formula|

| | |bar and how the result appears in the cell. |

|Slide 9 |[pic] |Here is a fairly complicated formula that calculates |

| | |the sub total for four different textbooks. Notice that|

| | |one single formula multiplies the quantity and price of|

| | |each textbook and adds the subtotal for each book. |

| | |Example: The formula begins with the Biology textbook |

| | |in A2, 4 books in B2 sold at a price of $99.99 each are|

| | |represented by the formula (B2*C2). This total is |

| | |added to the totals for each of the other text books |

| | |and the final subtotal is shown in cell C7. Notice the |

| | |double parentheses surrounding the entire formula. |

| | |Each separate calculation within the formula is within |

| | |parentheses and the overall summing formula is also in |

| | |parentheses. |

| | | |

|Slide 10 |[pic] |The next two slides will show you how to insert the |

| | |function from the menu bar. The functions are grouped |

| | |into categories for easy use. The functions within |

| | |each category appear on the right under Function name. |

| | |When a function is selected a definition of it appears |

| | |at the bottom of the selection window. |

| | |Place your cursor in the cell in which you want the |

| | |answer to appear and click on fx icon on the tool bar. |

| | |Select the desired function category and function name |

| | |and click OK. |

|Slide 11 |[pic] |Excel will anticipate which data will be used in the |

| | |formula and will put the cell or range in the formula. |

| | |If this is correct, select OK. Otherwise you will have|

| | |to select the cells for the formula. If the function |

| | |box is hiding the data to be selected, the box can be |

| | |moved by clicking and dragging it away from the data. |

| | |When the cell or data range is selected on the |

| | |spreadsheet it will be placed into the function box. |

| | |Some functions will require selections in both |

| | |available boxes.) Click OK and the answer will appear |

| | |in the cell that holds the function. |

| | |CAUTION: don’t click in any cells you don’t want to be|

| | |in the formula before clicking OK – it is easy to get |

| | |more cells in the formula than you want to have there. |

|Slide 12 |[pic] |Excel will automatically fill in cells with dates, |

| | |numbers, text, or formulas. Begin the series and then |

| | |highlight a significant portion of the data and move |

| | |the cursor to the lower right corner of the selection. |

| | |The cursor changes to a cross and will now perform the |

| | |auto fill function. Click the mouse button and drag |

| | |the selection to auto fill the cells that are being |

| | |selected. In this example clicking and dragging the |

| | |mouse to the right will put the number 3 in cell C2, |

| | |the number 4 in cell D2, etc. If you click and drag |

| | |down the cells will just be copied (A3 will have a 1, |

| | |and B3 will have a 2). |

| | |This feature is very useful with dates and numbers once|

| | |the beginning word or number has been entered. Auto |

| | |fill works up and down and left and right. Play with |

| | |it to find out how it works best – try different series|

| | |of numbers 1-3-7 and months and days of the week. |

| | |The next slide shows examples |

|Slide 13 |[pic] |Remember, the cursor changes to a cross when the cursor|

| | |rests on the lower right corner of the selected cells. |

| | |Numbers will be filled consecutively 1, 2, 3, etc., or |

| | |in a series. The series must be typed in first and |

| | |then the program will complete the series that was |

| | |chosen. |

| | |The word January begins a series for months when the |

| | |cursor is dragged to the right. This works with days of|

| | |the week as well. |

|Slide 14 |[pic] |Formulas can be auto filled also. This is really |

| | |useful if the same formula works for many columns or |

| | |rows of numbers. Put the formula in the first cell for|

| | |a row or column and when it is correct, auto fill it |

| | |across the remaining columns or down the remaining |

| | |rows. It is also useful for complicated formulas that |

| | |need to be repeated in other places. |

| | |Relative references When you create a formula, |

| | |references to cells or ranges are usually based on |

| | |their position relative to the cell that contains the |

| | |formula. In this example, cell B6 contains the formula |

| | |=A5; Microsoft Excel finds the value one cell above and|

| | |one cell to the left of B6. This is known as a relative|

| | |reference. |

| | | |

| | |When you copy a formula that uses relative references, |

| | |Excel automatically adjusts the references in the |

| | |copied formula to refer to different cells relative to |

| | |the position of the formula. In this example, if the |

| | |formula in B6 is auto filled (or copied) to cell B7 |

| | |Excel would adjust the formula in cell B7 to =A6, which|

| | |refers to the cell that is one cell above and to the |

| | |left of cell B7. In other words the relationship |

| | |remained the same. |

| | |Absolute references If you don't want Excel to adjust|

| | |references when you copy a formula to a different cell,|

| | |use an absolute reference. For example, if your formula|

| | |multiplies cell A5 with cell C1 (=A5*C1) and you copy |

| | |the formula to another cell, Excel will adjust both |

| | |references. You can create an absolute reference to |

| | |cell C1 by placing a dollar sign ($) before the parts |

| | |of the reference that do not change. To create an |

| | |absolute reference to cell C1, for example, add dollar |

| | |signs to the formula as shown on the slide =A5*$C$1. |

| | |Notice on the example that the formula in B7 has been |

| | |auto filled from A7 and it shows the absolute cell |

| | |reference $C$1 hasn’t changed. |

|Slide 15 |[pic] |Cell references are used to determine which numbers to |

| | |use in the formulas. These references can be either |

| | |relative or absolute. |

| | |Relative references refer to the location of a cell in |

| | |relationship to other cells. This can change as rows or|

| | |columns are added or deleted from a worksheet, but the |

| | |formula will still work. |

| | |Absolute cell references refer to a specific cell and |

| | |they will never change even if rows or columns are |

| | |added or subtracted in the spreadsheet. We will see how|

| | |relative and absolute references are significant while |

| | |copying formulas later during the presentation. |

|Slide 16 |[pic] |Using Auto fill to copy a formula in an Excel sheet |

| | |provides a quick and easy way to be sure that the |

| | |formula is correct for all rows or columns. In this |

| | |example the formula is auto filled (copied) from Cell E|

| | |7 down the column through E10 and the formulas have |

| | |been adjusted relatively. |

|Slide 17 |[pic] |In this example for Absolute cell referencing and auto |

| | |fill we are working with student grades. Each student |

| | |is receiving 20 points for participation as shown in |

| | |cell B1. Because each student is receiving the points |

| | |and the points are not changing we can use an absolute |

| | |cell reference in the formula that will compute the |

| | |total points. The formula in the sub total column adds|

| | |the points for each subject. Then, the formula for |

| | |total points adds the participation points to the |

| | |subtotal. This formula has been auto filled down the |

| | |column for every student. Because the $ sign was used |

| | |to show an absolute cell reference that part of the |

| | |formula remained the same for each student. |

|Slide 18 |[pic] |This spreadsheet contains statistics for some players |

| | |on the Richfield Barons baseball team over the first 20|

| | |games of their season. The data has been entered for |

| | |Games 1-10 and Games 11-20 and need to be totaled for |

| | |the entire season to date. You will be using different |

| | |formula and functions in this exercise to calculate the|

| | |season statistics for the team. |

|Slide 19 |[pic] |You should upload the Baseball Project to your www |

| | |folder and hyperlink it to your website. Also, send |

| | |your work to your instructor through the digital drop |

| | |box. |

| | | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download