Icd PDF

ICDL / ECDL 2.0 Advanced Spreadsheets MS Excel 2016 Diag Eng - R... https://icdlasia.psionline.com/phoenix/s/lw/4571586/result.html?endOfT...
ICDL / ECDL 2.0 Advanced Spreadsheets MS Excel 2016 Diag Eng

Candidate Vangara Harish (IN0000004204)
Invigilator
Knowledge ECDL/ICDL Advanced 2.0 Diagnostics (ENGLISH)
Area
Test Area Spreadsheets
Type Diagnostic
Number of 46
questions
Date 2019-08-12 14:07
Duration 49:16
Vendor EnlightKS
European Computer Driving Licence, ECDL, International Computer Driving

Licence, ICDL, e-Citizen and related logos are all trademarks of The European
Computer Driving Licence Foundation Limited (“ECDL Foundation”).
PSI is an entity independent of ECDL Foundation and is not associated with

ECDL Foundation in any manner. This diagnostic has been developed by PSI
and has been approved by ECDL Foundation for use by candidates preparing to sit the Certification test. This is not a test;
candidates will not receive a certificate or results when availing of this product.
Candidates using this diagnostic must be registered with a National Operator in order to sit ICDL / ECDL 2.0 Advanced
Spreadsheets MS Excel 2016 Diag Eng. Without a valid registration, ICDL / ECDL 2.0 Advanced Spreadsheets MS Excel
2016 Diag Eng cannot be undertaken and no certificate, nor any other form of recognition, can be given to a candidate.
Registration should be undertaken with the National Operator or at any Approved Test Centre.
This test is finished and the result has been saved
Your score
Total result 13%
Result by Category
Formatting 66%
Functions and Formulas 20%
Charts 0%
Analysis 0%
Validating and Auditing 0%
Enhancing Productivity 16%
Collaborative Editing 0%
Answer Listing
1 of 19 8/12/2019, 5:37 PM
Item Text Answer Feedback Result
Formatting
The file 2011 forecast is open. Open a Suggested solution: Unanswered

second file called rainbow from \. Right-click the worksheet tab Charting
Copy the charting worksheet from and choose Move or Copy… from the
the rainbow file so the copied menu.
worksheet becomes the last
In the Move or Copy window:
worksheet in the 2011 forecast
workbook. Check the box next to Create a copy.
In the drop-down list below To book:,
select the 2011 forecast workbook.
In the drop-down list below Before
sheet:, select move to end.
Click OK.
On the forecast 2011 worksheet Suggested solution: Unanswered

select the cell range A4:B8 and apply Select the cell range A4:B8.
the style Table Style Dark 2 to this cell
Click the Format as table button on the
range.
Home tab.
A number of styles are displayed.
Click the style Table Style Dark 2, located
below the heading Dark at the bottom of
the styles window.
The Format as Table window is displayed.
Below Where is the data for your table?,
verify the cell range A4:B8.
Click OK.
Functions and Formulas
2 of 19 8/12/2019, 5:37 PM
On the sales worksheet: A. Add a Suggested solution: 10 of 20

function in cell D2 to round the value A. Select cell D2 and click the Insert
in cell C2 up to zero decimal places.
function button on the Formulas tab.
Copy the function in cell D2 to the
Highlight the ROUNDUP function and
cell range D3:D18. B. Add a function
in cell E2 that will rank the number in click OK.
cell C2 from highest to lowest, In the Function Arguments window,
relative to the cell range named sales. enter C2 in the Number box and 0 in the
Copy the function in cell E2 to the Num_digits box.
cell range E3:E18. Click OK.
Select cell D2, click the fill handle and
drag down to cell D18.

Expected function =ROUNDUP(C2,0)
On the staff worksheet add an IF Suggested solution: 0 of 10

function in cell G3 with the following Select cell G3 and enter the following:
conditions:If cell E3 is equal to cell K2
=IF(AND(E3=$K$2,F3>=$N$2),"Yes","No")
and cell F3 is greater than or equal to
cell N2, then cell G3 displays Yes or Select cell G3, click the fill handle and drag
otherwise displays No.Copy the down to cell G38.
function in cell G3 to the cell range
G4:G38.
Expected function
=IF(AND(E3=$K$2,F3>=$N$2),"Yes","No")
3 of 19 8/12/2019, 5:37 PM
On the analysis worksheet add a Suggested solution: Unanswered

lookup function in cell C5 to show Select cell C5 and click the Insert
the name of the chief salesperson
that deals with the region in cell B5
Highlight the LOOKUP function and click
(West). Copy the function in cell C5
to the cell range C6:C31. OK.
In the Select Arguments window,
highlight ‘lookup_value_array’ and click
On the analysis worksheet add a Suggested solution: Unanswered

function in cell J5 that will display the Select cell J5 and click the Insert function
first five characters of the contents of button on the Formulas tab.
cell G5. Copy the function in cell J5 to
the cell range J6:J8. Highlight the LEFT function and click OK.
In the Function Arguments window,
enter G5 in the Text box and 5 in the
Num_chars box.
Click OK.
Select cell J5, click the fill handle and drag
down to cell J8.

Expected function =LEFT(G5,5)
4 of 19 8/12/2019, 5:37 PM
On the analysis worksheet enter an Suggested solution: Unanswered

appropriate criterion within the cell Select cell B2 and enter North.
range A1:D2 and a database function
Select cell D33 and click the Insert
in cell D33 to calculate the maximum
sales for the North region of Europe.
Highlight the DMAX function and click
OK.
In the Function Arguments window,
enter A4:D31 in the Database box, "Sales
(€000)" in the Field box and A1:D2 in the
Criteria box..
Click OK.


enter a sum function in cell E2 with a
Click cell E2.
3-D reference to sum cells E2 on the
west, east, north and south Type = (equal sign), enter the function name
SUM, and then type an opening parenthesis.
worksheets.
Click the West tab.
Hold down the SHIFT key and click the South
tab.
Select cell E2 so it is included in the fomula.
Type an ending parenthesis.
Press ENTER.

Expected function
=SUM('west'!E2,'east'!E2,'north'!E2,'south'!E2)
On the new office worksheet add a Suggested solution: Unanswered

function in cell C13 to calculate the
Select the cell C13 and click the Insert
monthly payments to be made if function button on the Formulas tab.
Rainbow invest in new premises in
Highlight the PMT function and click OK.
Rome and take out the loan detailed
on the worksheet. In the Select Arguments window:
Enter C9/12 in the Rate box, C11*12 in
the Nper box and C7 or -C7 in the Pv
box.
Click OK.

Expected function
=PMT(C9/12,C11*12,C7) OR
=PMT(C9/12,C11*12,-C7)
5 of 19 8/12/2019, 5:37 PM
On the overheads worksheet enter a Suggested solution: Unanswered

formula in cell D23 using only mixed Select the cell D23.
cell referencing that will calculate
Enter the formula =$C23+($C23*D$21)
increased salary costs by multiplying
cell C23 by cell D21 and adding to Press ENTER.
original amount in cell C23. Copy the Select cell D23, click the fill handle and
formula in cell D23 to complete the drag to cell I23.
calculations in the cell range E23:I23.
Expected formula =$C23+($C23*D$21)
Charts
On the charting worksheet, on the Suggested solution: Unanswered

chart beginning at cell A7: A. Delete
A. Right-click one of the Total Profit
the data series for Total Profit Margin Margin (€000) columns and choose
(€000) without editing the contents Delete from the menu.
of the cell range A5:G5. B. Plot the

Total Overheads (€000) data series on
a secondary axis. B. Right-click one of the Total Overheads
(€000) columns and choose Format Data
Series from the menu.
In the Format Data Series pane, choose
Series Options and click the option
Secondary Axis.
chart beginning at cell A7: A. Change A. Right-click one of the Total Overheads
the series chart type for the Total
(€000) columns and choose Change
Overheads (€000) data series only to
Series Chart Type… from the menu.
a Line chart. B. Set the minimum
value for the values of the vertical In the Change Chart Type window,
choose the Line option in the Chart Type
(value) axis to be 0, the maximum
value to be 27500 and the major unit drop-down list for the serie Total
value to be 2500. Overheads (€000), and then click one of
the Line charts.
Click OK.

B. Right-click the vertical axis on the left
hand side of the chart and choose Format
Axis from the menu.
In the Format Axis pane, choose Axis
Options.
Enter the values for the Minimum,

Maximum and Major unit options.
6 of 19 8/12/2019, 5:37 PM

chart beginning at cell A35: A. Format A. Right-click one of the Budgeted Sales
the Budgeted Sales data series to
columns and choose Format Data Series
display the jet image located in \. B.
from the menu.
Format the position of the Budgeted
Sales data labels to appear at the
outside end.

chart beginning at cell A35: A. Apply
A. Right-click the vertical axis and choose
a setting to the chart so that the
Format Axis….
values on the vertical (value) axis are
displayed as units in millions with a In the Format Axis pane, choose Axis
Options.
label. Do not edit the source data for
the chart. B. Change the chart type To the right of Display units:, choose
for the Budgeted Overheads data Millions in the drop-down list.
series only to an Area chart. Make sure that the box next to Show
display units label on chart is checked.

B. Right-click one of the Budgeted
Overheads columns and choose Change
Series Chart Type… from the menu.
In the Change Chart Type window,
choose Area in the Chart Type drop-
down list for the serie Budgeted
Overheads, and then click one of the
Area charts.
Click OK.
Analysis
7 of 19 8/12/2019, 5:37 PM
On the analysis worksheet: A. Suggested solution: Unanswered

Perform a custom sort on the cell A. Select a cell in the cell range A4:D31.
range A4:D31 by Region of Europe in
Click the Sort button on the Data tab.
the order West, South, North, East. B.
Filter the cell range A4:D31 so that In the Sort window, below Column,
only sales with dates after
31/03/2008 are visible.
8 of 19 8/12/2019, 5:37 PM
On the yr 2008 worksheet: A. Suggested solution: Unanswered

Simultaneously sort the cell range A. Select a cell in the cell range A1:D81.
A1:D81 by City in A to Z order then
Click the Sort button on the Data tab.
by Sales (€000) in largest to smallest
order. B. Filter the cell range A1:D81 In the Sort window, below Column,
so that only Sales (€000) greater than choose City in the Sort by drop-down list.
10 are visible. In the Sort window, below Order, choose
A to Z in the Sort by drop-down list.
Click the Add Level button.
In the Sort window, below Column,

choose Sales (€000) in the Sort by drop-
down list.
In the Sort window, below Order, choose
Largest to Smallest in the Sort by drop-
down list.
Click OK.

create a scenario named Worst Case Click the What-If Analysis button on the
as follows: Changing Cells______Cell Data tab and choose Scenario Manager.
Values
In the Scenario Manager window, click
B5_-------------------------_6200
B6__-.__-------------------_6500 the Add button.
B7_-------------------------_6700 In the Add Scenario window, enter Worst
B8__-.__----------------_-_6400 Case in the Scenario name box.
Click OK.
In the Scenario Values window, enter the
new values for the changing cells.
Click OK.
In the Scenario Manager window, click
Close.
9 of 19 8/12/2019, 5:37 PM
On the forecast 2009 worksheet: A. Suggested solution: Unanswered

Delete the scenario named Mid Case. A. Click the What-If Analysis button on
B. Create a scenario summary report.
the Data tab and choose Scenario
Manager.
In the Scenario Manager window, select
Mid Case and click the Delete button.
On the yr 2007 worksheet collapse all Suggested solution: Unanswered

the subtotalled data so that only the In the outline on the left hand side of the
Region of Europe subtotals and the worksheet, click all outline minus signs
Grand Total are displayed. below 2. The minuses will change to
outline plus signs and the rows will
collapse so that only the Region of
Europe subtotals and the Grand Total are
displayed.
On the subtotals worksheet use a Suggested solution: Unanswered

built-in feature to subtotal by sum Select a cell in the cell range A1:D81.
the Sales (€000) column after each
change in Region of Europe in the Click the Subtotal button on the Data
tab.
cell range A1:D81.
In the Subtotal window:
In the At each change in: drop-down list,
select Region of Europe.
Make sure that Sum is selected in the Use
function: drop-down list.
Below Add subtotal to:, make sure that
the box next to Sales (€000) is checked.
Click OK.
10 of 19 8/12/2019, 5:37 PM
On the pivot worksheet change the Suggested solution: Unanswered

content of cell C4 to 16 and refresh Click cell C4 and type 16.
the pivot table.
Click a cell in the pivot table (the pivot
table cell range is E2:F107).
On the Analyze tab, below PivotTable
On the pivot worksheet: A. Manually Suggested solution: Unanswered

group the sales data in the pivot A. Move the cursor just above cell E4 so
table by Years and Quarters. B. Filter that the cursor changes into an arrow.
the pivot table so that the East and Click once when the arrow is shown to
West details do NOT display.
select the cells with dates.
Right-click a selected cell and choose
Group… In the menu.
In the Grouping window, select Quarters
and Years from the list.
Click OK.

B. Right-click the cell E3 (West) and choose
Filter from the menu, and then select the
Hide selected items option.
Do the same for cell E27 (East).
11 of 19 8/12/2019, 5:37 PM
On the pivot worksheet create a pivot Suggested solution: Unanswered

table from the cell range A110:D190 Click a cell in the cell range A110:D190.
starting at cell F110 showing City and
Click the PivotTable button on the Insert
Region of Europe in the row area, Qtr
tab.
Yr 2008 in the column area and Sales
(€ 000) field as the data to sum. In the Create PivotTable window:
Below Choose the data that you want to
analyze, make sure that Select a table or
range is selected
In the Table/Range box, verify that the
correct range of cells is selected.
Below Choose where you want the
PivotTable report to be placed, specify
the location by selecting Existing
Worksheet, and then in the Location box,
specify cell F110.
Click OK.
The Pivot Table Field pane is displayed in
the right hand part of the Excel window.
Click and drag the City and Region of

Europe fields to the Row Lables area, the
On the overheads worksheet create a Suggested solution: Unanswered

data table from the cell range C8:D17 Select the cell range C8:D17.
that will calculate the overheads
increase variables shown on the first On the Data tab, click the What-If
Analysis button, and then click Data
column of the table.
Table.
In the Data Table window, enter C4 in the
Column input cell: box.
Click OK.
Validating and Auditing
On the yr 2008 worksheet trace the Suggested solution: Unanswered

cells that are dependent on cell D79.
Select cell D79.
Click the Trace dependents button on the
Formulas tab.
12 of 19 8/12/2019, 5:37 PM

Hide the comment in cell D1. B. Use A. Right-click the cell D1 and choose Hide
built-in commands to display all
Comment in the menu.
formulas used on the worksheet.

B. Click the File tab and then click the

Apply validation criteria to the cell A. Select the cell range B5:B8.
range B5:B8 so that only a whole
Click the Data Validation button on the
number between 6500 and 8000 can
be entered. B. Apply a validation Data tab.
input message to the cell range B5:B8 In the Data Validation window:
with the title Number restriction and Choose Whole Number in the Allow:
input message Whole number. drop-down-list.
Choose between in the Data: drop-
down-list.
Enter 6500 in the Minimum: box.
Enter 8000 in the Maximum: box.
Click OK.

B. Select the cell range B5:B8.
Click the Data Validation button on the
Data tab.
In the Data Validation window, click the
Input Message tab.
Enter Number restriction in the Title:
box.
Enter Whole number in the Input
message: box.
Click OK.
Enhancing Productivity
13 of 19 8/12/2019, 5:37 PM

Delete the range named year (do not A. Click the Name Manager button on
delete worksheet data). B. Edit the
the Formulas tab.
formula in cell B10 by using named
In the Name Manager window, select the
cells instead of the B5:B8 cell
references. year entry and click the Delete button.
In the warning window, click OK.
In the Name Manager window, click
Close.

B. Select cell B10 and press the F2 key.
Replace B5:B8 in the function with
quarter_totals.
Press Enter.

Expected function =SUM(quarter_totals)
Remove the hyperlink from the text
A. Right-click cell B4 and choose Remove
in cell B4. B. Link the cell range Hyperlink from the menu.
A4:B10 as a worksheet object to the
line below the heading in the open
Word document (use ALT+TAB to B. Select the cell range A4:B10 and click
switch to it). the Copy button on the Home tab.
Press the ALT+TAB keys and switch to
the Word document.
Place the cursor on the line below the
heading.
Click the arrow below the Paste button on
the Home tab and choose Paste Special.
In the Paste Special window, select
Microsoft Office Excel Worksheet
object and then click the Paste link
button on the left hand side.
Click OK.
On the pivot worksheet use built-in Suggested solution: Unanswered

commands to replace the formulas in Select the cell range C104:C106.
the cell range C104:C106 with values.
Click the Copy button on the Home tab.
Click the arrow below the Paste button on
the Home tab.
Select Values, in the Paste Values section.
14 of 19 8/12/2019, 5:37 PM

multiply the cell range B5:B8 by cell Select the cell C13.
C13 without using a formula or
function so that the resulting values
display in the cell range B5:B8.
On the overheads worksheet record a Suggested solution: Unanswered

macro called footer stored in the
Click the Record Macro button on the
2011 forecast workbook (accepting Developer tab.
default settings) that inserts the file
In the Record Macro window:
name in the left section of the footer.
Save the file as an Excel macro- Enter footer in the Macro name: box.
enabled workbook to \. Make sure that This Workbook is
selected in the Store macro in: drop-
down list.
Click OK.
The macro is now recording.
Click the Header & Footer button on the
Insert tab.
On the Design tab, below Header &
Footer tools, click the Go to Footer
button.
Click the left section of the footer.
On the Design tab, below Header &
Footer tools, click the File Name button.
Click in a worksheet cell to confirm.
Click the Stop Recording button on the
Developer tab.
Click the File tab and then Save As.

Click Browse.
In the Save as type: drop-down list,
choose Excel Macro-Enabled Workbook.
Click Save.
15 of 19 8/12/2019, 5:37 PM
Open the file called new hires from \ Suggested solution: Unanswered
enabling all content. Break all links to Click the File tab and then Open.
external files.
Click Computer and then Browse.
In the Open window, select the file new
hires and click Open.
Run the macro called margins so that Suggested solution: Unanswered

all margins and centring on page are Click the Macros button on the
altered.
Developer tab.
In the Macro window:
Make sure that the macro called margins
is selected.
Click the Run button and wait until the
cursor has changed to normal.
The hr details file is open. Import the Suggested solution: Unanswered

text file called directors.txt in tab Select the cell A1.
delimited format from \ into the hr
details file. The import should start at Click the From Text button on the Data
tab.
cell A1 of the directors worksheet
accepting all other defaults. In the Import Text File window, select the
file directors.txt.
Click Import.
In the Text Import Wizard window, click
the Finish button.
In the Import Data window, make sure
that cell A1 is selected and click the OK
button.
Collaborative Editing
16 of 19 8/12/2019, 5:37 PM
On the sales worksheet protect only Suggested solution: Unanswered

the cell range C20:D20 using Press CTRL+A to select the entire
nochange as the password.
worksheet.
On the west worksheet, hide the Suggested solution: Unanswered

formulas in cells B10 and E2 so that
Select the cells B10 and E2.
they can't be seen in the formula bar
Right-click a selected cell and then click
when the cells are selected.
Format Cells….
In the Format Cells window, on the
Protection tab, select the Hidden check
box.
Click OK.
On the Review tab click the Protect
Sheet button.
In the Protect Sheet window, make sure
that the Protect worksheet and contents
of locked cells check box is selected, and
then click OK.
17 of 19 8/12/2019, 5:37 PM
Open the file called employees from Suggested solution: Unanswered

\ using the password secret to open Click the File tab and then Open.
and the password allow to modify.
Click Computer and then Browse.
Remove both passwords and save
the file to \. In the Open window, select the file
employees and click Open.
In the Password window, enter the
The sales targets file is open. Suggested solution: Unanswered

Compare and merge the file called On the Quick Access toolbar, click the
copy of sales targets from \ into the Compare and Merge Workbooks button
sales targets file.
(an icon that looks like a green circle).
In the Select files to merge into the
current workbook window, select the file
copy of sales targets and click Open.
18 of 19 8/12/2019, 5:37 PM
On the staff details worksheet: A. Use Suggested solution: Unanswered

the track changes feature to reject A. On the Review tab, click the Track
the change in cell D20 and accept all
Changes button, and then click
other changes that have been
Accept/Reject Changes.
tracked and changed in the
worksheet. B. Turn off the track In the Set Changes to Accept or Reject
changes while editing feature, window, click OK.
removing the worksheet from shared In the Accept or Reject window:
use. Click the Accept button for all changes
except for the change in cell D20.
Click the Reject button for the change in
D20.

B. On the Review tab, click the Track
19 of 19 8/12/2019, 5:37 PM

Icd PDF

Uploaded by

Copyright:

Available Formats

Icd PDF

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Icd PDF

Uploaded by

Copyright:

Available Formats

ICDL / ECDL 2.0 Advanced Spreadsheets MS Excel 2016 Diag Eng - R... https://icdlasia.psionline.com/phoenix/s/lw/4571586/result.html?endOfT...

ICDL / ECDL 2.0 Advanced Spreadsheets MS Excel 2016 Diag Eng

European Computer Driving Licence, ECDL, International Computer Driving

PSI is an entity independent of ECDL Foundation and is not associated with

This test is finished and the result has been saved

Total result 13%

Item Text Answer Feedback Result

The file 2011 forecast is open. Open a Suggested solution: Unanswered

On the forecast 2011 worksheet Suggested solution: Unanswered

Functions and Formulas

On the sales worksheet: A. Add a Suggested solution: 10 of 20

On the staff worksheet add an IF Suggested solution: 0 of 10

On the analysis worksheet add a Suggested solution: Unanswered

On the analysis worksheet add a Suggested solution: Unanswered

On the analysis worksheet enter an Suggested solution: Unanswered

On the forecast 2011 worksheet Suggested solution: Unanswered

On the new office worksheet add a Suggested solution: Unanswered

On the overheads worksheet enter a Suggested solution: Unanswered

On the charting worksheet, on the Suggested solution: Unanswered

Enter the values for the Minimum,

On the charting worksheet, on the Suggested solution: Unanswered

On the charting worksheet, on the Suggested solution: Unanswered

On the analysis worksheet: A. Suggested solution: Unanswered

On the yr 2008 worksheet: A. Suggested solution: Unanswered

Click the Add Level button.

In the Sort window, below Column,

On the forecast 2009 worksheet Suggested solution: Unanswered

On the forecast 2009 worksheet: A. Suggested solution: Unanswered

On the yr 2007 worksheet collapse all Suggested solution: Unanswered

On the subtotals worksheet use a Suggested solution: Unanswered

On the pivot worksheet change the Suggested solution: Unanswered

On the pivot worksheet: A. Manually Suggested solution: Unanswered

On the pivot worksheet create a pivot Suggested solution: Unanswered

Click and drag the City and Region of

On the overheads worksheet create a Suggested solution: Unanswered

Validating and Auditing

On the yr 2008 worksheet trace the Suggested solution: Unanswered

On the forecast 2009 worksheet: A. Suggested solution: Unanswered

On the forecast 2010 worksheet: A. Suggested solution: Unanswered

On the forecast 2009 worksheet: A. Suggested solution: Unanswered

On the pivot worksheet use built-in Suggested solution: Unanswered

On the forecast 2011 worksheet Suggested solution: Unanswered

On the overheads worksheet record a Suggested solution: Unanswered

Click the File tab and then Save As.

Run the macro called margins so that Suggested solution: Unanswered

The hr details file is open. Import the Suggested solution: Unanswered

On the sales worksheet protect only Suggested solution: Unanswered

On the west worksheet, hide the Suggested solution: Unanswered

Open the file called employees from Suggested solution: Unanswered

The sales targets file is open. Suggested solution: Unanswered

On the staff details worksheet: A. Use Suggested solution: Unanswered

You might also like