1. Return to the Projects worksheet. In the range I17:J22, Hwan wants to display the project estimate amounts per state. He can retrieve this information from the PivotTable on the Projects by State worksheet. Display the estimates per state as follows:
   1. In cell J18, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Montana from cell G5 on the Projects by State worksheet.
   2. In cell J19, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in North Dakota from cell G10 on the Projects by State worksheet.
   3. In cell J20, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Nebraska from cell G15 on the Projects by State worksheet.
   4. In cell J21, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in South Dakota from cell G21 on the Projects by State worksheet.
   5. In cell J22, insert a formula using the GETPIVOTDATA function to display the total estimate amount for projects in Wyoming from cell G26 on the Projects by State worksheet.

x AutoSave Off g NP_EX19_CS9-12a_AmyNichols_2 9 Search (Alt+Q) amy nichols X File Home Insert Draw Page Layout Formulas Data Review View Developer Help Power Pivot Comments Share Lo Record Macro Properties Map Properties Import Visual Macros Use Relative References Add- Exce COM Insert Design Lo View Code Source Expansion Packs gy Export Basic A Macro Security ins Add-ins Add-ins Mode ! Run Dialog 30 Refresh Data Code Add-ins Controls XML X i AUTOSAVE TURNED OFF This workbook contains features that prevent it from using AutoSave. Please save your workbook manually. J18 vi XV fx ='Projects by State'!G5 A B C D E G H I K L M N O P Q R W&K Engineering Projects Add Project Print Stats AW NI Client ID Project Type Category State Project Start - Estimate Paid to Date Category Projects Total Estimates Avg Estimates W-0338 Attenuators Highway NE 1/12/2021 $30,562 $30,562 Bridge $176,406 44,102 5 W-0341 Signage Highway NE 1/20/2021 $14,500 $12,000 Highway $258,530 $36,933 6 W- 0277 Grade crossing Bridge WY 2/16/2021 $54,672 $50,000 Paving 15 $858,325 $57,222 7 W-0344 Noise abatement Site development SD 2/22/2021 $38,750 $30,000 Site development $389,934 $48,742 8 W-0290 Erosion control Site development NE 2/22/2021 $107,654 $57,654 9 W-0185 Cold planing Paving ND 3/1/2021 $48,932 $45,932 Estimate 10 W-0338 Abutments Bridge NE 3/12/2021 $72,358 $72,358 >50,000 11 W-0322 Off-road rest area Site development WY 3/20/2021 $24,505 $24,505 Projects 14 12 W-0290 Hydraulic fill Site development NE 3/23/2021 $34,756 $34,756 13 W-0327 Patching Paving ND 3/24/2021 $68,900 $34,900 Client ID 14 W-0332 Ramps Paving MT 3/30/2021 $42,350 $4,250 W-0338 15 W-0338 Seal coating Paving NE 4/2/2021 $27,800 $17,000 Total paid $170,270 16 W-0364 Seal coating Paving SD 4/2/2021 $28,500 $28,500 17 W-0277 Grade separation Bridge WY 4/5/2021 $19,576 $10,576 Projects by State 18 W-0132 Highway grading Paving MT 4/5/2021 $70,347 $30,400 MT $302,117 19 W-0185 Curbing Paving ND 4/14/2021 $78,400 $38,400 ND $331,598 20 W-0358 Shoulders Paving MT 4/16/2021 $52,050 $12,050 NE $577,122 21 W-0332 Patching Paving MT 4/16/2021 $76,320 $16,320 SD $278,605 22 W-0284 Median strip Highway SD 4/17/2021 $56,890 $6.000 WY 23 W-0360 Resurfacing Paving NE 4/23/2021 $34,560 $17,000 24 W-0322 Grading Site development WY 5/2/2021 $19,750 $2,000 25 W-0185 Patching Paving ND 5/10/2021 $34,566 $24,000 26 W-0332 Abutments Bridge MT 5/14/2021 $29.800 $20,000 ... Projects Project Stats Projects by State Project Payments Projects by Category Monthly Sales Employees Expansion Loan Investment ... + 1 1209 Ready Lo %% Accessibility: Investigate - - 46F Search P W A A X 8:36 PM Partly cloudy 12/13/2022x AutoSave Off g NP_EX19_CS9-12a_AmyNichols_2 Search (Alt+Q) amy nichols X File Home Insert Draw Page Layout Formulas Data Review View Developer Help Power Pivot PivotTable Analyze Design Comments Share Lo Record Macro Properties Map Properties Import Visual Macros Use Relative References Add- Exce COM Insert | View Code Source [oz] Expansion Packs key Export Basic A Macro Security ins Add-ins Add-ins Mode ! Run Dialog 30 Refresh Data Code Add-ins Controls XML X i AUTOSAVE TURNED OFF This workbook contains features that prevent it from using AutoSave. Please save your workbook manually. G vi X fx 302117 A B C D E F G H I K L M N O P PivotTable Fields X W&K Projects by State Choose fields to add to report: Search AWN- Estimates Category Client ID State Project Start . Bridge Highway Paving Site development Grand Total Project Type 5 GMT $29,800 $31,250 $241,067 $302,117 Category Y a Mar $42,350 $42,350 State Apr $198,717 $198, 717 Project Start May $29,800 $29,800 Estimate Jun $31,250 $31,250 Paid to Date Amt Owed 10 AND $100,800 $230,798 $331,598 % Difference 11 Mar $117,832 $117,832 12 Apr $78,400 $78,400 More Tables... 13 May $18,500 $34,566 $53,066 14 Jun $82,300 $82,300 15 ONE $72,358 $45,062 $282,710 $176,992 $577,122 Drag fields between areas below: 16 Jan $45,062 $45,062 17 Feb $107,654 $107,654 Y Filters Ill Columns 18 Mar $72,358 $34,756 $107, 114 Category 19 Apr $62,360 $62,360 20 Jun $220,350 $34,582 $254,932 21 9 SD $81,418 $28,500 $168,687 $278,605 22 Feb $38,750 $38,750 23 Apr $56,890 $28,500 $85,390 Rows E Values 24 May $24,528 $85,437 $109,965 State Estimates 25 Jun $44,500 $44,500 Project Start 26 OWY $74,248 $75,250 $44,255 $193,753 27 Feb $54,672 $54,672 28 Mar $24,505 $24,505 29 ApI $19,576 $19,576 30 Max $10 750 $10 750 O Defer Layout Update Update ... Projects Project Stats Projects by State Project Payments Projects by Category Monthly Sales Employees ... + : 1109 Ready 1% Accessibility: Investigate 46 F A X 8:37 PM Search W Partly cloudy 12/13/2022