ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
4
Instructions
5
Important: Do not request edit access to this file in Google Sheets. Download the xlsx file locally to edit it and submit the Excel file to us. Go to File -> Download -> Microsoft Excel (.xlxs)
6
7
Chart
8
1. Create a new tab and label it "Charts"
9
2. Using the data in the "Raw Data" tab, create a Line Chart in this tab that trends Expiry Date Vs Insured Value over time
10
3. Using the data in the "Raw Data" tab, create a Bar Chart in this tab that displays Total Value by Code. Display the chart showing the highest total value at the top, and smallest at the bottom
11
12
Pivot Table
13
4. Create a pivot table in a new sheet from the table in the Raw Data tab
14
5. In the pivot table, display the Average of the Insured Value by State (as rows), and format Insured Value as a $ amount
15
16
Formulas
17
6. In the "Formulas" Tab, use a lookup formula to grab the corresponding value for "Business Type" from the Raw Data table, based on the policy number and populate this in column B (replacing the question marks)
18
7. In the "Formulas" Tab, in Cell G2, create a SUMIF formula to sum all Insured values, that are associated with the State "NY"
19
20
Extract
21
8. In the "Extract" tab, create a formula in Column B, to extract the $ amount value from the Raw Description in Column A
22
9. In the "Extract" tab, create a formula in Column C, to extract the date value from the Raw Description in Column A
23
10. In the "Extract" tab, create a formula in Column D, to extract the description part (without the date or amount) from the Raw Description in Column A
24
25
26
27
Send your completed file to applications@bruntwork.co, with the subject: "Excel Test - [Your Name]
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100