Working with Spreadsheets - Business Decision Making - ثاني ثانوي

Lesson 6 Chapter 9 www.ien.edu.sa Working with Spreadsheets 6-1 The Evolution of Spreadsheets We live in an era where data are abundant and available. We are constantly con- nected to the Internet at home, school, and work. Never in human history have so many people had so much access to so much information. Imagine at this very moment if you wanted to know: the score from a recent sporting event وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 313 . what programs will be on TV this evening . the price of an item that you would like to buy from a local store. In a matter of moments, you could use a computer, tablet, or cellphone to find the information you need. Because of rapid advances in computer, Internet, and wireless technology we are accustomed to accessing data on demand-some- thing that was unthinkable a generation ago. Businesses rely heavily on this abundance of data to guide their decisions and help them shape their strategy. The expression "data-driven decision making" describes how managers and other professionals take actions that are guided carefully analyzing relevant information. It is no longer acceptable to make important business decisions based on one's own assumptions. As you prepare to start your career and begin to take on professional responsibilities, learning how to use decision-support technology will become very important. How do professionals manage the large amounts of data they have to work with? How can managers process information quickly enough that they can use it to respond to new challenges and opportunities? A couple of decades ago these were the most significant obstacles to making data-driven decisions. There was simply more data than a human could reasonably be expected to manage and work with. Today there are many different types of decision-support systems available, and one of the most popular is the computer spreadsheet. DEFINITION Spreadsheet: An electronic file used to arrange data in a grid, which can then be used in calculations. Using Data to Support the Decision Making Process 313 30/06/2023 14:28

6: Working with Spreadsheets

www.ien.edu.sa 314 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 314 SPREADSHEET APPLICATIONS The first commercial spreadsheet was called VisiCalc ("Visible Calculator"). It was released in 1979 on the Apple computer. It became an instant suc- cess with accountants, financial managers, and other people who worked with numeric data. Today, Microsoft's Excel spreadsheet is the market leader. It is available for PCs, Macs, and online in a Web-based format. However, there are other spreadsheet applications including Apple Numbers, Google Sheets, and OpenOffice Calc. A modern personal computer has the power to work with extremely large sets of data. If the computer is connected to the Internet, it may also be used to access publicly available information from businesses, government, and educational sites. The computer can quickly perform complex calculations and produce detailed solutions and graphic representations of the information. All of the computations are done by the computer itself, which allows the user to focus on how to model the data, what tests to run, and how to analyze and interpret the results. If your experience with computer spreadsheets is limited, you can access a tutorial that reviews the basics of this important software tool. Click on the QR code on this page to access this guide. If you have prior experience with spreadsheets, this lesson will present the basic functions needed to begin using the application as a decision-support system. In this lesson, you will work through a simple example that demonstrates how a spreadsheet may be used to create a model of a real business situation. Then, you will use the model to make important business decisions for the firm. These decisions are more complex than most people could manage if working without a computer. However, you will see in this example how the computer can sim- plify this process and give you valuable insights and recommendations based on the data you've provided. When learning something new, one of the best ways to understand it is to actu- ally work with it. In this lesson, you will learn about spreadsheet concepts by actually creating a workbook, populating it with data, performing some basic analyses, and generating a chart. To complete this exercise, you will need to use 30/06/2023 14:28

6: Working with Spreadsheets

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 315 a computer (PC, Mac, Chromebook) and a spreadsheet application. There are several popular options for spreadsheets including Microsoft Excel, Apple Numbers, Google Sheets, and OpenOffice Calc. Each of these spreadsheet appli- cations are similar to each other with only minor differences in the way they look and feel. For the example in this lesson, we will use the popular Excel spread- sheet in Windows. 6-2 Spreadsheet Basics The intersection of each column and row is a cell. A cell is the box, formed by the intersection of a column and a row, where you enter data. A cell is referred to by its unique address, or cell reference, which is the column letter and row number location that identifies a cell within a worksheet, such as A1. To identify a cell, specify the column letter first, followed by the row number. DEFINITIONS Cell: The intersection of a row and a column in a spreadsheet. Cell reference: The combination of column letter and row number that identifies a cell on a worksheet. Worksheet: The basic element of a spreadsheet, made up of rows and columns. Using Data to Support the Decision Making Process 315 30/06/2023 14:28

6: Working with Spreadsheets

6-3 Worked Example FIGURE 9-8: A workbook-even a blank one-contains many elements Quick Access Toolbar Microsoft account area AutoSave Off Book! - Excel Search (Alt-Q) File Home Insert Page Layout Formulas Data Review View Help Name box Calibri 11 - Paste BIU AA A Wrap Text General Merge & Center Clipboard Font Alignment Heavy border surrounding active cell 3 Worksheet C D E F G Cell D5 window 9 10 11 Highlighted row and column 12 Row heading 12 headings indicate 13 cell A1 is active 14 15 16 17 18 Sheet tab 19 20 21 New sheet button 22 Sheet1 Ready Tab scrolling buttons 316 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 316 Conditional Format as Cell Table Styles Insert Delete Format Number Search box tyles Cells Gridlines Status bar View buttons; default is Normal view N Column heading L Scroll boxes 0 Comments Share WE Σ' ΑΡ Ribbon Sort & Find & Filter Analyze Select- Data Editing Analysis Adjusts size of displayed document R Scroll bars 1. Enter Text Let's begin this exercise by creating a worksheet for a real estate agency. To do this, follow along with the steps shown below. Common questions are answered at each step. Type Real Estate Company Budget in cell A1 (Figure 9-9). Why did the appearance of the formula bar change? Excel displays the title in the formula bar and in cell A1. When you begin typing a cell entry, Excel enables two additional buttons in the formula bar: the Cancel button and the Enter button. Clicking the Enter button completes an entry. Clicking the Cancel button cancels an entry. D 100% Scroll arrows 30/06/2023 14:28

6: Working with Spreadsheets

] FIGURE 9-9: Making a start by typing into cell A1 AutoSave off H 2 - Book1.xlsx Saved v File Home Insert Page Layout Formulas Data Review Vie Calibri 11 v AA ab Wrap Paste Enter button BIU A Merg Cancel button Formula bar Clipboard Font Alignment A1 fx Real Estate Company Budget B C D G H 1 Real Estate Company Budget text in active cell overflows into adjacent cells to the right 2 ] Click the Enter button in the formula bar to complete the entry and enter the worksheet title (Figure 9-10). Why does the entered text appear in three cells? When the typed text is longer than the width of a cell, Excel displays the overflow characters in adjacent cells to the right as long as those adjacent cells contain no data. If the adjacent cells contain data, Excel hides the overflow characters. The overflow characters are visible in the formula bar whenever that cell is active. FIGURE 9-10: Use the Enter button when you've finished typing, or press Enter on the keyboard AutoSave Off وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 317 Book1.xlsx File Home Insert Page Layout Formulas Data Review X Calibri 11 A A Paste BIU AE EE Enter button Clipboard Alignment A1 A foc Text entered in cell A1 B C D Real Estate Company Budget E EL F G 1 Real Estate Company Budget 2 Using Data to Support the Decision Making Process 317 30/06/2023 14:28

6: Working with Spreadsheets

318 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 318 . Click cell A2 to select it. • Type Monthly Estimates as the cell entry. . Q&A Click the Enter button to complete the entry and enter the worksheet subtitle (Figure 9-11). What happens when I click the Enter button? When you complete an entry by clicking the Enter button, the cell below the newly filled data cell becomes the active cell. FIGURE 9-11: Moving from cell A1 to A2 by pressing the Enter button AutoSave Off Book1.xlsx Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help X Calibri 11 Paste BIU Enter button A A A Wrap Text Merge & Center Gen CO Clipboard Font Alignment A2 fx Monthly Estimates B D E F 1 Real Estate Company Budget 2 Monthly Estimates 3 Worksheet subtitle entered in cell A2 2. Enter Column Titles The worksheet is divided into two parts, income and expense, as shown in Figure 4-14. Grouping income and expense data by month is a common method for organizing budget data. The column titles shown in row 3 iden- tify the income section of the worksheet and indicate that the income values will be grouped by month. Likewise, row 8 is clearly identified as the expense section and similarly indi- cates that the expense values will be estimated on a per-month basis. The following steps enter the column titles in row 3. Why? Data entered in col- umns should be identified using column titles to identify what the column contains. STEP 1 Click cell A3. Type Income to begin entry of a column title in the cell (Figure 9-12). 30/06/2023 14:28

6: Working with Spreadsheets

وزارة التعليم Ministry of Education 2024-1446 FIGURE 9-12: Entering the first column title in cell A3 Business Decision Making S1 S2 S3.indb 319 AutoSave Off H Book1.xlsx v File Home Insert Page Layout Formulas Data Re X Calibri 11 AA =3 ΓΑ Active cell reference in Name BIU A box changes to A3 Clipboard Font 17 Alig A3 fx Income B C D E F 1 Real Estate Company Budget 2 Monthly Estimates 3 Income 4 Cell A3 is active cell Income displayed in formula bar and cell A3 STEP 2 Press the RIGHT ARROW key to enter the column title and make the cell to the right the active cell (Figure 9-13). Why is the RIGHT ARROW key used to complete the entry in the cell? Pressing an arrow key to complete an entry makes the adjacent cell in the direction of the arrow (up, down, left, or right) the next active cell. However, if your next entry is in a non-adjacent cell, you can complete your current entry by clicking the next cell in which you plan to enter data. You also can press ENTER and then click the appropriate cell for the next entry. FIGURE 9-13: Moving between cells by using the arrow keys AutoSave Off H - Book1.xls File Home Insert Page Layout Formulas X Calibri 11 Α' Α 三三 Paste BIU A Clipboard Font B3 B 1 Real Estate Company Budget 2 Monthly Estimates 3 Income Column title entered D E Cell B3 is active cell பட Using Data to Support the Decision Making Process 319 30/06/2023 14:28

6: Working with Spreadsheets

. STEP 3 Repeat Steps 1 and 2 to enter the remaining column titles; that is, enter January in cell B3, February in cell C3, March in cell D3, April in cell E3, May in cell F3, June in cell G3, July in cell H3, August in cell 13, September in cell J3, October in cell K3, November in cell L3, December in cell M3, and Total in cell N3 (com- plete the last entry in cell N3 by clicking the Enter button in the formula bar). Click cell A8 to select it. Repeat Steps 1 and 2 to enter the remaining column titles: Expenses in cell A8, January in cell B8, February in cell C8, March in cell D8, April in cell E8, May in cell F8, June in cell G8, July in cell H8, August in cell 18, September in cell J8, October in cell K8, November in cell L8, December in cell M8, and Total in cell N8 (complete the last entry in cell N8 by clicking the Enter button in the formula bar) (Figure 9-14). FIGURE 9-14: Completing the list of column titles AutoSave Off Book1.xlsx Saved Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Calibri 11 Paste BIU AA A = Wrap Text General Merge & Center %9 Enter button Conditional Format as Cell Formatting Table Insert Styles Clipboard Font Б Alignment Number Styles $22 fx B D E L G H K M N 1 Real Estate Company Budget 2 Monthly Estimates 3 Income January February March April May June July 4 5 7 69 6 8 Expenses January February March April May June July 9 10 11 123 12 320 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 320 August September October November December Total August September October November December Total Column titles left-aligned in cells 30/06/2023 14:28

6: Working with Spreadsheets

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 321 3. Enter Row Titles • The next step in developing the worksheet for this project is to enter the row titles in column A. For the real estate budget worksheet data, the row titles contain a list of income types and expense types. Each income or expense item should be placed in its own row. Why? Entering one item per row allows for maximum flexibility, in case more income or expense items are added in the future. The following steps enter the row titles in the worksheet. STEP 1 Click cell A4 to select it. Type Commission and then click cell A5 or press the DOWN ARROW key to enter a row title (Figure 9-15). FIGURE 9-15: Navigate from cell A4 to cell A5 by clicking on A5 or pressing the DOWN ARROW key AutoSave Off Book1.xlsx Saved v Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help X Calibri 11 AA Wrap Text General BIU A Merge & Center Clipboard Font Alignment A5 fx B C m G H 1 Real Estate Company Budget 60 .00 Conditiona %9 00-> Formatting Number 2 Monthly Estimates 3 Income January February Ma June July August September October November De 4 Commission Row title, Commission, entered in cell A4 7 5671 Cell A5 is active cell 8 Expenses Januar March April May June July August September October November D 9 10 Using Data to Support the Decision Making Process 321 30/06/2023 14:28

6: Working with Spreadsheets

Q&A STEP 2 Repeat Step 1 to enter the remaining row titles in column A; that is, enter Interest in cell A5, Total in cell A6, Rent in cell A9, Utilities in cell A10, Advertising in cell A11, Web site in cell A12, Printing in cell A13, Office Supplies in cell A14, Gas in cell A15, Miscellaneous in cell A16, and Total in cell A17. Why is the text left-aligned in the cells? Excel automatically left-aligns the text in the cell. Excel treats any combination of numbers, spaces, and non-numeric characters as text. For example, Excel would recognize the following entries as text: 401AX21, 921-231, 619 321, 883XTY. See Figure 9-16. FIGURE 9-16: Completing the spreadsheet's row titles AutoSave Off File Home Insert Page Layout Calibri Paste BIU Clipboard A19 Font Book1.xlsx Formulas Data Re AA A Alig f Net B C D E 1 Real Estate Company Budget 9 2 Monthly Estimates 3 Income January February March April May June 4 Commission 5 Interest 6 Total Row titles 7 entered 8 Expenses January February March April May June 9 Rent 10 Utilities 322 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 322 11 Advertising 12 Website 13 Printing 14 Office Supplies 15 Gas 16 Miscellaneous 17 Total 18 30/06/2023 14:28

6: Working with Spreadsheets

4. Using Numbers In Excel, you enter a number into a cell to represent an amount or value. A number is an amount or value using any of the following characters: 0 12 3456789 +-(), /. $ Ee. The use of special characters is explained when they are used in this book. If you are entering numbers that will not be used in a calculation, you should format those numbers as text. You can format numeric data as text by typing an apostrophe before the number(s). 5. Enter the Numbers The real estate budget worksheet numbers are summarized in Table 9-3. These numbers, which represent yearly income and expense amounts, are entered in rows 4-5 and 9–16. Why? One of the most powerful features of Excel is the ability to perform calculations on numeric data. Before you can perform calculations, you must first enter the data. The following steps enter the numbers in Table 9-3 one row at a time. TABLE 9-3 Real Estate Company Budget Worksheet Income Commission Interest January February March April May May June July August September October November December 12000 12000 14000 14000 16000 18500 20000 18500 18500 14000 14000 16500 100 100 100 100 100 100 100 100 100 100 100 100 Expenses January February March April Rent 1500 1500 1500 1500 May May June 1500 1500 1500 July August September October November December 1500 1500 1500 1500 1500 Utilities 325 325 325 325 325 325 325 325 325 325 325 325 Advertising 400 400 400 400 400 400 400 400 400 400 400 400 Web site 0 0 0 0 0 0 500 0 0 0 0 0 Printing 200 200 200 200 200 200 200 200 200 200 200 200 Office Supplies 200 0 0 200 0 0 200 0 0 200 0 0 Gas 100 100 100 100 100 100 100 100 100 100 100 100 Miscellaneous 250 250 250 250 250 250 250 250 250 250 250 250 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 323 Using Data to Support the Decision Making Process 323 30/06/2023 14:28

6: Working with Spreadsheets

324 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 324 STEP 1 Click cell B4 to select it. Type 12000 and then press the RIGHT ARROW key to enter the data in the selected cell and make the cell to the right (cell C4) the active cell (Figure 9-17). FIGURE 9-17: Begin to enter data in the corresponding cells AutoSave Off File Home Insert Page Layout Formulas Data Revie & Cut Copy → Calibri 11 A A Paste BIU AE וד Format Painter Clipboard C4 A Font fx B D E F 1 Real Estate Company Budget 2 Monthly Estimates 3 Income 4 Commission 5 Interest 6 Total 7 January February March 12000 Ancil Cell C4 is active cell May 12000 entered in cell B4 STEP 2 Refer to Table 9-3 and enter the appropriate values in cells C4, D4, E4, F4, G4, H4, 14, J4, K4, L4, and M4 to complete the first row of numbers in the worksheet (Figure 9-18). Why are the numbers right-aligned? When you enter numeric data in a cell, Excel recognizes the values as numbers and automatically right-aligns the values in order to vertically align decimal and integer values. 30/06/2023 14:28

6: Working with Spreadsheets

FIGURE 9-18: Note that Excel right-aligns numeric data automatically, making it easier to view and compare Search (Alt+Q) AutoSave Off Book1.xlsx File Home Insert Page Layout Formulas Data Review View Help Calibri AA = Wrap Text General Paste BIU 三 Merge & Center % 0.00 000 Clipboard Font Alignment Number Conditional Format as Formatting Table Styles Styles Cell Insert M4 fx 16500 B 1 Real Estate Company Budget 2 Monthly Estimates 3 Income 4 Commission D E EL January 12000 February March 12000 April May 14000 14000 16000 G June July 18500 20000 August 18500 M N September October November December Total 18500 14000 14000 16500 Numbers right- ugust September October November December Total 5 Interest 6 Total 7 8 Expenses January February March April May June aligned in cells 9 Rent 10 Utilities 11 Adver Commission amounts entered in row 4 STEP 3 Click cell B5 to select it and complete the entries below. Enter the remaining numbers provided in Table 4-3 for each of the nine remain- ing budget items in row 5 and rows 9-16 (Figure 9-19). FIGURE 9-19: Text in cells where text had overflowed across adjacent cells is partially obscured AutoSave Off Book1.xlsx Saved Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Calibri A A Wrap Text General BIU 训 Merge & Center -% <--0 Clipboard Font Alignment Number Conditional Format as Cell Formatting Table Styles Styles M16 250 E F G M 1 Real Estate Company Budget 2 Monthly Estimates 3 Income January February March April May June July 4 Commissi 12000 5 Interest 100 12000 100 14000 100 14000 100 16000 100 18500 100 August 20000 18500 100 100 100 Septembe October Novembe December Total 18500 14000 14000 16500 100 100 100 6 Total & Expenses January February March April May June July August Septembe October Novembe December Total 9 Rent 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 Some row 10 Utilities 325 325 325 325 325 325 325 325 325 325 325 325 titles only 11 Advertisin 400 400 400 400 400 400 400 400 400 400 400 400 partially visible 12 Website 0 0 0 0 0 0 500 0 0 0 0 0 13 Printing 200 200 200 200 200 200 200 200 200 200 200 200 14 Office Sup 200 0 0 200 0 0 200 0 0 200 0 O 15 Gas 100 100 100 100 100 100 100 100 100 100 100 100 16 Miscellan 250 250 250 250 250 250 250 250 250 250 250 250 17 Total 18 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 325 Data entered in worksheet Using Data to Support the Decision Making Process 325 30/06/2023 14:28

6: Working with Spreadsheets

326 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 326 6. Calculating Sums and Using Formulas The next step in creating the worksheet is to perform any necessary calcula- tions, such as calculating the column and row totals. In Excel, you can easily perform calculations using a function. A function is a special, predefined formula that provides a shortcut for a commonly used calculation, for exam- ple, SUM or COUNT. When you use functions, Excel performs the calcula- tions for you, which helps to prevent errors and allows you to work more efficiently. DEFINITION Function: A pre-defined set of formulas that work together to calcu- late a commonly-used operation. 7. Sum a Column of Numbers In this workbook, totals are required for each month and each budget item. The first calculation is to determine the total of Commission and Interest income in the month of January (column B). To calculate this value in cell B6, Excel must add, or sum, the numbers in cells B4 and B5. The SUM function adds all the numbers in a range of cells. Why? The Excel SUM function is an efficient means to accomplish this task. Many Excel operations are performed on a range of cells. A range is a series of two or more adjacent cells in a column, row, or rectangular group of cells, notated using the cell address of its upper-left and lower-right corners, such as B5:C10. For example, the group of adjacent cells B4 and B5 is a range. DEFINITION Range: The range of a data set is defined as Range = Largest Value - Smallest Value. After calculating the total income for January, you will use the fill handle to calculate the monthly totals for income and expenses and the yearly total for each budget item. A fill handle is a feature in Excel used to extend (or fill) several numbers, dates, or text to other adjacent cells. The following steps sum the numbers in column B. 30/06/2023 14:28

6: Working with Spreadsheets

. Use the fill handle: STEP 1 . . Move the block arrow to the small square at the bottom right-hand corner of cell B6. Click and hold down the mouse and drag across to the right all the way to cell M6. This copies the SUM formula across all the cells selected. Click cell B6 to make it the active cell. Click the AutoSum button (Home tab | Editing group) to enter a formula in the formula bar and in the active cell (Figure 9-20). What if my screen displays the Sum menu? If you are using a touch screen, you may not have a separate AutoSum button and AutoSum arrow. In this case, select the desired option (Sum) on the AutoSum menu. How does Excel know which cells to sum? Excel automatically selects what it considers to be your choice of the range to sum. When proposing the range, Excel first looks for a range of cells with numbers above the active cell and then to the left. If Excel proposes the wrong range, you can correct it by dragging through the correct range before pressing ENTER. You can also enter the correct range by typing the beginning cell reference, a colon (:), and the ending cell reference. FIGURE 9-20: Use the SUM function to calculate a total AutoSum button AutoSum arrow Sh タマ Number Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format Find & Analy Filter Select Cells Editing Editing group AutoSave Off Book1.xlsx Search (Alt-Q) File Home Insert Page Layout Formulas Data Review View Help X SUM function Wrap Text General in formula bar Menje Ceriter Alignment Clipboard Font SUM SUM(B4:B5) C D E 1 Real Estate Company Budget B4:B5 in parentheses indicates range to be summed G M N 0 P 2 Monthly Estimates 3 Income 4 Commission 5 Interest 6 Total January February March April May 12000 100 June July August 14000 14000 16000 18500 20000 18500 100 7 8 Expenses Proposed range to sum surrounded by moving border, called a marquee 12000 100 =SUM(84:85 SUMinumber1, (number21- SUM function displayed in active cell January February March April May June July 1500 1500 1500 1500 1500 1500 1500 September October November December Total 14000 100 18500 100 14000 100 16500 100 August September October November December Total 1500 1500 1500 1500 1500 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 327 α Using Data to Support the Decision Making Process S 327 30/06/2023 14:28

6: Working with Spreadsheets

STEP 2 Click the Enter button in the formula bar to enter the sum in the active cell. What is the purpose of the arrow next to the AutoSum button on the ribbon? The AutoSum arrow (shown in Figure 9-20) displays a list of functions that allow you to easily determine the average of a range of numbers, the number of items in a selected range, or the maximum or minimum value of a range. STEP 3 Use the fill handle to complete the row B6 to M6. STEP 4 Repeat Steps 1 and 2 to enter the SUM function in cell B17 (Figure 9-21). FIGURE 9-21: The SUM function can be used to cover cell ranges of any size Number Conditional Format as Cell Formatting Table Styles- Styles Insert Delete Format Cells AutoSave Off Book1.xlsx Search (Alt-Q) File Home Insert Page Layout Formulas Data Review View Help Paste Calibri BIU 11 AA Wrap Text General Merge & Center Enter button Clipboard Font Alignment 817 =SUM(B9:816) SUM function H 1 Real Estate Company Budget 2 Monthly Estimates entered in cell B17 N 3 Income 4 Commission 5 Interest 6 Total January February March April May June 12000 100 12100 12000 100 14000 100 14000 100 July August September October November December Total 16000 18500 20000 18500 18500 100 100 100 100 100 14000 100 14000 100 16500 100 Expenses January February March April May June July August September October November December Total 9 Rent 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 10 Utilities 325 325 325 825 325 325 325 825 325 325 325 325 11 Advertising 400 400 400 400 400 400 400 400 400 400 400 400 12 Website 0 0 0 0 0 0 500 0 0 0 0 0 13 Printing 200 200 200 200 200 200 200 200 200 200 200 200 14 Office Supplies 200 0 0 200 0 0 200 0 0 200 0 0 15 Gas 100 100 100 100 100 100 100 100 100 100 100 100 16 Miscellaneous 250 250 250 250 250 250 250 250 250 250 SUM function 17 Total 2975 entered in cell B17 18 328 9 Chapter رة ا Ministry of Education 2024-1446 STEP 5 Use the fill handle to complete the row B17 to M17. 0 Σ DOM G Comments 7 Sort & Find & Filter Select- Editing Ana Da Ana C a R Business Decision Making S1 S2 S3.indb 328 30/06/2023 14:28

6: Working with Spreadsheets

8. Calculate Multiple Totals at the Same Time The next step in building the worksheet is to determine the total income, total expenses, and total for each budget item in column N. To calculate these totals you use the SUM function, similarly to how you used it to total the income and expenses for each month in rows 6 and 17. In this case, however, Excel will determine totals for all of the rows at the same time. Why? By determining multiple totals at the same time, the num- ber of steps to add totals is reduced. The following steps sum multiple totals at once. STEP 1 Click cell N4 to make it the active cell (Figure 9-22). FIGURE 9-22: Using the SUM function across multiple cells AutoSave Off Book1.xlsx Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Calibri 11 Α΄ Α Wrap Text General Paste BIU Merge & Center Clipboard Font E Alignment N4 fx C D E G 1 Real Estate Company Budget 2 Monthly Estimates 3 Income January February March April May June July 4 Commission 12000 12000 5 Interest 6 Total 100 12100 100 12100 14000 100 14100 14000 100 14100 16000 18500 100 100 16100 18600 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 329 .00 Conditional Format as Cell Insert Delete Format 000 Formatting Table Styles Number Styles Cells M Pointer is block plus sign August September October November December Total 20000 18500 100 20100 18500 14000 100 100 100 14000 100 16500 100 18600 18600 14100 14100 16600 + Cell N4 is active cell Using Data to Support the Decision Making Process 329 30/06/2023 14:28

6: Working with Spreadsheets

STEP 2 With the pointer in cell N4 and in the shape of a block plus sign, drag the pointer down to cell N6 to select the range (Figure 9-23). FIGURE 9-23: Use the block plus sign pointer to select the cell range AutoSave Off Book1.xlsx Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Calibri -11 AA Wrap Text General Paste BIU 4 A m Merge & Center % 500-00 Clipboard Font Alignment Number Conditional Format as Cell Formatting Table Styles Styles Insert Delete Cells N4 B C E F G H M N 3 Income 4 Commission 1 Real Estate Company Budget 2 Monthly Estimates January February March 12000 5 Interest 6 Total April May June July 12000 14000 100 12100 100 14000 100 16000 100 18500 100 August September October November December Total 18500 18500 14000 14000 16500 100 20000 100 100 12100 14100 14100 16100 18600 20100 18600 100 100 18600 14100 100 14100 100 16600 7 8 Expenses January February March April May June July August September October November December Total 9 Rent 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 10 Utilities 325 325 325 325 325 325 325 325 325 325 325 325 11 Advertising 400 400 400 400 400 400 400 400 400 400 400 400 12 Website D 0 0 0 D 500 0 0 0 D 0 STEP 3 0 Click the AutoSum button (Home tab | Editing group) to calculate the sums of all three rows (Figure 9-24). How does Excel create unique totals for each row? If each cell in a selected range is adjacent to a row of numbers, Excel assigns the SUM function to each cell when you click the Sum button. FIGURE 9-24: The AutoSum button makes it possible to add a variety of functions to your spreadsheet using fewer clicks AutoSave Off Book1.xlsxv Search (Alt+Q) AutoSum button File Home Insert Page Layout Formulas Data Review View Help Comments SH Calibri - 11 A A Paste BIU A Wrap Text Merge & Center General 44 % Clipboard Fant F Alignment Number Conditional Format as Cell Formatting Table Styles- Styles Insert Delete Format Cells Sort & Find & Filter Select Editing Analyze Data Analysis =SUM(84:M4) C D E G H M N R 1 Real Estate Company Budget 2 Monthly Estimates 3 Income January February March April May June July 4 Commission 12000 12000 5 interest 6 Total 100 12100 100 12100 14000 100 14100 14000 16000 18500 20000 100 100 100 100 14100 16100 18600 20100 330 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 330 18500 14000 100 100 18600 14100 August September October November December Total 18500 100 18600 14000 100 14100 16500 188000 100 1200 16600 189200 SUM function entered in range N4:N6 30/06/2023 14:28

6: Working with Spreadsheets

STEP 4 Repeat Steps 1-3 to select cells N9 to N17 and calculate the sums of the corre- sponding rows (Figure 9-25). FIGURE 9-25: The AutoSum button applies the SUM function to cells with corresponding rows of numeric data AutoSave Book1.xlsx Search (Alt-Q) File Home Insert Page Layout Formulas Data Review View Help AutoSum button Comments Calibri 11 AA Paste BIU Wrap Text Merge & Center General Clipboard Font Alignment Number % 88 Conditional Format as Cell Formatting Table Styles- Styles Insert Delete Format Sort & Find & Filter Select- Cells Editing An =SUM(89:M9) D 9 M N 0 P D 3 Income 4 Commission 5 Interest 6 Total 1 Real Estate Company Budget 2 Monthly Estimates June July January February March April May August September October November December Total 12000 12000 14000 14000 16000 18500 20000 18500 18500 14000 100 100 100 100 100 100 100 12100 12100 14100 14100 16100 18600 20100 18600 100 100 100 18600 14100 14000 100 14100 16500 188000 100 1200 16600 189200 & Expenses January February March April May June July August September October November December Total 9 Rent 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 18000 10 Utilities 325 325 325 325 325 325 325 325 325 325 325 325 3900 11 Advertising 400 400 400 400 400 400 400 400 400 400 400 400 4800 12 Website 0 0 0 0 0 0 500 0 0 0 0 이 500 SUM function entered 13 Printing 200 200 200 200 200 200 200 200 200 200 200 200 2400 in range N9:N17 14 Office Supplies 200 0 0 200 0 0 200 0 0 200 0 이 800 15 Gas 100 100 100 100 100 100 100 100 100 100 100 100 1200 16 Miscellaneous 250 250 250 250 250 250 250 250 250 250 250 250 3000 17 Total 2975 2775 2775 2975 2775 2775 3475 2775 2775 2975 2775 2775 34600 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 331 9. Saving the Project While you are building a worksheet in a workbook, it is important to save the workbook frequently, for the following reasons: The worksheet will be lost if the computer is turned off or you lose electrical power while Excel is open. If you run out of time before completing your workbook, you can finish your worksheet at a future time without starting over. Where should you save the workbook? When saving a workbook, you must decide which storage medium to use: • If you always work on the same computer and have no need to transport your projects to a different location, then your computer's hard drive will suffice as a storage location. It is a good idea, however, to save a backup copy of your proj- ects on a separate medium, such as an external drive, in case the file becomes corrupted or the computer's hard drive fails. The workbooks used in this book are saved to the computer's hard drive. Using Data to Support the Decision Making Process 331 30/06/2023 14:28

6: Working with Spreadsheets

332 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 332 . If you plan to work on your workbooks in various locations or on multiple com- puters or mobile devices, then you should save your workbooks on a portable medium, such as a USB flash drive. Alternatively, you can save your workbooks to an online cloud storage service such as OneDrive. Save a Workbook. The following steps save a workbook in the Documents library on the hard drive using the file name, Real Estate Budget. Why? You have performed many tasks while creating this project and do not want to risk losing the work completed thus far. . Click on "File" on the ribbon Click on "Save As" • Click on "Browse"" . Click on "Documents" . Click on the "File Name" field and type in "Real Estate Budget" . Click "Save" 10. Adding a Chart to the Worksheet Excel includes 17 chart types from which you can choose: column, line, pie, bar, area, XY (scatter), map, stock, surface, radar, tree map, sunburst, histo- gram, box & whisker, waterfall, funnel, and combo. The type of chart you choose depends on the type and quantity of data you have and the message or analysis you want to convey. DEFINITION Chart: Information in the form of a table, graph, or diagram. 30/06/2023 14:28

6: Working with Spreadsheets

Pie charts show the contribution of each piece of data to the whole, or total, of the data. A pie chart can go even further in comparing values across categories by showing each pie piece in comparison with the others. Your budget worksheet should include a pie chart to graphically represent the yearly expense totals for each item in the Real Estate Company's budget. The pie chart shown in Figure 9-26 is on its own sheet in the workbook. The pie chart resides on a separate sheet, called a chart sheet. A chart sheet is a sep- arate sheet in a workbook that contains only a chart, which is linked to the workbook data. DEFINITION Pie chart: A graph of a frequency distribution for a categorical data set. Each category is represented by a slice of the pie, and the area of the slice is proportional to the corresponding frequency or relative fre- quency. FIGURE 9-26: Data in a spreadsheet can be converted into visual tools like charts AutoSave Off File Home Insert Page Layout Real Estate Company Budget.xlsx. Saved Formulas Data Review View Help Search (Alt-Q Chart Design Format Add Chart Quick Element Layout Change Colors Chart Layouts Ready وزارة التعليم Ministry of Education 2024-1446 Chart sheet Chart Styles Chart title Monthly Expenses KM D Comments Share Switch Row Select Change Move Column Data Chart Type Chart Data Type Location Pie chart Chart legend Monthly Expenses Chart Sheet1 Business Decision Making S1 S2 S3.indb 333 36012 Using Data to Support the Decision Making Process 333 30/06/2023 14:28

6: Working with Spreadsheets

11. Add a Pie Chart When you want to see how each part relates to the whole, you use a pie chart. Use the following steps to draw the pie chart for your data. STEP 1 Select the range A9:A16 to identify the range of the category names for the pie chart. While holding down ctrl, select the non-adjacent range N9:N16. . Click Insert on the ribbon to display the Insert tab. Click the "Insert Pie or Doughnut Chart" button (Insert tab | Charts group) to display the Insert Pie or Doughnut Chart gallery (Figure 9-27). FIGURE 9-27: Make sure you've highlighted all the data you want to use before inserting the kind of chart you want to use Insert tab AutoSave Off File Home Insert Page Layout Real Estate Company Budget.xlsx Formulas Search (Alt+Q Data Review View Help Comments Get Ad A PivotTable Recommended Table Illustrations Pivot Tables My Ad "Insert Pie or Doughnut Chart" button mended arts Maps PivotChart 3D Map Line Column Win/ Loss Slicer Timeline Link Comment Text Tables 2-D Pie Tours Sparklines Filters Links Comments N9 =SUM(89:M9) C Pie 1 Real 3-D Pie "Insert Pie or Doughnut 2 3 Income 4 Commission 5 Interest 6 Total January February March April 12,000.00 12,000.00 14,000.00 14,000.00 100.00 100.00 100.00 100.00 12,100.00 12,100.00 14,100.00 14,100.00 May 16,000.00 100.00 16,100.00 Chart" gallery August September October November December 18,500.00 18,500.00 Total Doughnut 14,000.00 14,000.00 16,500.00 100.00 100.00 100.00 100.00 100.00 18,600.00 18,600.00 14,100.00 14,100.00 16,600.00 188,000.00 1.200.00 189,200.00 7 8 Expenses January February March April 9 Rent 1,500.00 1,500.00 1,500.00 1,500.00 May 1,500,00 10 Utilities 325.00 325.00 325.00 325.00 325.00 More Pie Charts... August September October November December 1,500.00 1.500.00 1,500.00 1,500.00 1,500.00 325.00 325.00 325.00 Total 18,000.00 325.00 11 Advertising 400.00 400.00 400.00 400.00 400.00 www.3 433.33 400.00 400.00 400.00 400.00 325.00 400.00 3,900.00 4,800.00 12 Website 500.00 13 Printing 200.00 200.00 200.00 200.00 200.00 Ranges A9:A16 200.00 200.00 200.00 200.00 2,400.00 14 Office Supplies 200.00 200.00 and N9:N16 selected 200.00 800.00 15 Gas 100.00 100.00 100.00 100.00 16 Miscellaneous 17 Total 250.00 2,975.00 250.00 250.00 250.00 100.00 250.00 250.00 250.00 100.00 250.00 100.00 100.00 100.00 100.00 1,200.00 250.00 250.00 250.00 250.00 3,000.00 2,775.00 2,775.00 2,975.00 2,775.00 2,775.00 3,475.00 2,775.00 2,775.00 2,975.00 2,775.00 2,775.00 34,600.00 18 19 Net 9,125.00 9,325.00 11,325.00 11,125.00 13,325.00 15,825.00 16,625.00 15,825.00 15,825.00 11,125.00 11,325.00 13,825.00 154,600.00 20 334 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 334 STEP 2 Click Pie in the 2-D Pie category of the Insert Pie or Doughnut Chart gallery to insert the chart in the worksheet (Figure 9-28). Why have new tabs appeared on the ribbon? The new tabs provide additional options and functionality when you are working with certain objects, such as charts, and only display when you are working with those objects. 30/06/2023 14:28 о P

6: Working with Spreadsheets

وزارة التعليم Ministry of Education 2024-1446 FIGURE 9-28: You can modify the appearance of the chart using the Chart Design or Format tabs on the ribbon AutoSave Off Real Estate Company Budgetxhx v Search (Alt-Q) File Home Insert Page Layout Formulas Data Review View Help Chart Design Format Con Add Chart Quick Element Layout Chart Layouts Chart 2 Change Colors Chart Styles Switch Row Select Change Column Data Move Chart Type New tabs appear Data Type Chart Location H Real Estate Company Budget 2 Monthly Estimates B Income 4 Commission 5 Interest Total January February 12,000.00 12,000.00 14,000.00 100.00 100.00 100.00 12,100.00 12,100.00 14,100.00 March April 14,000.06 100.00 14,100.00 May June July August September October November December 000.00 16,500.00 Chart Title 100.00 100.00 Total 100.00 16,600.00 188,000.00 1,200.00 189,200.00 Expenses January February March April mber December Total 9 Rent 1,500.00 1,500.00 1,500.00 1,500.00 1,500.00 1,500.00 18,000.00 10 Utilities 325.00 325.00 325.00 325.00 325.00 325.00 3,900.00 11 Advertising 400.00 400.00 400.00 400.00 400.00 400.00 4,600.00 12 Website 500.00 13 Printing 200.00 200.00 200.00 200.00 200.00 200.00 2,400.00 14 Office Supplies 200.00 200.00 800.00 15 Gas 100.00 100.00 100.00 100.00 100.00 16 Miscellaneous 250.00 250.00 250.00 250.00 Rent Utilities Advertising Webste Total 2.975.00 2,775.00 2,775.00 2,975.00 Printing Office Supplies Gas Miscellaneous 100.00 1,200.00 250.00 250.00 3,000.00 2.775.00 2,775.00 34,600.00 18 19 Net 9,125.00 9,325.00 11,325.00 11,125.00 13,325.00 15,825.00 16,625.00 15,825.00 15,825.00 11,125.00 11,325.00 13,825.00 154,600.00 20 Business Decision Making S1 S2 S3.indb 335 . STEP 3 Click the chart title to select it. Click and drag to select all the text in the chart title. Type Monthly Expenses to specify the title. Click a blank area of the chart to deselect the chart title (Figure 9-29). FIGURE 9-29: Click on the chart title to make any changes .00 .00 00 888 00 роф 00 May June July August September October No Monthly Expenses Chart title .00 .00 .00 88888 Rent 00 Printing Utilities Advertising Website ■Miscellaneous $ Office Supplies Gas Using Data to Support the Decision Making Process 335 30/06/2023 14:28

6: Working with Spreadsheets

336 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 336 To create the Chart sheet: Click anywhere in the chart box. Click on "Chart Design" on the ribbon. Click on "Move Chart" (top right). Select "New Sheet"; click OK. To rename your newly created Chart 1 sheet: Right click on the tab containing the word Chart 1. Scroll to "rename". . Over type with: Monthly Expenses Chart. 12. Add a Column or Bar Chart When you want to compare the total expenses from each month, use a col- umn or bar chart. Use the following steps to draw a column or bar chart for your data. STEP 1 Select the range B8:M8 to identify the range of the category names for the column or bar chart. While holding down ctrl, select the non-adjacent range B17:M17. . Click Insert on the ribbon to display the Insert tab. Click the "Insert Column or Bar Chart" button (Insert tab | Charts group) to dis- play the Insert Column or Bar Chart gallery (Figure 9-30). 30/06/2023 14:28

6: Working with Spreadsheets

Insert tab FIGURE 9-30: Highlight only the data you want to use for a bar chart AutoSave Real Estate Company Budget.xlsx Search (Alt-Q) File Home Insert Page Layout Formu View Help Kathryn Morrell KM Comments "Insert Column or Bar Chart" 日時 Get button de 2-D Column Pivot Table Recommended Table Pivot Tables Illustrations My Add-ins mended Charts 3D Map Line Column Win Loss Slicer Timeline Link Comment 시 Text Sym Tables B17 A Clustered column Tours Sparklines Filters Links Comments =SUM(B9:B16 3-D Column D E F 1 Real ba "Insert Column M N or Bar Chart" gallery 0 a 23 2 Income Commission 5 Interest 6 Total January February March 12,000.00 12,000.00 14,000.00 100.00 100.00 100.00 12,100.00 12,100.00 14,100.00 April 14,000.00 100.00 14,100.00 May September October November December 2-D Bar 16,000.00 100.00 16,100.00 Total D 18,500.00 D 14,000.00 14,000.00 16,500.00 100.00 100.00 100.00 100.00 18,600.00 14,100.00 14,100.00 16,600.00 188,000.00 1,200.00 189,200.00 7 8 Expenses January February March 9 Rent 1,500.00 1,500.00 1,500.00 10 Utilities 325.00 325.00 325.00 11 Advertising 400.00 400.00 400.00 April 1,500.00 325.00 400.00 May 1,500.00 325.00 400.00 3-D Bar September October November December D 325.00 1,500.00 1,500.00 1,500.00 1,500.00 325.00 Total 18,000.00 325.00 400.00 400.00 400.00 325.00 400.00 12 Website 13 Printing 200.00 200.00 200.00 200.00 200.00 14 Office Supplies 200.00 200.00 Ranges B8:M8 and B17:M17 selected 200.00 200.00 200.00 200.00 3,900.00 4,800.00 500.00 2,400.00 200.00 800.00 15 Gas 100.00 100.00 100.00 16 Miscellaneous 17 Total 250.00 2,975.00 250.00 250.00 100.00 250.00 100.00 250.00 100.00 100.00 100.00 100.00 100.00 1,200.00 250.00 250.00 250.00 250.00 250.00 250.00 250.00 3,000.00 2,775.00 2,775.00 2,975.00 2,775.00 2,775.00 3,475.00 2,775,00 2,775.00 2,975.00 2,775.00 2,775.00 34,600.00 18 19 Net 9,125.00 9,325.00 11,325.00 11,125.00 13,325.00 15,825.00 16,625.00 15,825.00 15,825.00 11,125,00 11,325.00 13,825.00 154,600.00 20 21 وزارة التعليم Ministry of Education 2024-1446 STEP 1 Click Clustered column in the 2-D Column category of the Insert Bar or Column Chart gallery to insert the chart in the worksheet (Figure 9-31). FIGURE 9-31: The bar chart allows the user to easily compare expenses figures each month AutoSave Off File Home Insert Page Layout Real Estate Company Budget.xlsx Saved Formulas Data Review View Help Search (Alt+Q) Chart Design: Format Kathryn Morrell KM Comme Add Chart Quick Element Layout Change Colors Chart Layouts Chart 6 X 3 B D 1227 Chart Styles 1 2 3 5 Interest 6 Total 7 8 Expanses 9 Rent January February 3,000.00 4,000,00 3,500.00 Monthly Estimates Income January 4 Commission 12,000.00 100.00 12,100.00 February March 12,000.00 Q April May June July 100.00 12,100.00 Chart Title Real Estate Company Budget August September October November Decen 1.00 14,000.00 14,000.00 Switch Row Select Column Data New tabs appear Data Change Chart Type Type Move Chart Location H M Format Chart Ar Chart Options Text O 10 Utilities 1,500.00 1,500.00 325.00 2,500.00 325.00 11 Advertising 400.00 2,000.00 400.00 12 Website 1,500.00 13 Printing 200.00 14 Office Supplies 200.00 1,000.00 200.00 500.00 15 Gas 100.00 100.00 0 16 Miscellaneous 250.00 250.00 17 Total 2,975.00 2,775.00 January 18 19 Net 20 21 9.125.00 9,125.00 11.325.00 11,125.00 13,325.00 February March Aprit May June July August 15,825.00 September October .00 100.00 100.00 11 1.00 14,100.00 14,100.00 16,50 10 16,60 >Fill › Border P 325.00 325.00 400.00 400.00 ber October November Decen 1,500.00 1,500.00 1,500.00 1,50 325.00 32 400.00 40 November December 200.00 200.00 200.00 20 200.00 100.00 100.00 100.00 10 10 250.00 2,775.00 250.00 2,975.00 2,775.00 2,71 250.00 25 16,625.00 15,825.00 15,125.00 11,125.00 11.325,00 11,83 Business Decision Making S1 S2 S3.indb 337 Using Data to Support the Decision Making Process 337 30/06/2023 14:28

6: Working with Spreadsheets

338 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 338 STEP 3 Click the chart title to select it. . Click and drag to select all the text in the chart title. Type Expenses by Month to specify the title. . Click a blank area of the chart to deselect the chart title (Figure 9-32). FIGURE 9-32: Changing the chart title May June July 0.00 August September October No 0.00 Expenses by Month chart title 0.00 4,000.00 3,500.00 3,000.00 .00 2,500.00 .00 2,000.00 .000 1,500.00 1,000.00 0.00 500.00 0.00 0.00 0 0.00 5.00 January February 500 March April May June July August September 15:00 October November December To create the Chart sheet: Click anywhere in the chart box. • Click on "Chart Design" on the ribbon. Click on "Move Chart" (top right). Select "New Sheet"; click OK. To rename your newly created Chart 1 sheet: . Right click on the tab containing the word "Chart 1". Scroll to "rename". Over type with: Expenses by Month Chart. 30/06/2023 14:28

6: Working with Spreadsheets

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 339 13. Calculating Average, Maximum, and Minimum Values As you learned earlier in this lesson, the AutoSum list lets you calculate not only sums but also the average, the number of items, or the maximum or minimum value of a range. You can calculate these using three additional functions: AVERAGE, MAX, and MIN. The AVERAGE function calculates the average value in a range of cells, the MAX function calculates the maximum value in a range of cells, and the MIN function calculates the minimum value in a range of cells. Table 4-4 shows examples of each of these functions. DEFINITION AutoSum: Function in a spreadsheet that lets you calculate sums, the average, the number of items, or the maximum or minimum value of a range. TABLE 9-4 AVERAGE, MAX, and MIN functions Function =AVERAGE(H1:H5) =MAX(H1:H5) =MIN(H1:H5) Result Determines the average of the values in cells H1, H2, H3, H4, and H5 Determines the maximum value entered in cells H1, H2, H3, H4, and H5 Determines the minimum value entered in cells H1, H2, H3, H4, and H5 Using Data to Support the Decision Making Process 339 30/06/2023 14:28

6: Working with Spreadsheets

YOU TRY IT Use the functions (shown previously) to calculate the Average, Minimum, and Maximum values for each of the Income and Expense categories. To do this, create three new columns (in O, P, and Q). Add column titles (e.g., "Average", "Min", "Max") in cells 03, P3, Q3 and 08, P8, Q8. Remember to adjust the range of data in the functions to match your worksheet values (B:M). Your new columns should show the Average, Min, and Max values for the year. REVIEW QUESTIONS 1. The first spreadsheet developed for the personal computer was called: a. Excel b. WordStar c. dBase d. Visicalc 2. A special, predefined formula that provides a shortcut for a commonly used calculation is a: a. table b. function c. range d. cell 3. When you want to see how each part relates to the whole, you use a: a. map b. scatter diagram c. pie chart d. bar chart 340 9 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 340 30/06/2023 14:28

6: Working with Spreadsheets