1 / 42

TERM 2: FORMULA & FUNCTIONS

TERM 2: FORMULA & FUNCTIONS. OBJECTIVES: At the end of these sessions you should be able to: Formulate basic and more complex formulae Use some pre-defined functions in Excel Explain the difference between relative and absolute cell referencing. IMPORTANT!!!!.

vera
Download Presentation

TERM 2: FORMULA & FUNCTIONS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TERM 2: FORMULA & FUNCTIONS OBJECTIVES: At the end of these sessions you should be able to: • Formulate basic and more complex formulae • Use some pre-defined functions in Excel • Explain the difference between relative and absolute cell referencing

  2. IMPORTANT!!!! • PLEASE NOTE: THE REFERENCES AND HOME-WORK ARE GIVEN FROM THE PURPLE TEXT BOOK. IF YOU ARE NOT SURE OF THE PAGES IN THE BLUE BOOK ASK YOUR TEACHER FOR THE EQUIVALENT REFERENCE!

  3. Formulae in MS Excel • Electronic Spreadsheets allow you to perform calculations on data entered into the spreadsheet. • You can use an Excel 2007 formula for basic calculations, such as addition or subtraction, as well as more complex calculations. • In addition, if you change the data Excel will automatically recalculatethe answer without you having to re-enter the formula.

  4. Use of Mathematical Operators The mathematical operators used in Excel formulae are: • Subtraction - minus sign ( - ) • Addition - plus sign ( + ) • Division - forward slash ( / ) • Multiplication – asterisk ( * ) • Exponentiation - caret ( ^ ) To create a formula in Excel you type an equal sign and combine the cell references of your data with the correct mathematical operator. (Read Pages 273-274 : Log On to IT for CSEC (2nd Ed.)

  5. Order of Operators If more than one operator is used in a formula, there is a specific order that Excel will follow to perform these mathematical operations. This order of operations can be changed by adding brackets/parentheses to the equation. An easy way to remember the order of operations is to use the acronym BEDMAS (no not BODMAS) • The Order of Operations is: BracketsExponentsDivisionMultiplicationAdditionSubtraction

  6. Order of Operations • Any operation(s) contained in brackets will be carried out first followed by any exponents. • After that, Excel considers division or multiplication operations to be of equal importance, and carries out these operations in the order they occur left to right in the equation. • The same goes for the next two operations – addition and subtraction. They are considered equal in the order of operations. Which ever one appears first in an equation, either addition or subtraction, is the operation carried out first.

  7. The example shows how to add two numbers. The formula will add the numbers 3 + 2. STEPS:1. Type a 3 in cell E1 2. Type a 2 in cell E2 3. Type the = sign in cell E3 Simple Formula 4. Click on cell E1 with the mouse pointer to enter the cell reference into the formula.5. Type a plus ( + ) sign. 6. Click on cell E2 with the mouse pointer to enter the cell reference into the formula.7. Press the ENTER key on the keyboard. 8. The answer 5 should appear in cell E3. The formula in the formula bar will be =E1+E2

  8. ACTIVITY 1 Perform the following calculations on the data in your spreadsheet: • Addition • Subtraction • Multiplication • Division • Exponentiation (see Fig. 1) • Your spreadsheet should look like Fig. 2. Fig. 1 Fig. 2

  9. AFL • What is Automatic recalculation (w.r.t. using Excel)? • What is the order of precedence of the mathematical operators used in Excel? • Write the formula to add 2 numbers stored in cells A1 and A2 respectively.

  10. Using a more complex formula ACTIVITY 2: The activity is a Marksheet that calculates the Total Marks the students attained and their Average Mark: STEPS: • Enter the data as seen in the spreadsheet (SEE next slide). • Enter the formula in cell F4 (=C4+D4+E4) • Copy and paste the formula to cells F5:F8 • Enter the formula G4 (=F4/300)*100 • Copy and paste the formula to cells G5:G8

  11. ACTIVITY 2:

  12. HOME-WORK • Do Activity 4 and Exercise 3- Page 274- 275 (Log On to IT for CSEC- 2nd Ed.)

  13. FUNCTIONS Excel uses Functions (which are mathematical expressions already available in Excel) and formulae ( mathematical expressions that you create) to dynamically create results from data in your worksheet. Each of Excel’s Functions is a predefined formula acting on a range of cells that you select. The range of cells in the function that is selected is referred to as an argument.

  14. FUNCTIONS • The following functions will be looked at in our class: • SUM • COUNT • MAX • MIN • AVERAGE • (Read Page 276-278 (Ch 13): Log On to IT for CSEC (2nd Edition)

  15. We said earlier if you wanted to add a series of numbers you would enter an equal sign followed by the cells you want Excel to add up. Example: = B4 + B5 + B6 + B7 • But this is not a good way to add up in Excel. It could get very tedious if you had to type out say 100 cell references by hand. The easy way is to get Excel to do the work for you. That's where SUM comes in!!

  16. Example using SUM • Consider the following example in the next slide. We want to find the total sales for Monday, Tuesday, Wednesday, etc. • First you type an equal sign, the word SUM then open brackets) in cell B8. • Then you highlight the range of cells with the data you want to be added. In this case, B4:B7. • Then you enter the closed brackets and press enter

  17. The total sales for Monday is 66!

  18. You can copy and paste the formula to the other cells to get the totals for the other days instead of retyping the formula.

  19. Now do the Activity for yourself. • After you have completed the activity, click on cells B8, C8, D8, E8 and F8 and write the formula in your books. What is your observation?

  20. If you noticed that the cell ranges changed with respect to the new location of the formula then YOU ARE RIGHT!! B8’s formula is =SUM (B4:B7) C8’s formula is =SUM (C4:C7) D8’s formula is =SUM (D4:D7) E8’s formula is =SUM (E4:E7) and so on. This is known as relative cell referencing!!!!

  21. AVERAGE FUNCTION First, what is in an average? In math, an average is a number derived by dividing how many there are in a list by the list total. For example, suppose a list of student scores in an exam was this: 9, 7, 6, 7, 8, 4, 3, 9. We only have eight scores. To get the average score we first need to get the total. So add up the numbers in the list: 9 + 7 + 6 + 7 + 8 + 4 + 3 + 9 = 53. Next divide by how many there are in the list: 8. So to get the average score the sum is 53 divided by 8. The answer is 6.625. Which means that the average score in the exam was 6.625.

  22. In Excel there is a predefined function to determine the average, known as AVERAGE. Using the previous example, let’s find the average amount of chocolates that are sold each day. Again, we start with an equal sign, followed by the function, then the range in brackets. Press enter. The result should be:16.5 (format your number to 0 decimal places to get 17). Copy and paste the formula to the other cells.

  23. Now try the activity on your own!

  24. MAX AND MIN FUNCTIONS The MAX function allows you to find the largest value amongst a list of values. The MIN function allows you to find the smallest value amongst a list of values. Using the same activity as before, let’s find the largest amount of chocolates sold each day and the least amount of chocolates sold each day!

  25. Just as before, you enter equal sign, the function and the range in brackets! • Copy and paste it to the other cells

  26. Now you try it!

  27. MIN function! • Just as before, you enter equal sign, the function and the range in brackets! • Copy and paste it to the other cells

  28. Now you try it!

  29. COUNTA FUNCTION The COUNTA function allows you to count/determine the number of items on a list. The This is easier than having to count manually, especially with large lists. What it does is count up the number of cells containing data – either numbers or labels in a selected range. Let’s see how it works. Say, we wanted to determine the Total amount of the brands of chocolates that are sold.

  30. Just as before, you enter equal sign, the function and the range in brackets!

  31. The result is 4! In this instance we do not need to copy and paste because we counted the number of brands of chocolate sold! Now try the activity on your own!

  32. Please Note, The COUNTA function is different from the SUM function- COUNTA counts the number of cells containing data (labels or values), SUM finds the total of the values (only) in a range of cells!!!!

  33. HOME-WORK • DO ACTIVITY 5, CHAPTER 13, PAGE 279:LOG ON TO IT FOR CESEC (2ND EDITION)

  34. HOME-WORK • DO EXERCISE 4, PAGE 280:LOG ON TO IT FOR CESEC (2ND EDITION)

  35. Absolute Cell Referencing vs Relative Cell Referencing Relative cell references are basic cell references that adjust and change when copied. Situations arise in which the cell reference must remain the same when copied or when using AutoFill.  Dollar signs are used to hold a column and/or row reference constant. This is Absolute cell referencing.

  36. ACTIVITY • Read Page 281: Log On to IT for CSEC (2nd Edition). Do Activity 6 (Using Absolute Cell Reference)

  37. HOME-WORK • CHECK OUT THIS SITE: http://www.gcflearnfree.org/excel2007/8

  38. REFERENCES • http://spreadsheets.about.com/od/excelformulas/ss/07excel_formula_2.htm • http://ase.tufts.edu/its/trainDocuments/excel07FunctionsFormulas.pdf • http://www.homeandlearn.co.uk/me/mes6p1.html

More Related