Download to read offline
Presentation on key function in micro Excel
- 2. Presentation outline Formula How to enter formula How to edit and copy formula How to insert a function Mathematics statistical and Functions How to insert a Function Functions(sum,average,count) Function(round ,mod, ABS, min and max) Practical explanation Questions and answers
- 3. A formulais an expression which calculates the value of one or more cell(s). The functions are also predefined formulas and it can be used independently in Excel and performs calculations using specific values in a particular order. A simple formula can be created using add, subtract, multiply or divide values in your worksheet. Formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs. An example is shown below;
- 4. Cell B4contains the value obtain from the addition of all other values in column B(B1,B2,B3,B4) figure 1
- 5. The examplesin figure two cell B4 contains the SUM function of (B1:B3)
- 6. How to entera formula To enter a formula ,execute the following steps Select a cell Types equal sign Type in the formula required in the cell where your answer is required For instance type =B1+B2+B3 in cell B4 Then click enter
- 7. How to edita formula When you select a cell which contains a Formula , excel Shows the formula of the cell in the formula bar.
- 9. To edita formula click in the formula bar and Change the formula
- 10. The clickenter ,And there change in the output
- 11. How to copy/pastea formula When you copy a formula, Excel automatically changes the cell references for each new cell the formula is copied to. Here are steps belo
- 12. Select cellB4, right-click, and then click Copy (or press CTRL + c) as shown below. Next, select cell the C4 and right-click, and then click Paste under ‘Paste Options:’ (or press CTRL + v).
- 13. You canalso drag the formula horizontally or vertically according to your data in the sheet. Here in the picture below shows that select cell B4, click on the lower right corner of cell B4 and drag it across to cell C4.
- 14. Result. Theformula in cell C4 references the values in column C
- 15. How to inserta function Excel consist of numerous function key A function must have a syntax which indicates how to write a specific function. The basic syntax for a function is an equals sign (=), then the function name (SUM, for example), and one or more arguments within parentheses. Arguments contain the information you want to calculate. Suppose, SUM(B1:B4). This function adds the values in cells B1,B2, B3, and B4. Here, the picture in figure 13 below shows the parts of the function.
- 16. Arguments : Arguments may be a single cell or a range of cells and must be enclosed in parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function. For example, the function =SUM(B1:B3) would calculate the sum of the values in the cell range B1:B3. This function contains only one argument. Here is the picture below.
- 17. Multiple argumentscan be used but it must be separated by a comma. For example, the function =SUM(B1:B3, D1:D2,F1) will add the values of all the cells in the three arguments. Here is the picture below
- 18. The stepsbelow describes how to inse
- 19. Search fora function or select a function from a category. For example, choose AVERAGE from the Statistical category. Then click ok
- 20. The ‘FunctionArguments’ dialog box appears. Click Ok. Output : Average of B2 and C2 is 5.
- 22. Mathematical and Statistical Functions This are used to perform various arithmetic operations like sum, average, count, round, abs, etc. SUM FUNCTION: This function is used to adds all the values within a cell range or group of selected cell in a worksheet. Here in the example below, we will create a basic function to calculate the sum of working hours generates in a day.
- 23. Select thecell where you want to put the formula, type the equals sign (=) and write the desired function name or choose the function from the suggested function list. Here in the example below we write the SUM function.
- 24. Now writethe range of sum or you can select the range by using the mouse to drag
- 25. AVERAGE FUNCTION: This function is used to calculate the arithmetic mean for a group of numbers Here in the example below, we will create a basic function to calculate the average working hours of each employee.
- 26. Press Enterkey and see the result.
- 27. COUNT FUNCTION: The COUNT Function is used to determine how many cells in a range contain a numeric entry. The COUNT function will not work for counting text or other non-numeric entries Here in the example below, we will create a basic function to calculate the number of employees.
- 28. ROUND FUNCTION: The round function is used to round a number to a specified number of digits.
- 29. MOD FUNCTION: This function is used to find the remainder after dividing a number by another number.
- 30. ABS FUNCTION: The abs function is used to return the absolute value of a given number. The number may be positive or negative. The absolute value is always a positive value and not a negative value Here is the example below.
- 31. These functionsidentify the highest and lowest values in a range of cells. The fol lowing steps explain how to add these functions to the Budget Detail worksheet using the Insert Function button: Click cell D15 in the Budget Detail worksheet. 2. Click the Insert Function button on the Formulas ribbon. (see Figure 30)
- 32. 9. Clickcell D16 10.Repeat steps 2-8 (using MAX instead of MIN) to add the MAX function to the worksheet. (see Figure 32)
- 33. This bringsup the Insert Function dialog box. Type the word MIN in the search box and then click the Go button. (see Figure 32) 4. Double-click MIN in the list. This opens the Function Arguments dialog box. 5. Click the Collapse Dialog button in the Function Arguments dialog box. 6. Highlight the range D3:D11. 7. Click the expand Dialog button in the Function Arguments dialog box 8.Click the Ok button on the Function Arguments dialog box. This adds the MIN function to the worksheet (see figure 31)