Question

spreadsheet and data base managment

File Edit Insert Table Format Arrange View Share Window Help Target's Monthly Stock Prices T - Chart Text Shape Media 125 zoom Insert Table Questions Data Finish the following technical steps in the EXCEL workboek and upload your file for grading 1. Add a new worksheet in your EXCEL workbook, rename this worksheet as Documentation and then input your full name in the Al cell, and input the test date in the A2 cell (10 points) 2. In the Data worksheet, create a table named DataTable. This table should include the full date in the D worksheet (10 ) 3. Make a copy of the Data worksheet and rename the copied worksheets Price Stats. In the cell to generate the average price, maximum price, minimum price and the price range respectively calculated from the Close Price column (field) by using the built-in syntax in EXCEL (15 4. Make a copy of the Data worksheet and rename the copied worksheet as Sorting Sort the Date field column) in the Data Table from the oldest date to the newest date (10 points) S. Make a copy of the Data worksheet based on Step 2nd rename the copied worksheets Price Move In the column, create a new field named Daily Dynamics. Then output of the Close Price Open Price and of the Close Price <Open Price (25 points) 6. Create a Pivot Table in a separate worksheet based on the DataTable in the PivoTable display the Date and Close Price fields Group the date format of ddyyyy into the date format of yyyy for the Date column, display the yearly average price in the Close Price field and rename this field as Ave of Close Price Generate a Line Chart Pithart by using the Date in year) as the X-asis and the Age of Close Price as the Yaxi 30 points) 7. Save your file in a name of Exam 1. Your First Name - Your Last Name to your computers and drive desktop area or your USB flash drive and then ch the file and we to the test site of Exam I in Blackboard for grading MacBook

Answer 1

Please find the answers with appropriate screenshots attached for 1, 2, 3 and 4.

Con - 9 X New Microsoft Office Excel Worksheet - Microsoft Excel Team Page Layout Formulas Data Review View = = = Calibri B I - 11 D- - Home Insert * Cut Lg Copy Paste Format Painter Clipboard E23 A B - A A -A Wrap Text Merge & Center - General - % *- } AutoSum U E E E , $8.99 Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Filter Editing Find & Select Font Alignment Number Cells C D | | Ε K L M N O P Q R S T Insert... Delete Rename Move or Copy... View Code Protect Sheet... Tab Color 23 24 Hide Unhide... Select All Sheets Sheet2 Sheet3 25 » Sheet Ready DO 100% 0 O 08:07 A Type here to search . ENG 17-10-2019 7

1) create excel ->shift+F11 (to create new wroksheet) -> right click on Sheet 1 -> rename-> change name to Datasheet.

In documentation , add full name in A1 and test data in A2.

Con = - 9 X New Microsoft Office Excel Worksheet - Microsoft Excel Team Insert Page Layout Formulas Data Review View Home Cut = = = General - 1 D - } Autosum - 47 Eg Copy D Wrap Text 1 Merge & Center - Alignment 0 Cell Styles Insert Delete Format Conditional Format Formatting as Table Styles 2 Clear Calibri - 11 - A A BI U DA Font for B C Test Date Format Painter Clipboard A4 Find & Select Sort & Filter Editing Number Cells A. D E F G H I J K L M N O P Q R 1 Full Name Documentation Sheet2 Sheet3 ► Ready + OU 100% 0 08:08 . I ENG 12 17-10-2019 Type here to search A 0

New Microsoft Office Excel Worksheet - Microsoft Excel - X Cn0A) Home Insert Page Layout Formulas Data Review View Team 27 28 T ola XX O 3 A A 12 PivotTable Table Picture Clip Art Shapes SmartArt Column Line Pie - Bar - Charts Area - Scatter Other - Charts Hyperlink Text Box Header WordArt Signature Object Symbol & Footer Line Text Tables Illustrations Links A B C D E F G H I J K L M N O P Q R S R Documentation Data N Ready DO 100% 0 ^ o. la ENG 17-10-2019 O 08:19 op Type here to search

2) select the number of cells you need to create the table. -> Insert -> Table -> ok ->table is created.

C. = - Home New Microsoft Office Excel Worksheet - Microsoft Excel Team Insert Page Layout Formulas Data Review View 31 T 3 X OBUKA A 2012 PivotTable Table Picture Clip Shapes SmartArt Column Line Pie Bar Area Scatter Other Charts Hyperlink Text Box Header WordArt Signature Object Symbol & Footer Line Text Tables Illustrations Charts Links A1 C D н M N O P. X Create Table ? Where is the data for your table? =$1:$5 My table has headers OK Cancel R Documentation Data N Point OU 100% + 08:21 17-10-2019 O Type here to search 4 . ( ENG-

C - 9 X O Home Table Name: ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Insert Page Layout Formulas Data Review View Team Design .Summarize with PivotTable Properties Header Row First Column Remove Duplicates Open in Browser Total Row Last Column Export Refresh Convert to Range Unlink ✓ Banded Rows O Banded Columns Tools External Table Data Table Style Options fx Column1 Miili IIIII IIIII TUTTI IIIII IIIII IIIII ILIIT III III III DII IT111 Table4 Resize Table Properties A1 IIIII Table Styles M 1 Column 1 Column 2 Column3 Column 4 Columns Column Column Columns Column Column 10 column11 column12 Column13 Column14 Column15 Column 16 colum Documentation Data Ready Count: 16384 BOU 100% g . ENG u 08:22 17-10-2019 17 . O Type here to search

3) Right click on Data datasheet -> or move or copy -> check on create the copy and select documention-> data(2) is created.

right click on Sheet 1 -> rename-> change name to PriceStats.

C - 9 X O Home Table Name: 11 ili TO IIIII DUIT IIIII IIIII III III III III ILIIT ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Insert Page Layout Formulas Data Review View Team Design .Summarize with PivotTable Properties Header Row First Column Remove Duplicates Open in Browser Total Row Last Column Export Refresh Convert to Range Unlink ✓ Banded Rows O Banded Columns Tools External Table Data Table Style Options fix Columni B C D E F G H I Column 2 Column3 Column 4 Columns Column6 Column Columns Columng III III DII IIIII L Table4 Resize Table Properties A1 A Column1 Table Styles J Column10 K Column11 L Column12 M Column13 N Column14 O Column15 P Column 16 Colum GESPEELGOED BEE 00 van WN- Insert... Delete Rename Move or Copy... View Code Protect Sheet... Tab Color Hide Unhide... Select All Sheets Documentation Daca Ready Count: 16384 BOU 100% g . ENG u 08:24 17-10-2019 17 0 . O Type here to search

C - 1 x O Home Table Name: 11 ili TO IIIII DUIT IIIII IIIII III III III III ILIIT ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Insert Page Layout Formulas Data Review View Team Design .Summarize with PivotTable Properties Header Row First Column Remove Duplicates Open in Browser Total Row Last Column Export Refresh Convert to Range Unlink ✓ Banded Rows O Banded Columns Tools External Table Data Table Style Options fix Columni B C D E F G H I Column 2 Column3 Column 4 Columns Column6 Column Columns Columng III III DII IIIII L Table4 Resize Table Properties A1 A Column1 Table Styles J Column10 K Column11 L Column12 M Column13 N Column14 O Column15 P Column 16 Colum x Move or Copy ? Move selected sheets To book: New Microsoft Office Excel Worksheet.xlsx Before sheet: Documentation Data (move to end) Create a copy OK Cancel Documentation Data K Ready Count: 16384 BOU 100% u 08:25 17-10-2019 0 O A . Type here to search ENG

C - 9 X O Home Table Name: 11 ili TO IIIII DUIT IIIII IIIII III III III III ILIIT ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Insert Page Layout Formulas Data Review View Team Design .Summarize with PivotTable Properties Header Row First Column Remove Duplicates Open in Browser Total Row Last Column Export Refresh Convert to Range Unlink ✓ Banded Rows O Banded Columns Tools External Table Data Table Style Options fix Columni B C D E F G H I Column 2 Column3 Column 4 Columns Column6 Column Columns Columng III III DII IIIII L Table46 Resize Table Properties A1 A Column1 Table Styles J Column10 K Column11 L Column12 M Column13 N Column14 O Column15 P Column 16 Colum GESPEELGOED BEE 00 van WN- Data (2) Documentation Data Ready Count: 16384 BOU 100% o A . ENG u 08:27 17-10-2019 2 0 . O Type here to search

For average price :In K2 CELL :  =AVERAGE(A2:J2)

For Maximum price : In K3 CELL : =MAX(A3:J3)

For Minimum price : In K4 CELL :   =MIN(A4:J4)

For price range : In K5 CELL : =MAX(A5:J5) - MIN(A5:J5)

C - 9 X In ili TI IIIII DUIT IIIII IIIII III III III ILIIT III III III DII IT111 IIIII L O ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Home Insert Page Layout Formulas Data Review View Team Design Table Name: .Summarize with PivotTable Properties Header Row First Column Table46 Remove Duplicates Open in Browser Total Row Last Column Export Refresh Resize Table Convert to Range - Unlink ✓ Banded Rows O Banded Columns Properties Tools External Table Data Table Style Options K2 foc =AVERAGE(A2:32) A B C D E F G H I 1 Column 1 Column 2 Column3 Column 4 Columns Column6 Column Columns Columns 1 2 3 4 6 7 8 9 Table Styles L Column 10 Column11 Column12 10 5.5 M Column13 N Column14 O Column15 P Column16 Colum 0 0 0 0 24 25 Her Ready Price Stats Documentation Data BO 100% u 08:42 17-10-2019 12 O AO. Type here to search . ENG

C - 9 X In ili TI IIIII DUIT IIIII IIIII III III ILIIT ILIIT III III III DII IT111 IIIII L Table Styles O ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Home Insert Page Layout Formulas Data Review View Team Design Table Name: .Summarize with PivotTable Properties Header Row First Column Table46 Remove Duplicates Open in Browser Total Row Last Column Export Refresh Resize Table Convert to Range Unlink 7 Banded Rows Banded Columns Properties Tools External Table Data Table Style Options K3 f =MIN(A3,33) A B C D E F G H I 1 Column 1 Column2 Column3 Column 4 Columns Column6 Column Columns Column 1 3 6 7 8 9 5 6 7 8 1 7 6 7 8 K L Column 10 Column11 Column12 10 5.5 M Column13 N Column14 O Column15 P Column 16 Colum w 0000 9 9 10 10 w Price Stats Documentation Data ► Ready BO 100% uc ENG u 08:43 17-10-2019 12 - O Type here to search AO.

C - 9 X In ili TI IIIII DUIT IIIII IIIII III III ILIIT ILIIT III III III DII IT111 IIIII L O ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Home Insert Page Layout Formulas Data Review View Team Design Table Name: Summarize with Pivot Table Properties Header Row First Column Table46 Remove Duplicates Open in Browser Total Row Last Column Export Refresh Resize Table Convert to Range - Unlink 7 Banded Rows Banded Columns Properties Tools External Table Data Table Style Options Table Styles K4 F =MAX(A4,34) A B C E F G H I K L M 1 Column 1 Column 2 Column3 Column 4 Columns Column6 Column Columns Column Column 10 Column11 Column12 Column13 1 2 3 6 7 8 9 10 5.5 5 6 7 8 9101 1 7 9 10 10 9 10 N Column14 O Column15 P Column16 Colum w 0000 w Price Stats Documentation Data ► Ready BO 100% u 08:44 AO. ( ENG 12 17-10-2019 0 . O Type here to search

C - 9 X In ili TI IIIII DUIT IIIII IIIII III III ILIIT ILIIT III III III DII IT111 IIIII L Table Styles O ) New Microsoft Office Excel Worksheet - Microsoft Excel Table Tools Home Insert Page Layout Formulas Data Review View Team Design Table Name: .Summarize with PivotTable Properties Header Row First Column Table46 Remove Duplicates Open in Browser Total Row Last Column Export Refresh Resize Table Convert to Range - Unlink 7 Banded Rows O Banded Columns Properties Tools External Table Data Table Style Options K5 f =MAX(A5:45) - MIN(A5:5) A B C D E F G H I 1 Column 1 Column 2 Column3 Column 4 Columns Column6 Column Columns Column i 3 6 7 8 9 5 6 7 8 i 7 6 7 8 M Column13 N Column14 O Column15 P Column16 Colum L Column 10 Column11 Column12 10 5.5 9 10 1 S 10 10 9 9 OL01 w 0000 w Price Stats Documentation Data ► Ready BO 100% u 08:47 17-10-2019 7 0 O PAD. . Type here to search ENG

4)

Right click on Data datasheet -> or move or copy -> check on create the copy and select priceStat-> priceStat(2) is created.

right click on it -> rename-> change name to Sorting.

click on the small dropdown arrow for the column for the date column -> select sort smallest to largest

- 9 X - Σ AutoSum" Α Eg Copy Paste Sort & Find & Filter - Select Editing Con = New Microsoft Office Excel Worksheet - Microsoft Excel Home Insert Page Layout Formulas Data Review View Team Cut Calibri - 11 - A A = = = D Wrap Text General BI U DA Conditional Format 1 Merge & Center - Cell Format Painter Insert Delete Format Formatting as Table Styles 2 Clear Clipboard Font Alignment Number Styles Cells G24 Fax A B C D E F G H I J K L M N 1 Column 1 Column 2 Column3 Column4 Columns Columns Column Columns Column Column 10 Column11 1 Column12 Column13 Column14 i 6 7 8 A Sort Smallest to Largest 1 2 3 5 6 7 A Sort Largest to Smallest Sort by Color 1 6 7 Clear Filter From "Column11" Filter by Color Number Filters O Column15 P Column16 Colum 00000000 w w ... .- (Select All) 1 5.5 10 (Blanks) . OK Cancel Price Stats Documentation Data ► Ready BO 100% u 08:48 17-10-2019 0 O A . Type here to search D . ENG