PROF DUKER - BASIC 8 for - MS EXCEL.pptx

for students

1. PROF DUKER SERVICES MOTTO: SERVICE BEYOND YOUR EXPECTATION LOCATION: SEKONDI - TAKORADI TEL: [phone] E-MAIL: profdukerserivces @gmail.com PROF DUKER SERVICES MOTTO: SERVICE BEYOND YOUR EXPECTATION LOCATION: SEKONDI - TAKORADI TEL: [phone] E-MAIL: profdukerserivces @gmail.com PROF DUKER SERVICES [phone]

2. I AM BORN TO BE A BLESSING TO HUMANITY. IKE-GODSENT DUKER If you find this file helpful for your work, kindly donate to the MTN no. 0242830522 (ISAAC DUKER) to support the team. May Allah/God bless you PROF DUKER SERVICES [phone]

3. SUB-STRAND 2.4 – INTRODUCTION TO ELECTRONIC SPREADSHEET CONTENT STANDARDS B8.2.2.4.1 – Demonstrate How To Use The Spreadsheet ( Using Functions And Complex Formulas) INDICATORS B8.2.4.1.1 – Perform Operations Using Functions And Built-in Functions B8.2.4.1.2 – Demonstrate How To Create Complex Formulas B8.2.4.1.3 – Demonstrate How To Copy Formulas And References CONTENT LINK At this level, we will be learning how to use common built-n functions such as SUM, AVERAGE, COUNT, CONTA,COUNTIF,MAX and MIN to perform calculations and logical operations KEYWORDS FORMULA FUNCTION AUTOFILL CELL CELL REFERNCE MAX MIN COUNTIF SUM AVERAGE RELATIVE REFERENCES

4. FORMULAS IN SPREADSHEET APPLICATION FORMULA A formula is mathematical calculation in a spreadsheet application. NB: formulas contain information to perform a numerical calculation. In spreadsheet, formula can perform mathematical operations such as addition, multiplication , division, comparisons, subtractions etc. A formula can be typed into the formula bar or into a particular cell. Formula always starts with an equal sign (=) or an additional sign and followed Values or cell references joined by an operator. Eg : = 5 + 3 or = A1 + A2 PROF DUKER SERVICES [phone]

5. METHODS OR WAYS OF STARTING A FORMULA There are two valid methods or ways of staring a formula in spreadsheet. They are: Equal to sign (=) Addition sign (+) Some valid or correct examples of formula are: =A1+A2+A3+A4 +A1+A6-A3 =+A4*A3 =A4/B12 =B7 – C7 +B7- C7 PROF DUKER SERVICES [phone]

6. TO TYPE A FORMULA IN SPREADSHEET, USE THE FOLLOWING STEPS Select the cell into which the formula will be typed. Type the equal to or plus sign in the selected cell or into the formula bar after double clicking in it. Type the cell reference or cell address, or cell name into the selected cell or in the formula bar or select the cell reference or cell address to be used and type your preferred mathematical operation followed by the other cell address or reference to be used. Click the correct sign close to the name box or behind the formula bar, or press the enter key. PROF DUKER SERVICES [phone]

7. CREATE A FORMULA Here is how to create a formula in Excel Click the cell where you want to add the formula Press = Specify what to calculate Enter the formula Click the Enter button or Press ENTER PROF DUKER SERVICES [phone]

8. EDIT A FORMULA It is easy to make changes to a formula To do this apply the steps below Step 1 – Click the formula you want to edit Step 2 – Click in the formula bar Step 3 – Edit the formula Step 4 – Press Enter PROF DUKER SERVICES [phone]

9. FUNCTIONS IN EXCEL A function is a pre-recorded formula used for calculations in spreadsheet application. Functions are predefined or inbuilt formulas that is already available in excel Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structures. NB: Excel has over 450 functions you can use to perform just about any kind of calculation. If you are having trouble finding the right functions, the insert function command lets you search for the function you want. Functions always begin with equal sign (=) followed by the function name and then the argument. Any formula or function that contains wrong or invalid character or format is indicate by the hash sign(#) followed by a name.

10. ARGUMENTS IN EXCEL Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. NB: You can include one argument or multiple arguments depending on the syntax required for the function. =SUM(A1:A2) Equal Sign Function Name Argument Syntax order: All functions begin with the equal sign (=) The functions name comes after the equal sign without spacing. Argument, such as range of cells, text and numbers are enclosed by parenthesis If there is more than one argument it is separated by comma.

11. SOME COMMON FUNCTIONS IN EXCEL Function Description SUM Adds numbers together in a range AVERAGE Calculates the average (arithmetic means) MAX Returns the highest value in a range MIN Returns the lowest value in a range COUNT Counts cells with only numbers in a range COUNTA Counts all cells in a range with both numbers and letters COUNTIF Counts cells as specified COUNTBLANK Counts blank cells in a range CONCATENATE Joins together two or more text strings LOWER Converts all characters in a text string to lowercase UPPER Converts all characters in a text string to uppercase

12. DIFFERENCES BETWEEN FUNCTIONS AND FORMULAS IN EXCEL FUNCTION FORMULA It is a pre-defined (built-in) formula used to perform calculations It is a user-defined expression used to perform calculations Functions are used to perform complex calculations that cannot be done manually by the user Formulas are used to perform simple or complex calculations that can be done manually by the user A function can be a formula Formula can’t be a function =SUM(A1:A3) =A1+A2+A3 PROF DUKER SERVICES [phone]

13. ERRORS IN DATA ENTRY ERROR VALUE MEANS COMMON CAUSES #DIV/0 Division by zero You attempted to divide by a value of zero. A blank cell is treated as zero in mathematical operations #N/A No value available Manually entered (and sometimes when data is imported) to indicate information not available #NAME? Excel does not recognize the name of a list or range of cells The #NAME? error will result if you neglect to enclose text in quotes within a formula, or if you refer incorrectly to the name (address) of a cell or range of cells. #NULL! Reference to a non-existent intersection between two cell ranges If you neglect to separate cell ranges with a comma in certain function arguments, the #NULL! Error will result. Also, if you refer to an intersection between two cell ranges which do not intersect. #NUM! There is a problem with a number in formula or result Passing an valid argument to a function or formula, or a formula returns a number which is too large or too small to be represented in the cell #REF! Invalid cell reference You have deleted or pasted over a cell or cells referred to in a formula #VALUE! Invalid argument Usually results from performing a mathematical operation with cells that contain text ##### The column is not wide enough to display the content This error occurs when a column is not wide enough or a negative date or time is used.

14. USING BUILT-IN FUNCTIONS TO PERFORM SIMPLE OPERATION USING THE SUM FUNCTION SUM() – this is a function used to calculate the total of the values within a cell or range of cells. To use this function, type “=” sign followed by SUM and range of cells values within brackets. Syntax: =SUM(cell range) Let assume you want to add up your monthly expenditure. You can calculate the total amount using the SUM() function.

15. USING THE AVERAGE FUNCTION This function is used to calculate the average value of a selected cell range. To use this function, type “=” sign followed by AVERAGE and range of cells values within brackets. Syntax: =AVERAGE(cell range) Suppose you want to calculate the average marks of all your subjects in school. Type =AVERAGE(cell range) and press enter key. The output will be displayed in the same cell. RESULT

16. USING THE COUNT FUNCTION Excel’s COUNT functions can help you count cells with numbers, text or blanks cells. Excel has a small family of COUNT functions, but the most useful are: COUNT COUNTA COUNTBLANK COUNTIF USING THE COUNT () FUNCTION This function is used to count the total number of values in a given range of cells. To use this function, type “=” sign followed by COUNT and the cell range within brackets. Syntax: =COUNT(cell range) Suppose you have to count the total number of items you have purchased for your new class. Type =COUNT(cell range) and press enter key. The output will be displayed in the same cell.

17. USING THE COUNTA() FUNCTION This is a function used to count the number of non-blank cells in cell range. To use this function, type “=” sign followed by COUNTA and the cell range within brackets. Syntax: =COUNTA(cell range) Suppose you have created a list of guests you have invited to your party. Some have confirmed they will come while some would be unable to come. Count the number of guests who would be unable to come. Type =COUNTA(cell range) and press enter key. The output will be displayed in the same cell.

18. USING THE COUNTIF() FUNCTION This is a function used to count the number of cells that meet a certain condition. To use this function, type “=” sign followed by COUNTIF, the cell range, comma, and the condition within brackets Syntax: =COUNTIF(cell range, condition) To create a COUNTIF formula Click on the cell where you want to add formula Type the function. Eg =COUNTIF(A2:A14, “Accra”) this will count the frequency or number of times Accra appears in the range A2:A14 Press the Enter key.

19. VARIATIONS IN THE COUNTIF FUNCTION FORMULA DESCRIPTION =COUNTIF(A2:A5, “apples”) Counts the number of cells with apples in cells A2 through A5. The result is 2 =COUNTIF(A2:A5,A4) Counts the number of cells with peaches(the value in A4) in cells A2 through A5. The result is 1 =COUNTIF(B2:B5, “>45”) Counts the number of cells with a value greater than 45 in cells B2 through B5. The result is 2 =COUNTIF(B2:B5, “<>” &B4) Counts the number of cells with a value not to 57 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5, “<>” &45) The result is 3 DATA DATA Apples 23 Oranges 45 Peaches 57 Apples 68

20. FORMULA DESCRIPTION =COUNTIF(B2:B5,”>=23”)-COUNTIF(B2:B5,“<=58”) Counts the number of cells with a value greater than (>) or equal to (=) 23 and less than (<) or equal to (=) 58 in cells B2 through B5. The result is 1 =COUNTIF(A2:A5,“*”) Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4 =COUNTIF(A2:A5,”?????es”) Counts the number of cells that have exactly 7 characters, and end with the letters “es” in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2 VARIATIONS IN THE COUNTIF FUNCTION PROF DUKER SERVICES [phone]

21. USING THE MAX () FUNCTION MAX() is a function used to determine the maximum value within a specified range of cells. To use this function, type “=” sign, followed by MAX and cell range within brackets. Syntax: =MAX(cell range) Suppose you have created a list of learners with their scores in an end of term examination, and your teacher wants you to find out the maximum score on the list. You can easily use the MAX() functions

22. USING THE MIN() FUNCTION MIN() is a function used to determine the minimum value within a specified range of cells. To use this function, type “=” sign, followed by MIN and cell range within brackets. Syntax: =MIN(cell range) Suppose you have created a list of learners with their scores in an end of term examination, and your teacher wants you to find out the minimum score on the list. You can easily use the MIN() functions