Calculate the subtotals of the turnover, for each change in Geographical Direction within the cell range A3:C19.
Replace the formulas of the cell range K5:K9 with the values of the same cells.
Create two scenarios using the following data. The changing cells will be D7:D9. The first one, named Optimistic, will display:
value 1323 in the cell D7
value 543 in the cell D8
value 987 in the cell D9.
The second, named Pessimistic, will display:
value 989 in the cell D7
value 243 in the cell D8
value 890 in the cell D9.
On a new worksheet create a Pivot Table Report based on the data of the cell range B5:F26. Display the Product field in Column Labels, the Salesman field in Row Labels and the Color and Quantity fields in the Data Item fields.
Navigate to report worksheet and group the dates which appear in the pivot table by quarters.
Apply the Report 9 formatting in the Pivot Table Report.
Insert the data of the out1.txt file located in the ÉL-ates\Excel folder on your Desktop in cell Â10. (Delimiter is the semicolon ; ).
Calculate subtotals in the column Turnover at each change in Orientation. Display the Summary below data.
Use a One-Variable Data Table to calculate in the cell range B2:B25 the VAT of the values appearing in the cell range A2:A25. The formula is provided in cell E3 and VAT percentage is displayed in the cell Å1.
Use a Two-Variable Data Table to calculate in the cell range D7:J30 the VAT of the values appearing in the cell range C7:C30. VAT percentages are provided in the cell range D6:J6. The appropriate formula is provided in the cell B3.
Navigate to the SALES worksheet located in the IL-ates1525 workbook of the IL-ates\Excel folder on your desktop. Copy and paste the data included in the cell range Â20:J20 of the SALES worksheet in the cell range A1:É1 of the already opened workbook as link.
Change the linked archive from IL-ates1526_1 to IL-ates1526_2. Both files are located in the IL-ates\Excel file on the desktop.
Create two Scenarios with the following data:
1) Name: Min, changing cells: C5:C8, values: 5, 6, 10, 8 2) Name: Max, changing cells: C5:C8, values: 11, 12, 15, 10.
Display the scenario Max.
Create a scenario summary using the default values.
Remove the SubTotals.
Apply subtotals in the cell range A1:D201.
Create a table in the cell range C3:H13 to multiply the values displayed in the cell range B3:B13 by the quantities displayed in the cell range C2:H2.
Create a pivot table report beginning from the cell G5. The report will derive data from the cell range A1:D201 and will display the field Order date as row and the field Full name as column. Set the Sum of the field Quantity as data item.
Enter the data of the out1.txt file located in the IL-ates\Excel folder on your Desktop to a new workbook, using semicolon (;) as delimiter. Save the workbook as final.xls to the same folder.
Navigate to the pivot table and apply grouping by Months on the column OrderDate.
Navigate to the pivot table and apply the options required to display the total of quantities (count) instead of the Sum.
Remove the field Description from the pivot table and apply the Currency format on the Values.
Modify the worksheet to display the product's total price instead of Quantity in the pivot table (Do not use a calculated field, and do not move the pivot table).
Apply the adjustments required in the subtotal structures so that only the total quantity for each client and the Grand total are displayed.
Ιn the Sheet1 worksheet, insert the following entries:
Lambropoulos 75.00€ 109.75€ Goutoudis 45.00€ 65.85€
and update the pivot table in the Sheet2 worksheet.
On the Sheet1 worksheet delete the scenario called Scenario1.
Apply a validation rule in the cell B3 to allow only decimal numbers greater than 0. Insert the text Only numbers as Error Message.
Make sure we navigate to the address www.infolearn.gr by clicking on the text appearing in the cell A1. Then delete the hyperlink of the cell A2 without deleting the text.
Activate Track Changes in the document and Highlight changes for all users and all dates. Save the document.
Reject all changes effectuated in the document by all users.
Merge the active workbook with the merge file located in the IL-ates\Excel folder on your Desktop.