Page 1 :
MS-EXCEL, LECTURE, NOTES, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 1

Page 2 :
PREAMBLE, Microsoft Excel is an electronic spreadsheet. You can use it to organize your data, into rows and columns. You can also use it to perform mathematical calculations, quickly. This course teaches Microsoft Excel basics as a prelude to the use of, Statistical Analysis System (SAS) software in carrying out more complex statistical, analysis. Although knowledge of how to navigate in a Windows environment is, helpful, this manual is created for the computer novice., At the end of the course, participants are expected to know how to use Microsoft, Excel to:, , , , , , , , , , Enter text and numbers in a spreadsheet, Enter Excel formulas, Format data, Create Excel functions, Fill cells automatically, Print results, Create Charts, and, Enter advanced Excel formulas, , Accordingly, the course is divided into the following five (5) sections., Section 1: Entering Text and Numbers, Section 2: Entering Excel Formulas and Formatting Data, Section 3: Creating Excel Functions, Filling Cells, and Printing, Section 4: Creating Charts, Section 5: More on Entering Excel Formulas, , Section 1: Entering Text and Numbers, 1.1, , The Microsoft Excel Window, , This Section will introduce you to the Excel window. To begin this Section, start, Microsoft Excel 2007 as follows:, 1., 2., 3., 4., , Click on Microsoft Start Button, Point the mouse on All Programs, Click on Microsoft Office, Click on Microsoft Excel 2007, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 2

Page 3 :
The Microsoft Excel window appears and your screen looks similar to the one shown, here., , 1.2, , The Microsoft Office Button, , In the upper-left corner of the Excel 2007 window is the Microsoft Office button., When you click the button, a menu appears. You can use the menu to create a new, file, open an existing file, save a file, print and perform many other tasks., 1.3, , The Quick Access Toolbar, , Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access, toolbar gives you quick access to commands you frequently use., 1.4, , The Title Bar, , Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel, displays the name of the workbook you are currently using. At the top of the Excel, window, you should see "Book 1 - Microsoft Excel" or a similar name., 1.5, , The Ribbon, , In Microsoft Excel 2007, you use the Ribbon to issue commands. The Ribbon is, located near the top of the Excel window, below the Quick Access toolbar., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 3

Page 4 :
1.6, , Worksheets, , Microsoft Excel consists of worksheets. Each worksheet contains columns and rows., The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the, rows are numbered 1 to 1,048,576., The combination of a column coordinate and a row coordinate make up a cell, address. For example, the cell located in the upper-left corner of the worksheet is, cell A1, meaning column A, row 1. Cell E10 is located under column E on row 10., You enter your data into the cells on the worksheet., 1.7, , The Formula Bar, , If the Formula bar is turned on, the cell address of the cell you are in displays in the, Name box which is located on the left side of the Formula bar. Cell entries display, on the right side of the Formula bar., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 4

Page 5 :
1.8, , The Status Bar, , The Status bar appears at the very bottom of the Excel window and provides such, information as the sum, average, minimum, and maximum value of selected, numbers., 1.9, , Move Around a Worksheet, , By using the arrow keys, you can move around your worksheet. You can use the, down arrow key to move downward one cell at a time. You can use the up arrow, key to move upward one cell at a time. You can use the Tab key to move across the, page to the right, one cell at a time. You can hold down the Shift key and then press, the Tab key to move to the left, one cell at a time. You can use the right and left, arrow keys to move right or left one cell at a time. The Page Up and Page Down keys, move up and down one page at a time. If you hold down the Ctrl key and then press, the Home key, you move to the beginning of the worksheet., 1.10 EXERCISE 1, Move around the Worksheet using the Down and Up Arrow Keys, the Right and Left, Arrow Keys, the Tab Key, the Page Up and Page Down Keys and the (Ctrl) Home Key., 1.11 Go To Cells Quickly, The following are shortcuts for moving quickly from one cell in a worksheet to a cell, in a different part of the worksheet., 1.12 EXERCISE 2, Go to -- F5, 1. Press F5. The Go To dialog box opens., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 5

Page 6 :
2. Type J3 in the Reference field., 3. Press Enter. Excel moves to cell J3., Go to -- Ctrl+G, 1. Hold down the Ctrl key while you press "g" (Ctrl+g). The Go To dialog box, opens., 2. Type C4 in the Reference field., 3. Press Enter. Excel moves to cell C4., Go To -- The Name Box, You can also use the Name box to go to a specific cell. Just type the cell you want to, go to in the Name box and then press Enter., , 1. Type B10 in the Name box., 2. Press Enter. Excel moves to cell B10., 1.13 Select Cells, , If you wish to perform a function on a group of cells, you must first select those cells, by highlighting them. The exercises that follow teach you how to select., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 6

Page 7 :
1.14 EXERCISE 3, Select Cells – F8, To select cells A1 to E7:, 1. Go to cell A1., 2. Press the F8 key. This anchors the cursor., 3. Note that "Extend Selection" appears on the Status bar in the lower-left, corner of the window. You are in the Extend mode., 4. Click in cell E7. Excel highlights cells A1 to E7., 5. Press Esc and click anywhere on the worksheet to clear the highlighting., Alternative Method: Select Cells by Dragging, , 1. Go to cell A1., 2. Press the left mouse button., 3. While holding down the left mouse button, use the mouse to move from cell, A1 to C5., 4. Release the left mouse button., 5. Hold down the Ctrl key until step 9., 6. Using the mouse, place the cursor in cell D7., 7. Press the left mouse button., 8. While holding down the left mouse button, move to cell F10. Release the left, mouse button., 9. Release the Ctrl key. Cells A1 to C5 and cells D7 to F10 are selected., 10.Press Esc and click anywhere on the worksheet to remove the highlighting., 1.15 Enter Data, In this section, you will learn how to enter data into your worksheet. First, place the, cursor in the cell in which you want to start entering data. Type some data, and, then press Enter. If you need to delete, press the Backspace key to delete one, character at a time., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 7

Page 8 :
1.16 EXERCISE 4, Enter Data, , 1. Place the cursor in cell A1., 2. Type John Jordan. Do not press Enter at this time., 1.17 Edit a Cell – F2, After you enter data into a cell, you can edit the data by pressing F2 while you are in, the cell you wish to edit., 1.18 EXERCISE 5, Change "John" to "Jones.", 1. Move to cell A1., 2. Press F2., 3. Use the Arrow and Backspace keys to change John to Jones, 4. Press Enter., Alternate Method: Editing a Cell by Using the Formula Bar, You can also edit the cell by using the Formula bar. You change "Jones" to "Joker" in, the following exercise., , 1. Move the cursor to cell A1., 2. Click in the formula or entries area of the Formula bar, and change Jones to, Joker., 3. Press Enter., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 8

Page 9 :
Alternate Method: Edit a Cell by Double-Clicking in the Cell, You can change "Joker" to "Johnson" as follows:, , 1., 2., 3., 4., , Move to cell A1., Double-click in cell A1., Use the Arrow and Backspace keys to change Joker to Johnson., Press Enter., , Change a Cell Entry, Typing in a cell replaces the old cell entry with the new information you type., 1. Move the cursor to cell A1., 2. Type Cathy., 3. Press Enter. The name "Cathy" replaces "Johnson Jordan", , 1.19 Wrap Text, When you type text that is too long to fit in the cell, the text overlaps the next cell., If you do not want it to overlap the next cell, you can wrap the text., 1.20 EXERCISE 6, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 9

Page 10 :
1. Move to cell A2., 2. Type Text too long to fit., 3. Press Enter., , 4. Return to cell A2., 5. Choose the Home tab., 6. Click the Wrap Text button, , . Excel wraps the text in the cell., , 1.21 Delete a Cell Entry, To delete an entry in a cell or a group of cells, you place the cursor in the cell or, select the group of cells and press Delete., 1.22 EXERCISE 7, Delete a Cell Entry, 1. Select cells A1 to A2., 2. Press the Delete key., 1.23 Save a File, This is the end of Section 1. To save your file:, 1., 2., 3., 4., 5., , Click the Office button. A menu appears., Click Save. The Save As dialog box appears., Go to the directory in which you want to save your file., Type Section1 in the File Name field., Click Save. Excel saves your file., , 1.24 Close Excel, Close Microsoft Excel., 1. Click the Office button. A menu appears., 2. Click Close. Excel closes., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 10

Page 11 :
Section 2: Entering Excel Formulas and Formatting Data, Section 1 familiarized you with the Excel 2007 window, taught you how to move, around the window, and how to enter data. A major strength of Excel is that you, can perform mathematical calculations and format your data. In this Section, you, will learn how to perform basic mathematical calculations and how to format text, and numerical data. To start this Section, open Excel., 2.1, , Perform Mathematical Calculations, , In Microsoft Excel, you can enter numbers and mathematical formulas into cells., Whether you enter a number or a formula, you can reference the cell when you, perform mathematical calculations such as addition, subtraction, multiplication, or, division. When entering a mathematical formula, precede the formula with an equal, (=) sign. Use the following to indicate the type of calculation you wish to perform:, +, –, *, /, ^, , Addition, Subtraction, Multiplication, Division, Exponential, , In the following exercises, you practice some of the methods you can use to, perform mathematical calculations., 2.2, , EXERCISE 1, , 2.2.1 Addition, Subtraction, Multiplication and Division of Numbers, , 1. Type: Add, Subtract, Multiply, and Divide in cells A1, B1, C1, and D1, respectively, 2. Type: 12, 25, 11 and 75 in cells A2, B2, C2 and D2 respectively, 3. Type: 8, 13, 6 and 5 in cells A3, B3, C3 and D3 respectively, 4. Type: = A2 + A3 in cell A5 and press Enter, 5. Type: = B2 + B3 in cell A5 and press Enter, 6. Type: = C2 + C3 in cell A5 and press Enter, 7. Type: = D2 + D3 in cell A5 and press Enter, MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 11

Page 12 :
When creating formulas, you can reference cells and include numbers. All of the, following formulas are valid:, (a) =A2/B2;, , (b) =A2+12-B3;, , (c) =A2*B2+12;, , (d) =24+53/B2, , 2.2.2 Perform Advanced Mathematical Calculations, When you perform mathematical calculations in Excel, be careful of precedence., Calculations are performed from left to right, with multiplication and division, performed before addition and subtraction., 2.3, , EXERCISE 2, , Advanced Calculations, 1. Move to cell A7., 2. Type =3+3+12/2*4., 3. Press Enter., Note: Microsoft Excel divides 12 by 2, multiplies the answer by 4, adds 3, and then, adds another 3. The answer, 30, displays in cell A7., , To change the order of calculation, use parentheses. Microsoft Excel calculates the, information in parentheses first., 1. Double-click in cell A7., 2. Edit the cell to read =(3+3+12)/2*4., 3. Press Enter., Note: Microsoft Excel adds 3 plus 3 plus 12, divides the answer by 2, and then, multiplies the result by 4. The answer, 36, displays in cell A7., , 2.4, , AutoSum, , You can use the AutoSum button, , on the Home tab to automatically add a, , column or row of numbers. When you press the AutoSum button, , Excel selects, the numbers it thinks you want to add. If you then click the check mark on the, Formula bar or press the Enter key, Excel adds the numbers. If Excel's guess as to, which numbers you want to add is wrong, you can select the cells you want., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 12

Page 13 :
2.5, , EXERCISE 3, , AutoSum, The following illustrates AutoSum:, , 1., 2., 3., 4., 5., 6., 7., 8., 9., , Go to cell F1., Type 3., Press Enter. Excel moves down one cell., Type 3., Press Enter. Excel moves down one cell., Type 3., Press Enter. Excel moves down one cell to cell F4., Choose the Home tab., Click the AutoSum button, in the Editing group. Excel selects cells F1, through F3 and enters a formula in cell F4., , 10.Press Enter. Excel adds cells F1 through F3 and displays the result in cell F4., , Note that you can click on the arrow next to AutoSum to access other automatic, calculations like average, minimum and maximum values, count numbers, etc., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 13

Page 14 :
2.6 Align Cell Entries, When you type text into a cell, by default your entry aligns with the left side of the, cell. When you type numbers into a cell, by default your entry aligns with the right, side of the cell. You can change the cell alignment. You can center, left-align, or, right-align any cell entry. Look at cells A1 to D1. Note that they are aligned with the, left side of the cell., , 2.7, , EXERCISE 4, , To center cells A1 to D1:, , 1. Select cells A1 to D1., 2. Choose the Home tab., 3. Click the Center button, content., , in the Alignment group. Excel centers each cell's, , Note that left and right alignment can be carried out in a similar manner., 2.8, , Copy, Cut and Paste, , You can copy or cut data from one area of a worksheet to another., , 1. Select cells D9 to D12, 2. Choose the Home tab., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 14

Page 15 :
3. Click the Cut, button., 4. Move to cell G1., , 5. Click the Paste button, cells G1 to G4., 2.9, , . Excel moves the contents of cells D9 to D12 to, , Insert and Delete Columns and Rows, , You can insert and delete columns and rows. When you delete a column, you delete, everything in the column from the top of the worksheet to the bottom of the, worksheet. When you delete a row, you delete the entire row from left to right., Inserting a column or row inserts a completely new column or row., 2.10 EXERCISE 5, Insert and Delete Columns and Rows, To delete columns F and G:, , 1., 2., 3., 4., , Click the column F indicator and drag to column G., Click the down arrow next to Delete in the Cells group. A menu appears., Click Delete Sheet Columns. Excel deletes the columns you selected., Click anywhere on the worksheet to remove your selection., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 15

Page 16 :
To delete rows 7 through 12:, , 1., 2., 3., 4., , Click the row 7 indicator and drag to row 12., Click the down arrow next to Delete in the Cells group. A menu appears., Click Delete Sheet Rows. Excel deletes the rows you selected., Click anywhere on the worksheet to remove your selection., , To insert a column:, 1., 2., 3., 4., , Click on A to select column A., Click the down arrow next to Insert in the Cells group. A menu appears., Click Insert Sheet Columns. Excel inserts a new column., Click anywhere on the worksheet to remove your selection., , To insert rows:, 1., 2., 3., 4., , Click on 1 and then drag down to 2 to select rows 1 and 2., Click the down arrow next to Insert in the Cells group. A menu appears., Click Insert Sheet Rows. Excel inserts two new rows., Click anywhere on the worksheet to remove your selection., , 2.11 Work with Long Text, Whenever you type text that is too long to fit into a cell, Microsoft Excel attempts to, display all the text. It left-aligns the text regardless of the alignment you have, assigned to it, and it borrows space from the blank cells to the right. However, a, long text entry will never write over cells that already contain entries—instead, the, cells that contain entries cut off the long text. The following exercise illustrates this., 2.12 EXERCISE 6, Work with Long Text, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 16

Page 17 :
1. Move to cell A6., 2. Type Now is the time for all good men to go to the aid of their army., 3. Press Enter. Everything that does not fit into cell A6 spills over into the, adjacent cell., , 4. Move to cell B6., 5. Type Test., 6. Press Enter. Excel cuts off the entry in cell A6., , 7. Move to cell A6., 8. Look at the Formula bar. The text is still in the cell., 2.13 Change A Column's Width, You can increase column widths. Increasing the column width enables you to see, the long text., 2.14 EXERCISE 7, Change Column Width, , 1., 2., 3., 4., 5., 6., , Make sure you are in any cell under column A., Choose the Home tab., Click the down arrow next to Format in the Cells group., Click Column Width. The Column Width dialog box appears., Type 55 in the Column Width field., Click OK. Column A is set to a width of 55. You should now be able to see all, of the text., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 17

Page 18 :
Change a Column Width by Dragging, You can also change the column width with the cursor., 1. Place the mouse pointer on the line between the B and C column headings., The mouse pointer should look like the one displayed here, , with two, arrows., 2. Move your mouse to the right while holding down the left mouse button. The, width indicator, appears on the screen., 3. Release the left mouse button when the width indicator shows approximately, 20. Excel increases the column width to 20., Change a Column Width by AutoFit Column Width, 1., 2., 3., 4., , Select the column or column you want to change the column width., Choose the Home tab., Click the down arrow next to Format in the Cells group., Click on AutoFit Column Width. You should now be able to see all of the text., , 2.15 Format Numbers, You can format the numbers you enter into Microsoft Excel. For example, you can, add commas to separate thousands, specify the number of decimal places, place a, dollar sign in front of a number, or display a number as a percent., 2.16 EXERCISE 8, , 1. Move to cell B8., 2. Type 1234567., 3. Click the check mark [ √ ] on the Formula bar., , 4. Choose the Home tab., 5. Click the down arrow next to the Number Format box. A menu appears., 6. Click Number. Excel adds two decimal places to the number you typed., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 18

Page 19 :
7. Click the Comma Style button . Excel separates thousands with a comma., 8. Click the Accounting Number Format button, . Excel adds a dollar sign to, your number., 9. Click twice on the Increase Decimal button, to change the number format, to four decimal places., 10.Click the Decrease Decimal button, , if you wish to decrease the number of, decimal places., Change a decimal to a percent., , 1. Move to cell B9., 2. Type 0.35 (note the decimal point)., 3. Click the check mark [ √ ] on the formula bar., , 4. Choose the Home tab., 5. Click the Percent Style button, , . Excel turns the decimal to a percent., , This is the end of Section 2. You can save and close your file. See Section 1, (Subsections 1.23 and 1.24) to learn how to save and close a file., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 19

Page 20 :
Section 3: Creating Excel Functions, Filling Cells, and Printing, By using functions, you can quickly and easily make many useful calculations, such, as finding an average, the highest number, the lowest number, and a count of the, number of items in a list. Microsoft Excel has many functions that you can use., 3.1, , Using Reference Operators, , To use functions, you need to understand reference operators. Reference operators, refer to a cell or a group of cells. There are two types of reference operators: range, and union., A range reference refers to all the cells between and including the reference. A, range reference consists of two cell addresses separated by a colon. The reference, A1:A3 includes cells A1, A2, and A3. The reference A1:C3 includes cells A1, A2, A3,, B1, B2, B3, C1, C2, and C3., A union reference includes two or more references. A union reference consists of, two or more numbers, range references, or cell addresses separated by a comma., The reference A7,B8:B10,C9,10 refers to cells A7, B8 to B10, C9 and the number 10., 3.2, , Understanding Functions, , Functions are prewritten formulas. Functions differ from regular formulas in that, you supply the value but not the operators, such as +, -, *, or /. For example, you, can use the SUM function to add. When using a function, remember the following:, 1. Use an equal sign to begin a formula., 2. Specify the function name., 3. Enclose arguments within parentheses. Arguments are values on which you, want to perform the calculation. For example, arguments specify the, numbers or cells you want to add., 4. Use a comma to separate arguments., Here is an example of a function:, =SUM(2,13,A1,B2:C7), In this function, known as the SUM function:, 1., 2., 3., 4., , The equal sign begins the function., SUM is the name of the function., 2, 13, A1, and B2:C7 are the arguments. Parentheses enclose the arguments., Commas separate the arguments., , After you type the first letter of a function name, the AutoComplete list appears., You can double-click on an item in the AutoComplete list to complete your entry, quickly. Excel will complete the function name and enter the first parenthesis., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 20

Page 21 :
3.3, , EXERCISE 1, , Functions, The SUM function adds argument values., , 1., 2., 3., 4., 5., 6., 7., 8., 9., , Open Microsoft Excel., Type 12 in cell B1., Press Enter., Type 27 in cell B2., Press Enter., Type 24 in cell B3., Press Enter., Type =SUM(B1:B3) in cell A4., Press Enter. The sum of cells B1 to B3, which is 63, appears., , Alternate Method: Enter a Function with the Ribbon, , 1. Type 150 in cell C1., 2. Press Enter., 3. Type 85 in cell C2., 4. Press Enter., 5. Type 65 in cell C3, and Press Enter, 6. Choose the Formulas tab., 7. Click the Insert Function button. The Insert Function dialog box appears., 8. Choose Math & Trig in the Or Select A Category box., 9. Click Sum in the Select A Function box., 10.Click OK., 11.The Function Arguments dialog box appears with C1:C3 displayed in the, Number1 field., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 21

Page 22 :
12.Type C1:C3 in the Number1 field, if it does not automatically appear., 13.Click OK. The sum of cells C1 to C3, which is 300, appears., 3.4, , Calculate an Average, , You can use the AVERAGE function to calculate the average of a series of numbers., , 1., 2., 3., 4., 3.5, , Move to cell A6., Type Average. Press the right arrow key to move to cell B6., Type =AVERAGE(B1:B3)., Press Enter. The average of cells B1 to B3, which is 21, appears., Find the Lowest Number, , You can use the MIN function to find the lowest number in a series of numbers., , 1., 2., 3., 4., , Move to cell A7., Type Min. Press the right arrow key to move to cell B7., Type =MIN(B1:B3)., Press Enter. The lowest number in the series, which is 12, appears., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 22

Page 23 :
3.6, , Find the Highest Number, , You can use the MAX function to find the highest number in a series of numbers., , ., 1., 2., 3., 4., 3.7, , Move to cell A8., Type Max. Press the right arrow key to move to cell B8., Type =MAX(B1:B3)., Press Enter. The highest number in the series, which is 27, appears., Count the Numbers in a Series of Numbers, , You can use the count function to count the number of numbers in a series., , 1., 2., 3., 4., , Move to cell A9., Type Count. Press the right arrow key to move to cell B9., Type =COUNT(B1:B3)., Press Enter. The number of items in the series, which is 3, appears., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 23

Page 24 :
3.8, , Fill Cells Automatically, , You can use Microsoft Excel to fill cells automatically with a series. For example, you, can have Excel automatically fill your worksheet with days of the week, months of, the year, years, or other types of series., 3.9, , EXERCISE 2, , (a), , Fill Cells Automatically, , The following demonstrates filling the days of the week:, , 1., 2., 3., 4., 5., 6., 7., 8., 9., , Click the Sheet2 tab. Excel moves to Sheet2., Move to cell A1., Type Sun., Move to cell B1., Type Sunday., Select cells A1 to B1., Choose the Home tab., Click the Bold button . Excel bolds cells A1 to B1., Find the small black square in the lower-right corner of the selected area. The, small black square is called the fill handle., 10.Grab the fill handle and drag with your mouse to fill cells A1 to B14. Note how, the days of the week fill the cells in a series. Also, note that the Auto Fill, Options button appears., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 24

Page 25 :
(b), , Fill Times, , The following demonstrates filling time:, 1. Type 1:00 into cell C1., 2. Grab the fill handle and drag with your mouse to highlight cells C1 to C14., Note that each cell fills, using military time., 3. Press Esc and then click anywhere on the worksheet to remove the, highlighting., To change the format of the time:, 1. Select cells C1 to C14., 2. Choose the Home tab., 3. Click the down arrow next to the number format box, appears., 4. Click Time. Excel changes the format of the time., (c), , . A menu, , Fill Numbers, , You can also fill numbers., 1., 2., 3., 4., 5., , Type a 1 in cell D1., Type a 2 in cell D2., Select cells D1:D2, Grab the fill handle and drag with your mouse to highlight cells D1 to D14., The cells fill as a series, starting with 1, 2, 3., , Here is another interesting fill feature., 1. Go to cell E1., 2. Type Section 1., 3. Grab the fill handle and drag with your mouse to highlight cells E1 to E14. The, cells fill in as a series: Section 1, Section 2, Section 3, and so on., 3.10 Set Print Options, There are many print options. You set print options on the Page Layout tab. Among, other things, you can set your margins, set your page orientation, and select your, paper size., Margins define the amount of white space that appears on the top, bottom, left,, and right edges of your document. The Margin option on the Page Layout tab, provides several standard margin sizes from which you can choose., Paper comes in a variety of sizes. Most business correspondence uses 8 ½ by 11, paper, which is the default page size in Excel. If you are not using 8 ½ by 11 paper,, you can use the Size option on the Page Layout tab to change the Size setting., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 25

Page 26 :
3.11 EXERCISE 3, Set the Page Layout (Margins), , 1. Choose the Page Layout tab., 2. Click Margins in the Page Setup group. A menu appears., 3. Click Wide. Excel sets your margins to the Wide settings., Set the Page Orientation, , 1. Choose the Page Layout tab., 2. Click Orientation in the Page Setup group. A menu appears., 3. Click Landscape. Excel sets your page orientation to landscape., Set the Paper Size, , 1. Choose the Page Layout tab., 2. Click Size in the Page Setup group. A menu appears., 3. Click the paper size you are using. Excel sets your page size., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 26

Page 27 :
3.12 Print, The simplest way to print is to click the Office button, highlight Print on the menu, that appears, and then click Quick Print in the Preview and Print the Document, pane. Dotted lines appear on your screen, and your document prints. The dotted, lines indicate the right, left, top, and bottom edges of your printed pages. To print, from Microsoft Excel, you can proceed as follows:, Click on Microsoft Office Button, Highlight or point the mouse on Print., Click on Print., In the Name box, under Printer option, choose an appropriate printer., Under the Print Range option, choose appropriate range of pages to be, printed., 6. Under Copies option, choose appropriate number of copies of each page, to be printed., 7. Click on OK when ready., 1., 2., 3., 4., 5., , 3.13 EXERCISE 4, Print Preview, , 1. Click the Office button. A menu appears., 2. Highlight Print. The Preview and Print The Document pane appears., 3. Click Print Preview. The Print Preview window appears, with your document, in the center., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 27

Page 28 :
3.14 EXERCISE 5, Print, , 1 Click on Microsoft Office Button, 2 Highlight or point the mouse on Print., 3 Click on Print. The Print dialog box appears, , 4. In the Name box, under Printer option, choose an appropriate printer., 5. Under the Print Range option, choose appropriate range of pages to be, printed., 6. Under Copies option, choose appropriate number of copies of each page, to be printed., 7. Click on OK when ready., This is the end of Section 3. You can save and close your file., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 28

Page 29 :
Section 4: Creating Charts, In Microsoft Excel, you can represent numbers in a chart. On the Insert tab, you can, choose from a variety of chart types, including column, line, pie, bar, area, and, scatter. The basic procedure for creating a chart is the same no matter what type of, chart you choose. As you change your data, your chart will automatically update., You select a chart type by choosing an option from the Insert tab's Chart group., After you choose a chart type, such as column, line, or bar, you choose a chart subtype. For example, after you choose Column Chart, you can choose to have your, chart represented as a two-dimensional chart, a three-dimensional chart, a cylinder, chart, a cone chart, or a pyramid chart. There are further sub-types within each of, these categories. As you roll your mouse pointer over each option, Excel supplies a, brief description of each chart sub-type., 4.1, , Create a Chart, , To create the column chart shown above, start by creating the worksheet below, exactly as shown., , After you have created the worksheet, you are ready to create your chart., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 29

Page 30 :
4.2, , EXERCISE 1, , Create a Column Chart, , ., 1. Select cells A3 to D6. You must select all the cells containing the data you, want in your chart. You should also include the data labels., 2. Choose the Insert tab., 3. Click the Column button in the Charts group. A list of column chart sub-types, types appears., 4. Click the Clustered Column chart sub-type. Excel creates a Clustered Column, chart and the Chart Tools context tabs appear., 4.3, , Apply a Chart Layout, , Context tabs are tabs that only appear when you need them. Called Chart Tools,, there are three chart context tabs: Design, Layout, and Format. The tabs become, available when you create a new chart or when you click on a chart. You can use, these tabs to customize your chart., 4.4, , EXERCISE 2, , Apply a Chart Layout, , 1. Click your chart. The Chart Tools become available., 2. Choose the Design tab., 3. Click the Quick Layout button in the Chart Layout group. A list of chart layouts, appears., 4. Click Layout 5. Excel applies the layout to your chart., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 30

Page 31 :
4.5, , Change the Style of a Chart, , A style is a set of formatting options. You can use a style to change the color and, format of your chart. Excel 2007 has several predefined styles that you can use., They are numbered from left to right, starting with 1, which is located in the upperleft corner., 4.6, , EXERCISE 3, , Change the Style of a Chart, , 1. Click your chart. The Chart Tools become available., 2. Choose the Design tab., 3. Click the More button in the Chart Styles group. The chart styles appear., , 4. Click Style 42. Excel applies the style to your chart., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 31

Page 32 :
4.7, , Change the Size and Position of a Chart, , When you click a chart, handles appear on the right and left sides, the top and, bottom, and the corners of the chart. You can drag the handles on the top and, bottom of the chart to increase or decrease the height of the chart. You can drag, the handles on the left and right sides to increase or decrease the width of the, chart. You can drag the handles on the corners to increase or decrease the size of, the chart proportionally. You can change the position of a chart by clicking on an, unused area of the chart and dragging., 4.8, , EXERCISE 4, , Change the Size and Position of a Chart, , 1. Use the handles to adjust the size of your chart., 2. Click an unused portion of the chart and drag to position the chart beside the, data., 4.9, , Move a Chart to a Chart Sheet, , By default, when you create a chart, Excel embeds the chart in the active, worksheet. However, you can move a chart to another worksheet or to a chart, sheet. A chart sheet is a sheet dedicated to a particular chart. By default Excel, names each chart sheet sequentially, starting with Chart1. You can change the, name., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 32

Page 33 :
4.10 EXERCISE 5, Move a Chart to a Chart Sheet, , 1. Click your chart. The Chart Tools become available., 2. Choose the Design tab., 3. Click the Move Chart button in the Location group. The Move Chart dialog, box appears., , 4. Click the New Sheet radio button., 5. Type Toy Sales to name the chart sheet. Excel creates a chart sheet named, Toy Sales and places your chart on it., 4.11 Change the Chart Type, Any change you can make to a chart that is embedded in a worksheet, you can also, make to a chart sheet. For example, you can change the chart type from a column, chart to a bar chart., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 33

Page 34 :
4.12 EXERCISE 6, Change the Chart Type, , 1. Click your chart. The Chart Tools become available., 2. Choose the Design tab., 3. Click Change Chart Type in the Type group. The Chart Type dialog box, appears., 4. Click Bar., 5. Click Clustered Horizontal Cylinder., 6. Click OK. Excel changes your chart type., , You have reached the end of Section 4. You can save and close your file., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 34

Page 35 :
Section 5: More on Entering Excel Formulas, This Section looks at more examples of how to enter and execute Excel Formulas., 5.1, , The SUMIF Function, , Syntax, SUMIF(range,criteria,sum_range), Range is the range of cells where Excel searches for the criteria that you want, evaluated. Cells in each range must be numbers or names, arrays, or references that, contain numbers. Blank and text values are ignored., Criteria is the criteria in the form of a number, expression, or text that defines, which cells will be added. For example, criteria can be expressed as 32, "32", ">32",, or "apples"., Sum_range are the actual cells to add if their corresponding cells in range match, criteria. If sum_range is omitted, the cells in range are both evaluated by criteria, and added if they match criteria., Note: The SUMIF function can be read as:, “Sum or add up sum_range if range meets criteria.”, Example, A, , B, , 1, , Property Value, , Commission, , 2, , 100,000, , 7,000, , 3, , 200,000, , 14,000, , 4, , 300,000, , 21,000, , 5, , 400,000, , 28,000, , Formula, , Description (Result), , =SUMIF(A2:A5,">160000",B2:B5), , Sum of, property, (63,000), , =SUMIF(A2:A5,">160000"), , Sum of the property values over, 160,000 (900,000), , =SUMIF(A2:A5,"=300000",B2:B3), , Sum of the commissions for, property values equal to 300,000, (21,000), , the commissions for, values over 160,000, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 35

Page 36 :
5.2, , The AVERAGEIF Function, , Returns the average (arithmetic mean) of all the cells in a range that meet a given, criteria., Syntax, AVERAGEIF(range,criteria,average_range), Range is one or more cells to average, including numbers or names, arrays, or, references that contain numbers., Criteria is the criteria in the form of a number, expression, cell reference, or text, that defines which cells are averaged. For example, criteria can be expressed as 32,, "32", ">32", "apples", or B4., Average_range is the actual set of cells to average. If omitted, range is used., Note: The AVERAGEIF function can be read as:, “Average average_range if range meets criteria.”, Example: Averaging profits from regional offices, 1, 2, 3, 4, 5, 6, 7, 8, 9, , A, , B, , Region, , Profits (Thousands), , East, , 45,678, , West, , 23,789, , North, , -4,789, , South (New Office), , 0, , MidWest, , 9,678, , Formula, , Description (result), , =AVERAGEIF(A2:A6,"=*West",B2:B6), , Average of all profits for the, West and MidWest regions, (16,733.5), , =AVERAGEIF(A2:A6,"<>*(New, Office)",B2:B6), , Average of all profits for all, regions, excluding, new, offices (18,589), , 10, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 36

Page 37 :
5.3, , The COUNTIF Function, , Counts the number of cells within a range that meet the given criteria., Syntax:, , COUNTIF(range,criteria), , Range is one or more cells to count, including numbers or names, arrays, or, references that contain numbers. Blank and text values are ignored., Criteria is the criteria in the form of a number, expression, cell reference, or text, that defines which cells will be counted. For example, criteria can be expressed as, 32, "32", ">32", "apples", or B4., Note: The COUNTIF function can be read as:, “Count frequency or number of times or cells if range contains criteria.”, Remark, You can use the wildcard characters, question mark (?) and asterisk (*), in, criteria. A question mark matches any single character; an asterisk matches any, sequence of characters. If you want to find an actual question mark or asterisk,, type a tilde (~) before the character., Example 1: Common COUNTIF formulas, A, , B, , 1, , Data, , Data, , 2, , apples, , 32, , 3, , oranges, , 54, , 4, , peaches, , 75, , 5, , apples, , 86, , Formula, , Description (result), , =COUNTIF(A2:A5,"apples"), , Number of cells with apples in the first, column above (2), , =COUNTIF(A2:A5,A4), , Number of cells with peaches in the first, column above (1), , =COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2), , Number of cells with oranges and apples in, the first column above (3), , =COUNTIF(B2:B5,">55"), , Number of cells with a value greater than 55, in the second column above (2), , =COUNTIF(B2:B5,"<>"&B4), , Number of cells with a value not equal to 75, in the second column above (3), , =COUNTIF(B2:B5,">=32")COUNTIF(B2:B5,">85"), , Number of cells with a value greater than or, equal to 32 and less than or equal to 85 in, the second column above (3), , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 37

Page 38 :
Example 2: COUNTIF formulas using wildcard characters and handling blank values, A, , B, , 1, , Data, , Data, , 2, , apples, , Yes, , 3, , oranges, , NO, , peaches, , No, , apples, , YeS, , Formula, , Description (result), , =COUNTIF(A2:A7,"*es"), , Number of cells ending with, the letters "es" in the first, column above (4), , =COUNTIF(A2:A7,"?????es"), , Number of cells ending with, the letters "es" and having, exactly 7 letters in the first, column above (2), , =COUNTIF(A2:A7,"*"), , Number of cells containing, text in the first column above, (4), , =COUNTIF(A2:A7,"<>"&"*"), , Number of cells not containing, text in the first column above, (2), , =COUNTIF(B2:B7,"No")/ROWS(B2:B7), , The average number of No, votes including blank cells in, the second column above, formatted as a percentage, with no decimal places (33%), , =COUNTIF(B2:B7,"Yes")/(ROWS(B2:B7)COUNTIF(B2:B7,"<>"&"*")), , The average number of Yes, votes excluding blank cells in, the second column above, formatted as a percentage, with no decimal places (50%), , 4, 5, 6, 7, , NOTE You can view the number as a percentage. Select the cell, and then on the, Sheet tab in the Number group, click Percentage Style ., 5.4, , The IF Function, , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 38

Page 39 :
Returns one value if a condition you specify evaluates to TRUE and another value if, it evaluates to FALSE., Syntax, IF(logical_test,value_if_true,value_if_false), Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For, example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the, expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE., Value_if_true is the value that is returned if logical_test is TRUE., Value_if_false is the value that is returned if logical_test is FALSE., Remarks: Up to 64 IF functions can be nested as value_if_true and value_if_false, arguments to construct more elaborate tests., Note: The IF function can be read as:, “If Logical_test then Value_if_true otherwise Value_if_false”, Example, 1, 2, 3, 4, , A, Score, 45, 90, 78, Formula, , Description (Result), , =IF(A2<50,"FAIL","PASS"), , Assigns either a pass or fail, remark to the first score (FAIL), , =IF(A2>89,"A",IF(A2>79,"B",, IF(A2>69,"C",IF(A2>59,"D","F")))), , Assigns a letter grade to the, first score (F), , =IF(A3>89,"A",IF(A3>79,"B",, IF(A3>69,"C",IF(A3>59,"D","F")))), , Assigns a letter grade to the, second score (A), , =IF(A4>89,"A",IF(A4>79,"B",, IF(A4>69,"C",IF(A4>59,"D","F")))), , Assigns a letter grade to the, third score (C), , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 39

Page 40 :
5.5, , The AND Function, , Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument, is FALSE., Syntax, AND(logical1,logical2, ...), Logical1, logical2, ... are 1 to 255 conditions you want to test that can be either, TRUE or FALSE., Example 1, A, , B, , 1, , Formula, , Description (Result), , 2, , =AND(TRUE, TRUE), , All arguments are TRUE (TRUE), , 3, , =AND(TRUE, FALSE), , One argument is FALSE (FALSE), , 4, , =AND(2+2=4, 2+3=5), , All arguments evaluate to TRUE (TRUE), , Example 2, A, 1, , Data, , 2, , 50, , 3, , 104, Formula, , Description (Result), , =AND(1<A2,A2<100), , Because 50 is between 1 and 100, (TRUE), , =IF(AND(1<A3,A3<100),A3, "The, value is out of range."), , Displays the second number above,, if it is between 1 and 100,, otherwise displays a message (The, value is out of range.), , =IF(AND(1<A2,A2<100),A2, "The, value is out of range."), , Displays the first number above, if it, is between 1 and 100, otherwise, displays a message (50), , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 40

Page 41 :
5.6, , The FREQUENCY Function, , Calculates how often values occur within a range of values, and then returns a, vertical array of numbers. For example, use FREQUENCY to count the number of, test scores that fall within ranges of scores. Because FREQUENCY returns an array, it, must be entered as an array formula., Syntax:, , FREQUENCY(data_array,bins_array), , Data_array is an array of or reference to a set of values for which you want to count, frequencies. If data_array contains no values, FREQUENCY returns an array of zeros., Bins_array is an array of or reference to intervals into which you want to group the, values in data_array. If bins_array contains no values, FREQUENCY returns the, number of elements in data_array., Remarks, FREQUENCY is entered as an array formula after you select a range of, adjacent cells into which you want the returned distribution to appear., The number of elements in the returned array is one more than the number, of elements in bins_array., Example, A, , B, , 1, , Scores, , Bins, , 2, , 79, , 70, , 3, , 85, , 79, , 4, , 78, , 89, , 5, , 85, , 6, , 50, , 7, , 81, , 8, , 95, , 9, , 88, , 10, , 97, Formula, , Description (Result), , =FREQUENCY(A2:A10,B2:B4), , Number of scores less than or equal to 70 (1), Number of scores in the bin 71-79 (2), Number of scores in the bin 80-89 (4), Number of scores greater than or equal to 90 (2), , NOTE The formula in the example must be entered as an array formula. After, copying the example to a blank worksheet, select the range A12:A15, press F2, and, then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula,, there will be only one result in cell A12 (1)., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 41

Page 42 :
5.7, , The TREND Function, , Returns values along a linear trend. Fits a straight line (using the method of least, squares) to the arrays known_y's and known_x's. Returns the y-values along that, line for the array of new_x's that you specify., Syntax, TREND(known_y's,known_x's,new_x's,const), Known_y's is the set of y-values you already know in the relationship y = mx + b., If the array known_y's is in a single column, then each column of known_x's is, interpreted as a separate variable., If the array known_y's is in a single row, then each row of known_x's is, interpreted as a separate variable., Known_x's is an optional set of x-values that you may already know in the, relationship y = mx + b., The array known_x's can include one or more sets of variables. If only one, variable is used, known_y's and known_x's can be ranges of any shape, as long, as they have equal dimensions. If more than one variable is used, known_y's, must be a vector (that is, a range with a height of one row or a width of one, column)., If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the, same size as known_y's., New_x's are new x-values for which you want TREND to return corresponding, y-values., New_x's must include a column (or row) for each independent variable, just as, known_x's does. So, if known_y's is in a single column, known_x's and new_x's, must have the same number of columns. If known_y's is in a single row,, known_x's and new_x's must have the same number of rows., If you omit new_x's, it is assumed to be the same as known_x's., If you omit both known_x's and new_x's, they are assumed to be the array, {1,2,3,...} that is the same size as known_y's., Const is a logical value specifying whether to force the constant b to equal 0., If const is TRUE or omitted, b is calculated normally., If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so, that y = mx., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 42

Page 43 :
Remarks, You can use TREND for polynomial curve fitting by regressing against the same, variable raised to different powers. For example, suppose column A contains, y-values and column B contains x-values. You can enter x^2 in column C, x^3, in column D, and so on, and then regress columns B through D against column, A., Formulas that return arrays must be entered as array formulas., When entering an array constant for an argument such as known_x's, use, commas to separate values in the same row and semicolons to separate rows., Example, A, , B, , C, , 1, , Month, , Cost, , Formula (Corresponding Cost), , 2, , 1, , $133,890, , =TREND(B2:B13,A2:A13), , 3, , 2, , $135,000, , 4, , 3, , $135,790, , 5, , 4, , $137,300, , 6, , 5, , $138,130, , 7, , 6, , $139,100, , 8, , 7, , $139,900, , 9, , 8, , $141,120, , 10, , 9, , $141,890, , 11, , 10, , $143,230, , 12, , 11, , $144,000, , 13, , 12, , $145,290, , Month, , Formula (Predicted Cost), , 13, , =TREND(B2:B13,A2:A13,A15:A19), , 14, 15, 16, 17, , NOTE The formula in the example must be entered as an array formula. After, copying the example to a blank worksheet, select the range C2:C13 or B15:B19, starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the, formula is not entered as an array formula, the single results are 133953.3333 and, 146171.5152., MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 43

Page 44 :
5.8, , The ZTEST Function, , Returns the one-tailed probability-value of a z-test. For a given hypothesized, population mean, μ0, ZTEST returns the probability that the sample mean would be, greater than the average of observations in the data set (array) — that is, the, observed sample mean., To see how ZTEST can be used in a formula to compute a two-tailed probability, value, see "Remarks" below., Syntax, ZTEST(array,μ0,sigma), Array is the array or range of data against which to test μ0, μ0 is the value to test., Sigma is the population (known) standard deviation. If omitted, the sample, standard deviation is used., Remarks, If array is empty, ZTEST returns the #N/A error value., ZTEST is calculated as follows when sigma is not omitted:, , or when sigma is omitted:, , where x is the sample mean AVERAGE(array); s is the sample standard, deviation STDEV(array); and n is the number of observations in the sample, COUNT(array)., ZTEST represents the probability that the sample mean would be greater than, the observed value AVERAGE(array), when the underlying population mean is, μ0. From the symmetry of the Normal distribution, if AVERAGE(array) < μ0,, ZTEST will return a value greater than 0.5., The following Excel formula can be used to calculate the two-tailed probability, that the sample mean would be further from μ0 (in either direction) than, AVERAGE(array), when the underlying population mean is μ0:, =2 * MIN(ZTEST(array,μ0,sigma), 1 - ZTEST(array,μ0,sigma))., , MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 44

Page 45 :
Example, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, , A, Data, 3, 6, 7, 8, 6, 5, 4, 2, 1, 9, Formula, , Description (Result), , =ZTEST(A2:A11,4), , One-tailed probability-value of a z-test for the data set, above, at the hypothesized population mean of 4, (0.090574), , =2 * MIN(ZTEST(A2:A11,4), 1 ZTEST(A2:A11,4)), , Two-tailed probability-value of a z-test for the data set, above, at the hypothesized population mean of 4, (0.181148), , =ZTEST(A2:A11,6), , One-tailed probability-value of a z-test for the data set, above, at the hypothesized population mean of 6, (0.863043), , =2 * MIN(ZTEST(A2:A11,6), 1 ZTEST(A2:A11,6)), , Two-tailed probability-value of a z-test for the data set, above, at the hypothesized population mean of 6, (0.273913), , You have reached the end of Section 5. You can save and close your file., NOTE: The Excel Functions discussed in Section 5 are only a few of the numerous Excel, Functions available in Microsoft Office 2007. More assistance and exposure can be, obtained through the Microsoft Excel Help (F1), For Training and Software Installation, kindly contact our Chief Consultant:, Aloy Onyeka, PhD, Department of Statistics, Federal University of Technology, Owerri, PMB 1526, Owerri, Imo State, NIGERIA, Email: [email protected]; Phone: 234-803-710-3677, Fmr. Director, Information and Communication Technology (ICT) Centre, Federal University of Technology, Owerri (FUTO), MS-EXCEL LECTURE NOTES FOR OWERRI CBT HI-TECH, OWERRI NIGERIA, , Page 45