Instructions
Chart
1. Create a new tab and label it "Charts"
2. Using the data in the "Raw Data" tab, create a Line Chart in this tab that trends Expiry Date Vs In
3. Using the data in the "Raw Data" tab, create a Bar Chart in this tab that displays Total Value by C
Pivot Table
4. Create a pivot table in a new sheet from the table in the Raw Data tab
5. In the pivot table, display the Average of the Insured Value by State (as rows), and format Insure
Formulas
6. In the "Formulas" Tab, use a lookup formula to grab the corresponding value for "Business Type"
7. In the "Formulas" Tab, in Cell G2, create a SUMIF formula to sum all Insured values, that are asso
Extract
8. In the "Extract" tab, create a formula in Column B, to extract the $ amount value from the Raw D
9. In the "Extract" tab, create a formula in Column C, to extract the date value from the Raw Descri
10. In the "Extract" tab, create a formula in Column D, to extract the description part (without the d
Send your completed file to [email protected], with the subject: "Excel Test - [Your Na
ends Expiry Date Vs Insured Value over time
plays Total Value by Code. Display the chart showing the highest total value at the top, and smallest at the bottom
ws), and format Insured Value as a $ amount
ue for "Business Type" from the Raw Data table, based on the policy number and populate this in column B (replacing the q
d values, that are associated with the State "NY"
value from the Raw Description in Column A
from the Raw Description in Column A
on part (without the date or amount) from the Raw Description in Column A
Excel Test - [Your Name]
d smallest at the bottom
this in column B (replacing the question marks)
Policy State Expiry InsuredValue ($) Location Region Construction BusinessType Earthquak Flood Code
100314 NY 2-Jan-21 8678500 Urban East Fire Resist Apartment Y Y Red
100315 NY 2-Jan-21 17580000 Urban East Frame Apartment Y Y Blue
100385 NY 3-Jan-21 1925000 Urban East Masonry Hospitality N N Grey
100264 NY 5-Jan-21 2219900 Rural East Frame Farming N N Black
100265 NY 7-Jan-21 14100000 Urban East Frame Apartment Y Y Orange
100357 NY 7-Jan-21 4762808 Urban East Masonry Other Y Y
100399 NY 8-Jan-21 13925190 Urban East Frame Apartment Y Y
100329 NY 8-Jan-21 6350000 Urban East Frame Apartment Y Y
100372 NY 10-Jan-21 1370300 Urban East Frame Apartment Y Y
100280 NY 13-Jan-21 82000 Urban East Masonry Organization Y Y
100424 NY 15-Jan-21 192000 Urban East Masonry Retail N N
100425 NY 16-Jan-21 4950000 Urban East Frame Office Bldg Y Y
100279 NY 17-Jan-21 7677000 Urban East Frame Apartment Y Y
100292 NY 18-Jan-21 13750000 Urban East Frame Apartment Y Y
100398 NY 18-Jan-21 2529400 Rural East Frame Farming N N
100453 NY 22-Jan-21 1177700 Urban East Frame Hospitality N N
100379 NY 26-Jan-21 18777355 Urban East Frame Apartment Y Y
100341 NY 28-Jan-21 1123000 Urban East Frame Office Bldg Y Y
100289 NY 28-Jan-21 2145420 Rural East Frame Farming N N
100290 NY 29-Jan-21 18933000 Urban East Frame Office Bldg Y Y
100291 NY 29-Jan-21 21400000 Urban East Fire Resist Apartment Y Y
100340 NY 29-Jan-21 3754000 Urban East Frame Office Bldg Y Y
100415 NY 30-Jan-21 9650000 Urban East Frame Office Bldg Y Y
100421 NY 31-Jan-21 9503000 Urban East Frame Office Bldg Y Y
100420 NY 2-Feb-21 7577000 Urban East Frame Apartment Y Y
100413 NY 5-Feb-21 8397700 Urban East Frame Apartment Y Y
100377 NY 9-Feb-21 11400000 Urban East Masonry Apartment Y Y
100459 NY 9-Feb-21 2550750 Rural East Frame Farming N N
100325 NY 9-Feb-21 140000 Urban East Masonry Retail N N
100419 NY 13-Feb-21 725000 Urban East Masonry Service N N
100512 NY 13-Feb-21 10098900 Urban East Frame Office Bldg Y Y
100513 NY 13-Feb-21 2937200 Rural East Frame Farming N N
100471 NY 13-Feb-21 13882500 Urban East Frame Apartment Y Y
100396 NY 15-Feb-21 2050000 Rural East Frame Farming N N
100436 NY 16-Feb-21 3805200 Rural East Metal Clad Farming N N
100492 NY 17-Feb-21 1417800 Rural East Frame Organization N N
100469 NY 20-Feb-21 7350000 Urban East Frame Apartment Y Y
100511 NY 22-Feb-21 12750000 Urban East Frame Apartment Y Y
100536 NY 22-Feb-21 7585000 Urban East Fire Resist Office Bldg Y Y
100537 NY 22-Feb-21 9493313 Urban East Masonry Apartment Y Y
100394 NY 22-Feb-21 10302000 Urban East Fire Resist Office Bldg Y Y
100410 NY 24-Feb-21 2029500 Urban East Metal Clad Apartment Y Y
100369 NY 24-Feb-21 2233200 Rural East Frame Farming N N
100409 NY 25-Feb-21 2001250 Urban East Frame Office Bldg Y Y
100546 NY 25-Feb-21 1498850 Rural East Metal Clad Farming N N
100523 NY 1-Mar-21 1311220 Urban East Frame Apartment N N
100433 NY 1-Mar-21 1769785 Urban East Frame Hospitality Y Y
100522 NY 2-Mar-21 1599860 Rural East Frame Farming N N
100457 NY 5-Mar-21 10780450 Urban East Frame Apartment Y Y
100583 NY 8-Mar-21 7611000 Urban East Masonry Office Bldg Y Y
100446 NY 9-Mar-21 1600000 Urban East Masonry Hospitality Y Y
100490 NY 12-Mar-21 220000 Urban East Metal Clad Hospitality N N
100456 NY 12-Mar-21 1800000 Urban East Frame Apartment Y Y
100545 NY 14-Mar-21 14450000 Urban East Frame Office Bldg Y Y
100543 NY 14-Mar-21 2835800 Urban East Metal Clad Manufacturing N N
100444 NY 17-Mar-21 8892200 Urban East Masonry Apartment Y Y
100487 NY 22-Mar-21 5990067 Urban East Frame Apartment N N
100519 NY 23-Mar-21 4102500 Rural East Frame Farming N N
100462 NY 25-Mar-21 3400000 Urban East Frame Construction Y Y
100486 NY 26-Mar-21 9973900 Urban East Frame Apartment Y Y
100463 NY 26-Mar-21 15480000 Urban East Frame Office Bldg Y Y
100595 NY 27-Mar-21 2446600 Rural East Frame Farming N N
100581 NY 30-Mar-21 5150000 Urban East Frame Apartment Y Y
100504 NY 31-Mar-21 1649105 Rural East Frame Farming N N
100616 NY 31-Mar-21 2329500 Urban East Frame Apartment N N
100614 NY 31-Mar-21 721500 Urban East Frame Apartment Y Y
100625 NY 7-Apr-21 49837500 Urban East Fire Resist Apartment Y Y
100540 NY 7-Apr-21 2244800 Rural East Frame Farming N N
100642 NY 11-Apr-21 1697200 Urban East Frame Other N N
100560 NY 15-Apr-21 1381370 Urban East Frame Organization N N
100629 NY 16-Apr-21 2442400 Urban East Frame Retail N N
100630 NY 17-Apr-21 7394300 Urban East Frame Apartment Y Y
100637 NY 17-Apr-21 2402500 Urban East Frame Apartment Y Y
100623 NY 25-Apr-21 2006700 Rural East Frame Farming N N
100610 NY 26-Apr-21 5086300 Urban East Frame Office Bldg Y Y
100591 NY 27-Apr-21 4353100 Rural East Frame Farming N N
100557 NY 29-Apr-21 2056700 Urban East Frame Other Y Y
100628 NY 30-Apr-21 1973200 Rural East Frame Farming N N
100650 NY 1-May-21 4156000 Urban East Masonry Hospitality Y Y
100569 NY 2-May-21 800000 Urban East Frame Construction N N
100649 NY 3-May-21 3074775 Rural East Frame Farming N N
100677 NY 6-May-21 13370000 Urban East Frame Apartment Y Y
100674 NY 7-May-21 9250000 Urban East Frame Office Bldg Y Y
100675 NY 8-May-21 2468300 Urban East Frame Apartment Y Y
100648 NY 8-May-21 270000 Urban East Masonry Retail N N
100602 NY 10-May-21 1651300 Rural East Frame Farming N N
100655 NY 12-May-21 1974300 Urban East Masonry Organization Y Y
100671 NY 12-May-21 3660780 Rural East Frame Farming N N
100646 NY 12-May-21 2550000 Urban East Frame Apartment Y Y
100618 NY 13-May-21 2730000 Urban East Metal Clad Hospitality Y Y
100681 NY 14-May-21 963100 Urban East Metal Clad Apartment Y Y
100619 NY 14-May-21 1419500 Rural East Frame Farming N N
100627 NY 15-May-21 2758482 Urban East Fire Resist Apartment Y Y
100689 NY 16-May-21 19451600 Urban East Frame Office Bldg Y Y
100726 NY 16-May-21 1292345 Urban East Frame Office Bldg N N
100644 NY 18-May-21 2570600 Urban East Masonry Office Bldg Y Y
100643 NY 19-May-21 1280000 Urban East Fire Resist Hospitality Y Y
100698 NY 20-May-21 205000 Urban East Fire Resist Retail N N
100705 NY 21-May-21 3943000 Rural East Frame Farming N N
100718 NY 21-May-21 3107790 Urban East Masonry Office Bldg N Y
100744 NY 21-May-21 9448200 Urban East Frame Apartment Y Y
100749 NY 22-May-21 847300 Urban East Frame Apartment Y Y
100752 NY 22-May-21 363200 Urban East Frame Office Bldg N Y
100666 NY 23-May-21 1927450 Rural East Frame Farming N N
100667 NY 24-May-21 2660299 Rural East Frame Farming N N
100756 NY 27-May-21 16950000 Urban East Frame Apartment Y Y
100754 NY 27-May-21 36909180 Urban East Metal Clad Manufacturing Y Y
100764 NY 29-May-21 9600000 Urban East Frame Office Bldg Y N
100724 NY 29-May-21 22050000 Urban East Frame Office Bldg Y Y
100686 NY 30-May-21 3850000 Urban East Frame Construction N Y
100695 NY 2-Jun-21 2000000 Urban East Frame Hospitality Y Y
100774 NY 3-Jun-21 6750000 Urban East Frame Apartment Y Y
100759 NY 5-Jun-21 17050000 Urban East Frame Office Bldg Y Y
100722 NY 5-Jun-21 1958400 Rural East Frame Farming N N
100731 NY 6-Jun-21 17746832 Urban East Masonry Apartment Y Y
100746 NY 7-Jun-21 847300 Urban East Frame Office Bldg Y Y
100781 NY 9-Jun-21 1714835 Urban East Frame Apartment N N
100775 NY 10-Jun-21 3136600 Rural East Frame Farming N N
100773 NY 10-Jun-21 1780000 Urban East Frame Hospitality Y Y
100789 NY 11-Jun-21 230000 Urban East Frame Manufacturing Y Y
100793 NY 26-Aug-21 1275600 Urban East Frame Apartment N Y
100797 NY 27-Aug-21 5781710 Rural East Frame Farming N N
100800 NY 30-Aug-21 1379400 Urban East Frame Apartment N N
100805 NY 31-Aug-21 199000 Rural East Frame Retail N N
100810 NY 2-Sep-21 1875000 Urban East Masonry Hospitality Y Y
100813 NY 2-Sep-21 1986100 Urban East Frame Apartment N N
100822 NY 5-Sep-21 3866420 Rural East Frame Farming N N
100823 NY 6-Sep-21 1037500 Urban East Frame Manufacturing N N
100824 NY 6-Sep-21 2480800 Rural East Frame Farming N N
100826 NY 7-Sep-21 3178400 Urban East Frame Office Bldg Y Y
100832 NY 10-Sep-21 18275350 Urban East Frame Office Bldg Y Y
100834 NY 10-Sep-21 710400 Urban East Masonry Office Bldg N N
100845 NY 15-Sep-21 24000000 Urban East Frame Apartment Y Y
100846 NY 15-Sep-21 7700000 Urban East Fire Resist Construction Y Y
100859 NY 23-Sep-21 11147050 Urban East Fire Resist Apartment Y Y
100861 NY 23-Sep-21 2007000 Urban East Frame Office Bldg Y Y
100863 NY 23-Sep-21 9404500 Urban East Frame Apartment Y Y
100864 NY 24-Sep-21 5056900 Urban East Fire Resist Apartment Y Y
100869 NY 26-Sep-21 953100 Urban East Metal Clad Office Bldg N N
100870 NY 26-Sep-21 2562500 Rural East Fire Resist Manufacturing N N
100872 NY 27-Sep-21 6908000 Urban East Frame Office Bldg Y Y
100874 NY 29-Sep-21 311200 Urban East Frame Apartment N N
100875 NY 30-Sep-21 14850000 Urban East Frame Office Bldg Y Y
100876 NY 1-Oct-21 1557500 Urban East Frame Office Bldg N Y
100878 NY 1-Oct-21 4267000 Urban East Frame Office Bldg Y Y
100883 NY 3-Oct-21 280600 Urban East Frame Office Bldg N N
100886 NY 4-Oct-21 6748000 Urban East Frame Apartment Y Y
100889 NY 5-Oct-21 3235700 Urban East Frame Apartment Y Y
100894 NY 5-Oct-21 2356100 Urban East Frame Hospitality Y Y
100895 NY 7-Oct-21 7932500 Urban East Frame Apartment Y Y
100898 NY 7-Oct-21 1851125 Urban East Frame Office Bldg N N
100900 NY 10-Oct-21 8345500 Urban East Frame Apartment Y Y
100901 NY 11-Oct-21 2400000 Urban East Frame Construction Y Y
100908 NY 13-Oct-21 13900000 Urban East Frame Office Bldg Y Y
100909 NY 14-Oct-21 13575000 Urban East Frame Office Bldg Y Y
100910 NY 14-Oct-21 5950500 Urban East Metal Clad Office Bldg Y Y
100215 NY 15-Oct-21 2131900 Urban East Masonry Retail N N
100916 NY 17-Oct-21 1856637 Urban East Masonry Hospitality Y Y
100923 NY 21-Oct-21 1830900 Urban East Frame Office Bldg N N
100928 NY 22-Oct-21 1267625 Urban East Frame Office Bldg N N
100929 NY 22-Oct-21 3409000 Rural East Metal Clad Farming N N
100214 NY 22-Oct-21 33250000 Urban East Frame Apartment Y Y
100940 NY 27-Oct-21 21250000 Urban East Frame Office Bldg Y Y
100941 NY 28-Oct-21 2534633 Rural East Frame Farming N N
100942 NY 28-Oct-21 2494300 Urban East Frame Apartment Y Y
100943 NY 28-Oct-21 2562500 Urban East Frame Apartment Y Y
100945 NY 29-Oct-21 503000 Urban East Masonry Hospitality N Y
100947 NY 29-Oct-21 10259600 Urban East Frame Apartment Y Y
100226 NY 29-Oct-21 3798400 Urban East Metal Clad Office Bldg Y Y
100950 NY 30-Oct-21 2363700 Urban East Frame Farming N N
100953 NY 1-Nov-21 4127900 Rural East Metal Clad Farming N N
100287 NY 3-Nov-21 10518041 Urban East Frame Office Bldg Y Y
100261 NY 5-Nov-21 15625000 Urban East Frame Office Bldg Y Y
100962 NY 6-Nov-21 15750000 Urban East Masonry Apartment Y Y
100972 NY 9-Nov-21 2398000 Rural East Metal Clad Farming N N
100977 NY 10-Nov-21 4981500 Rural East Frame Farming N N
100978 NY 10-Nov-21 535000 Urban East Fire Resist Retail N N
100981 NY 10-Nov-21 10397500 Urban East Frame Apartment Y Y
100982 NY 11-Nov-21 10808800 Urban East Frame Office Bldg Y Y
100983 NY 11-Nov-21 1962400 Urban East Frame Apartment N N
100985 NY 12-Nov-21 3067800 Rural East Frame Farming N N
100986 NY 13-Nov-21 2815900 Urban East Metal Clad Manufacturing N N
100302 NY 14-Nov-21 847300 Urban East Frame Apartment Y Y
100274 NY 14-Nov-21 2029750 Urban East Frame Apartment N N
100209 NY 15-Nov-21 2636313 Rural East Frame Farming N N
100991 NY 16-Nov-21 2080000 Urban East Masonry Hospitality Y Y
100273 NY 18-Nov-21 2000000 Urban East Masonry Apartment Y Y
100996 NY 19-Nov-21 9900000 Urban East Frame Apartment Y Y
100999 NY 19-Nov-21 4477175 Rural East Frame Farming Y N
101000 NY 19-Nov-21 2395000 Urban East Frame Hospitality N Y
100257 NY 19-Nov-21 5550000 Urban East Fire Resist Apartment Y Y
100258 NY 20-Nov-21 1922800 Urban East Metal Clad Office Bldg Y Y
101005 NY 21-Nov-21 10811785 Rural East Metal Clad Retail N N
100208 NY 21-Nov-21 30000 Urban East Fire Resist Service Y Y
101008 NY 22-Nov-21 15700000 Urban East Frame Office Bldg Y Y
100271 NY 24-Nov-21 10979275 Rural East Frame Farming N N
100301 NY 25-Nov-21 6198000 Urban East Frame Office Bldg Y Y
101023 NY 27-Nov-21 660473 Urban East Frame Apartment N N
100206 NY 27-Nov-21 5213100 Urban East Frame Apartment Y Y
100286 NY 28-Nov-21 2336132 Rural East Metal Clad Farming N N
100334 NY 28-Nov-21 1500000 Urban East Fire Resist Hospitality Y Y
101029 NY 28-Nov-21 8115500 Urban East Fire Resist Apartment Y Y
101031 NY 28-Nov-21 2162291 Rural East Frame Farming N N
100222 NY 30-Nov-21 3295800 Rural East Frame Farming N N
101034 NY 30-Nov-21 9710700 Urban East Frame Apartment Y Y
100221 NY 30-Nov-21 2350000 Urban East Masonry Apartment Y Y
100333 NY 1-Dec-21 2806200 Rural East Frame Farming N N
101037 NY 2-Dec-21 2325718 Urban East Metal Clad Manufacturing N N
101039 NY 2-Dec-21 245000 Urban East Frame Service N N
101044 NY 4-Dec-21 12220000 Urban East Frame Office Bldg Y Y
100219 NY 4-Dec-21 1144300 Urban East Frame Apartment Y Y
100220 NY 5-Dec-21 4530000 Urban East Frame Apartment Y Y
100300 NY 5-Dec-21 2000300 Urban East Masonry Office Bldg N N
101054 NY 7-Dec-21 1510000 Urban East Masonry Hospitality N N
100234 NY 7-Dec-21 3871000 Urban East Frame Apartment Y Y
100269 NY 8-Dec-21 513300 Urban East Frame Apartment N N
100317 NY 9-Dec-21 2943800 Rural East Frame Farming N N
100319 NY 9-Dec-21 8923000 Urban East Frame Apartment Y Y
101061 NY 9-Dec-21 6450000 Urban East Frame Office Bldg Y Y
100365 NY 9-Dec-21 4479400 Urban East Frame Apartment N N
101066 NY 11-Dec-21 2013357 Rural East Frame Farming N N
101067 NY 11-Dec-21 4017155 Rural East Frame Farming N N
101068 NY 11-Dec-21 11650000 Urban East Frame Office Bldg Y Y
100244 NY 11-Dec-21 9250000 Urban East Frame Apartment Y Y
101070 NY 12-Dec-21 3553009 Rural East Frame Farming N N
101073 NY 12-Dec-21 2854500 Urban East Masonry Apartment Y Y
100364 NY 13-Dec-21 1882600 Rural East Frame Farming N N
101075 NY 14-Dec-21 2393232 Rural East Frame Farming N N
101076 NY 14-Dec-21 14678200 Urban East Frame Office Bldg Y Y
100218 NY 14-Dec-21 5017360 Rural East Frame Farming N N
101083 NY 17-Dec-21 2625000 Urban East Frame Apartment Y Y
101088 NY 18-Dec-21 285700 Urban East Frame Office Bldg N N
101089 NY 19-Dec-21 275000 Urban East Fire Resist Retail Y N
101090 NY 19-Dec-21 10396250 Urban East Frame Office Bldg Y Y
101091 NY 19-Dec-21 27208210 Urban East Fire Resist Apartment Y Y
100347 NY 19-Dec-21 535000 Urban East Frame Office Bldg N N
100346 NY 21-Dec-21 1780000 Urban East Masonry Hospitality Y Y
100391 NY 21-Dec-21 10746600 Urban East Frame Apartment Y Y
101096 NY 21-Dec-21 645000 Urban East Fire Resist Retail N N
100203 NY 21-Dec-21 3724339 Rural East Frame Farming N N
100231 NY 22-Dec-21 12493000 Urban East Frame Apartment Y Y
101100 NY 22-Dec-21 2159000 Urban East Frame Retail N N
100217 NY 23-Dec-21 100000 Rural East Metal Clad Farming N N
101102 NY 23-Dec-21 1121600 Rural East Metal Clad Farming N N
100305 NY 23-Dec-21 9000000 Urban East Fire Resist Apartment Y Y
101104 NY 24-Dec-21 292200 Urban East Frame Apartment Y Y
100201 NY 24-Dec-21 11575700 Urban East Frame Apartment Y Y
100361 NY 25-Dec-21 1125600 Urban East Masonry Apartment N N
101107 NY 25-Dec-21 9377600 Urban East Frame Apartment Y Y
101109 NY 25-Dec-21 3920500 Rural East Frame Farming N N
101111 NY 26-Dec-21 6235057 Rural East Frame Farming N N
101113 NY 27-Dec-21 2500000 Rural East Metal Clad Farming N N
100200 NY 27-Dec-21 1425000 Urban East Masonry Apartment N N
100330 NY 28-Dec-21 1545100 Urban East Frame Office Bldg Y Y
101116 NY 28-Dec-21 8245000 Urban East Frame Apartment Y Y
100389 NY 30-Dec-21 1480000 Urban East Fire Resist Hospitality Y Y
101117 NY 30-Dec-21 9050000 Urban East Frame Apartment Y Y
101120 NY 30-Dec-21 1480755 Rural East Frame Farming N N
101123 NY 31-Dec-21 2250000 Urban East Frame Office Bldg Y Y
101125 NY 31-Dec-21 16482200 Urban East Frame Office Bldg Y Y
Total Value ($)
7203500
3189300
3145700
18777355
8800000
Policy BusinessType
100314 Apartment
100359 Office Bldg
100315 #N/A
100385 Office Bldg
100388 #N/A
100358 Office Bldg
NY
8678500
0
17580000
1925000
0
0
?? Create SUMIF formula to add all insured values corresponding to the State "NY"
Raw Description Amount Date
04/28 VZEFEWRLSS*APOEEFCC VISE 24300-5922-02434 FL $22.84 $22.84 04/28
05/07 KENJEFEF #3905 FEKJFE FL $27.63 $27.63 05/07
Description
EFEWRLSS*APOEEFCC VISE 24300-5922-02434 FL $22.84
KENJEFEF #3905 FEKJFE FL $27.63
