Next, in the RawData worksheet: enter the formulas to complete the two missing column entries (note:...

    

Transcribed Image Text:

Next, in the RawData worksheet: enter the formulas to complete the two missing column entries (note: you must complete this step before proceeding to the creation of the pivot table). ● In the State column (column C), enter a VLOOKUP() function that will find the state each customer lives in (based on the CustomerID (column B) in the RawData worksheet) using the Customers worksheet, which contains each customer's data (including IDs, names and states). In the Sector column (column E), enter a VLOOKUP() function to find the sector for each stock (based on the Ticker symbol (column D) in the RawData worksheet) using the Stocks worksheet, which contains each stock's data (including its ticker symbol, stock name and sector). TransactionID TR093016275 | CustomerID CN0051411 TR093011962 CN0013372 TR093010648 CN0272657 TR093016432 CN0096893 TR093003876 CN0196579 TR093024190 CN0678023 TR093021236 CN0333567 TR093014330 CN0597295 TR093025402 CN0587850 TR093022880 CN0193371 TR093017116 CN0368745 TR093009306 CN0534728 TR093004193 CN0050434 TR093012693 CN0481071 TR093024338 CN0062289 TR093014340 CN0274301 =TR093011272 CN0492014 TR093023692 CN0597698 TR093013058 CN0379308 TR093026660 CN0201301 TR093015203 CN0707437 TR093015850 CN0364364 TR093010217 CN0425187 TR093017950 CN0575153 TR093010071 CN0603575 TR093017225 CN0193371 TR093018091 CN0147242 TR093021427 CN0670727 State Ticker OMC USB MHFI ZBH FISV FB IR EQT AES CHK UNP ORLY BLL KORS EXPE TSCO HD AAP PSX EMR TGNA TWC SCG ESS APH DLTR RHT DVN Sector Qty 6450 9500 8775 8850 7475 11900 5050 5025 9600 8575 10425 10900 950 5750 7450 11850 7750 6200 2075 1525 8750 1375 10825 7825 7425 5875 12150 12150 UnitPrice $138.93 $72.52 $144.88 $157.56 $25.43 $67.31 $28.35 $80.15 $77.19 $56.42 $102.06 $143.76 $216.06 $20.30 $23.48 $26.28 $197.39 $80.45 $50.03 $16.89 $64.63 $517.01 $4.53 $136.92 $60.97 $27.17 $128.92 $119.45 Total Dollar Value $896,098.50 $688,940.00 $1,271,322.00 $1,394,406.00 $190,089.25 $800,989.00 $143,167.50 $402,753.75 $741,024.00 $483,801.50 $1,063,975.50 $1,566,984.00 $205,257.00 $116,725.00 $174,926.00 $311,418.00 $1,529,772.50 $498,790.00 $103,812.25 $25,757.25 $565,512.50 $710,888.75 $49,037.25 $1,071,399.00 $452,702.25 $159,623.75 $1,566,378.00 $1,451,317.50 TR093009306 CN0534728 TR093004193 CN0050434 TR093012693 CN0481071 TR093024338 CN0062289 TR093014340 CN0274301 TR093011272 CN0492014 TR093023692 CN0597698 TR093013058 CN0379308 TR093026660 CN0201301 TR093015203 CN0707437 TR093015850 CN0364364 TR093010217 CN0425187 TR093017950 CN0575153 TR093010071 CN0603575 TR093017225 CN0193371 TR093018091 CN0147242 TR093021427 CN0670727 TR093016555 CN0641919 TR093019436 CN0238038 TR093008940 CN0313971 TR093023268 CN0285872 TR093015809 CN0306120 TR093023763 CN0744489 TR093015995 CN0710881 TR093013733 CN0325322 TR093025648 CN0744719 ORLY BLL KORS EXPE TSCO HD AAP PSX EMR TGNA TWC SCG ESS APH DLTR RHT DVN VRTX MAC AZO ITW SNDK EIX AFL PCL CBG 10900 950 7825 7425 5875 5750 7450 11850 7750 6200 2075 1525 8750 1375 $517.01 10825 $4.53 $136.92 $60.97 $27.17 $143.76 $216.06 12150 9650 6425 $20.30 $23.48 $26.28 $197.39 $80.45 11375 4250 5825 4050 2500 5300 7550 12150 $128.92 $119.45 $45.87 $130.16 $41.23 $232.64 $132.32 $50.03 $16.89 $64.63 $7.21 $79.26 $112.92 $43.18 $1,566,984.00 $205,257.00 $116,725.00 $174,926.00 $311,418.00 $1,529,772.50 $498,790.00 $103,812.25 $25,757.25 $565,512.50 $710,888.75 $49,037.25 $1,071,399.00 $452,702.25 $159,623.75 $1,566,378.00 $1,451,317.50 $442,645.50 $836,278.00 $468,991.25 $988,720.00 $770,764.00 $29,200.50 $198,150.00 $598,476.00 $326,009.00 Next, in the RawData worksheet: enter the formulas to complete the two missing column entries (note: you must complete this step before proceeding to the creation of the pivot table). ● In the State column (column C), enter a VLOOKUP() function that will find the state each customer lives in (based on the CustomerID (column B) in the RawData worksheet) using the Customers worksheet, which contains each customer's data (including IDs, names and states). In the Sector column (column E), enter a VLOOKUP() function to find the sector for each stock (based on the Ticker symbol (column D) in the RawData worksheet) using the Stocks worksheet, which contains each stock's data (including its ticker symbol, stock name and sector). TransactionID TR093016275 | CustomerID CN0051411 TR093011962 CN0013372 TR093010648 CN0272657 TR093016432 CN0096893 TR093003876 CN0196579 TR093024190 CN0678023 TR093021236 CN0333567 TR093014330 CN0597295 TR093025402 CN0587850 TR093022880 CN0193371 TR093017116 CN0368745 TR093009306 CN0534728 TR093004193 CN0050434 TR093012693 CN0481071 TR093024338 CN0062289 TR093014340 CN0274301 =TR093011272 CN0492014 TR093023692 CN0597698 TR093013058 CN0379308 TR093026660 CN0201301 TR093015203 CN0707437 TR093015850 CN0364364 TR093010217 CN0425187 TR093017950 CN0575153 TR093010071 CN0603575 TR093017225 CN0193371 TR093018091 CN0147242 TR093021427 CN0670727 State Ticker OMC USB MHFI ZBH FISV FB IR EQT AES CHK UNP ORLY BLL KORS EXPE TSCO HD AAP PSX EMR TGNA TWC SCG ESS APH DLTR RHT DVN Sector Qty 6450 9500 8775 8850 7475 11900 5050 5025 9600 8575 10425 10900 950 5750 7450 11850 7750 6200 2075 1525 8750 1375 10825 7825 7425 5875 12150 12150 UnitPrice $138.93 $72.52 $144.88 $157.56 $25.43 $67.31 $28.35 $80.15 $77.19 $56.42 $102.06 $143.76 $216.06 $20.30 $23.48 $26.28 $197.39 $80.45 $50.03 $16.89 $64.63 $517.01 $4.53 $136.92 $60.97 $27.17 $128.92 $119.45 Total Dollar Value $896,098.50 $688,940.00 $1,271,322.00 $1,394,406.00 $190,089.25 $800,989.00 $143,167.50 $402,753.75 $741,024.00 $483,801.50 $1,063,975.50 $1,566,984.00 $205,257.00 $116,725.00 $174,926.00 $311,418.00 $1,529,772.50 $498,790.00 $103,812.25 $25,757.25 $565,512.50 $710,888.75 $49,037.25 $1,071,399.00 $452,702.25 $159,623.75 $1,566,378.00 $1,451,317.50 TR093009306 CN0534728 TR093004193 CN0050434 TR093012693 CN0481071 TR093024338 CN0062289 TR093014340 CN0274301 TR093011272 CN0492014 TR093023692 CN0597698 TR093013058 CN0379308 TR093026660 CN0201301 TR093015203 CN0707437 TR093015850 CN0364364 TR093010217 CN0425187 TR093017950 CN0575153 TR093010071 CN0603575 TR093017225 CN0193371 TR093018091 CN0147242 TR093021427 CN0670727 TR093016555 CN0641919 TR093019436 CN0238038 TR093008940 CN0313971 TR093023268 CN0285872 TR093015809 CN0306120 TR093023763 CN0744489 TR093015995 CN0710881 TR093013733 CN0325322 TR093025648 CN0744719 ORLY BLL KORS EXPE TSCO HD AAP PSX EMR TGNA TWC SCG ESS APH DLTR RHT DVN VRTX MAC AZO ITW SNDK EIX AFL PCL CBG 10900 950 7825 7425 5875 5750 7450 11850 7750 6200 2075 1525 8750 1375 $517.01 10825 $4.53 $136.92 $60.97 $27.17 $143.76 $216.06 12150 9650 6425 $20.30 $23.48 $26.28 $197.39 $80.45 11375 4250 5825 4050 2500 5300 7550 12150 $128.92 $119.45 $45.87 $130.16 $41.23 $232.64 $132.32 $50.03 $16.89 $64.63 $7.21 $79.26 $112.92 $43.18 $1,566,984.00 $205,257.00 $116,725.00 $174,926.00 $311,418.00 $1,529,772.50 $498,790.00 $103,812.25 $25,757.25 $565,512.50 $710,888.75 $49,037.25 $1,071,399.00 $452,702.25 $159,623.75 $1,566,378.00 $1,451,317.50 $442,645.50 $836,278.00 $468,991.25 $988,720.00 $770,764.00 $29,200.50 $198,150.00 $598,476.00 $326,009.00

Answer rating: 100% (QA)

Answer Create table with three worksheet As sample 1 Raw d... View the full answer