2 3 4 On the SalesData worksheet in cell B4, use an XLOOKUP function to retrieve...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
2 3 4 On the SalesData worksheet in cell B4, use an XLOOKUP function to retrieve the product name for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. If the value is not found display a blank (""). Use the AutoFill handle to copy the function down to cell B18. Use the AutoFill Options to Fill Without Formatting. In cell C4, use a VLOOKUP function to retrieve the Unit Price for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. Copy the formula to cells C5, C7:C9, C11 C12, C14 C15, C17:C18. On the SalesData worksheet, in cell G4, create a formula that will perform a calculation that multiplies the Unit Price by the sum of products sold for January, February, and March. If a product has not sold any units over the past three months, display Discontinue. If the project has sold units, display the results of the Unit Price multiplied by the sum of the products sold for January, February, and March. Copy the formula, and then paste it in cells G5, G7:G9, G11:G12, G14 G15, and G17 G18. Resize the column as needed. A1 1 2 3 ProductID 4 PRD-00078 5 PRD-000587 6 7 PRD-00056 8 PRD-000837 9 PRD-000173 10 11 PRD-000382 12 PRD-000663 13 14 PRD-000446 15 PRD-000660 16 17 PRD-000229 18 PRD-00065 19 20 21 22 23 24 25 26 27 28 29 30 31 X✓ fx Totals by Item Hard Drives Flash Drives Monitors Keyboard RAM Sales Data B Product Data Unit Price Totals Monthly Snapshot Goal 50 75 52 55 12 D January 12 7 20 18 Actual 5 2 13 3 2 E Quarter 1 Sales February 11 6 12 20 Outcome 4 6 11 5 March 12 8 13 19 5 5 12 4 G Total H 1 ProductID 2 PRD-00078 3 PRD-000587 4 PRD-00056 5 PRD-000837 6 PRD-000173 7 PRD-000382 8 PRD-000663 9 PRD-000446 10 PRD-000660 11 PRD-000229 12 PRD-00065 13 14 15 16 17 18 19 20 722742788 21 23 25 26 29 30. 31 32 B Product Name 1TB SSD Hard Drive 8TB HDD Hard Drive 2TB Flash Drive USB 3.0 32GB Flash Drive USB 2.0 128 GB Flash Drive USB 2.0 27" Monitor LED 32" Monitor LED QHD Keyboard Ergo Keyboard 64GB RAM 128GB RAM SalesData Data Category Storage Storage Storage Storage Storage Display Display Keyboard Keyboard Memory Memory D Price $ 169.99 $ 249.00 $ 47.99 $19.99 $ 21.75 $ 175.00 $ 280.00 $ 20.00 $ 35.00 $360.00 $ 650.00 E F 2 3 4 On the SalesData worksheet in cell B4, use an XLOOKUP function to retrieve the product name for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. If the value is not found display a blank (""). Use the AutoFill handle to copy the function down to cell B18. Use the AutoFill Options to Fill Without Formatting. In cell C4, use a VLOOKUP function to retrieve the Unit Price for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. Copy the formula to cells C5, C7:C9, C11 C12, C14 C15, C17:C18. On the SalesData worksheet, in cell G4, create a formula that will perform a calculation that multiplies the Unit Price by the sum of products sold for January, February, and March. If a product has not sold any units over the past three months, display Discontinue. If the project has sold units, display the results of the Unit Price multiplied by the sum of the products sold for January, February, and March. Copy the formula, and then paste it in cells G5, G7:G9, G11:G12, G14 G15, and G17 G18. Resize the column as needed. A1 1 2 3 ProductID 4 PRD-00078 5 PRD-000587 6 7 PRD-00056 8 PRD-000837 9 PRD-000173 10 11 PRD-000382 12 PRD-000663 13 14 PRD-000446 15 PRD-000660 16 17 PRD-000229 18 PRD-00065 19 20 21 22 23 24 25 26 27 28 29 30 31 X✓ fx Totals by Item Hard Drives Flash Drives Monitors Keyboard RAM Sales Data B Product Data Unit Price Totals Monthly Snapshot Goal 50 75 52 55 12 D January 12 7 20 18 Actual 5 2 13 3 2 E Quarter 1 Sales February 11 6 12 20 Outcome 4 6 11 5 March 12 8 13 19 5 5 12 4 G Total H 1 ProductID 2 PRD-00078 3 PRD-000587 4 PRD-00056 5 PRD-000837 6 PRD-000173 7 PRD-000382 8 PRD-000663 9 PRD-000446 10 PRD-000660 11 PRD-000229 12 PRD-00065 13 14 15 16 17 18 19 20 722742788 21 23 25 26 29 30. 31 32 B Product Name 1TB SSD Hard Drive 8TB HDD Hard Drive 2TB Flash Drive USB 3.0 32GB Flash Drive USB 2.0 128 GB Flash Drive USB 2.0 27" Monitor LED 32" Monitor LED QHD Keyboard Ergo Keyboard 64GB RAM 128GB RAM SalesData Data Category Storage Storage Storage Storage Storage Display Display Keyboard Keyboard Memory Memory D Price $ 169.99 $ 249.00 $ 47.99 $19.99 $ 21.75 $ 175.00 $ 280.00 $ 20.00 $ 35.00 $360.00 $ 650.00 E F
Expert Answer:
Answer rating: 100% (QA)
The answer provided below has been developed in a clear step by step manner Step 1 2On the SalesData worksheet in cell B4 use an XLOOKUP function to r... View the full answer
Related Book For
Using Microsoft Excel and Access 2016 for Accounting
ISBN: 978-1337109048
5th edition
Authors: Glenn Owen
Posted Date:
Students also viewed these programming questions
-
Make announcements for employees in the company This is the case: There is "ABC Training" going on for 2 months and the company divides the training program into 2 part (new employee and refresher...
-
1. Stella Scott has a work-study position at the fitness center on campus. Her manager has asked her to create a worksheet to keep track of attendance and revenue for the fitness center's classes....
-
PROJECT STEPS 1. Lizzy is the manager of Cozy Movies, a small local movie theater that has been in business for a very long time. She would like to keep track of the theater's sales and expenses from...
-
Your geothermal heat pump is used to heat your house in the winter. The heat pump is operated as a vapor-compression cycle. The design is such that there must be a 10 degree temperature difference...
-
What is Hooke's law? Does it apply to elastic materials or to inelastic materials?
-
Interview an insurance agent about the differences in cost on a homeowner's or auto insurance policy based on changes in the amount of the deductible that is selected. Write a one-pa.ge report of...
-
Briefly describe the balance sheet approach in accounting for income taxes.
-
The marketing department at Cleveland Furniture Mfg. has an idea for a new product that is expected to have a 6-year life cycle. After conducting market research, the company found that the product...
-
With the way how u aswered plz :(. 5. We assume 4 people having same distribution. The pooling arrangement between 4 people is given as following, __?? 3. Calculate the Expected value
-
Consider the following costs for a certain chemical product: Fixed cost: $2.045 million/year. Variable costs: Material cost: 62 cents per gallon of product. Energy cost: 24 cents per gallon of...
-
A student takes a true-false test consisting of 15 questions. Assume that the student guesses at each question and find the probability that a. the student gets at least one question correct. b. the...
-
Determine the total labor cost for January- assume the business plans to pay straight time man hours regardless of demand Production units 8,000 direct labor hours per unit 0.5 straight time man...
-
The main idea behind china s currency depreciation is to increase exports. (I think) do you think this tactic works in the long run too? Or might it have negative effects like loosing trust in...
-
Assume that the parent company's home currency is weak today and will strengthen over time relative to a foreign subsidiary's currency. Develop a hypothetical example for the parent/subsidiary...
-
1. List and define the three relational dialectics. How do they impact interpersonal relationships? Provide specific examples from your own experience. 2. If you moved into the "dyadic breakdown"...
-
Find the relation between x and y when the point Q(x, y) has the property that dist (Q, P)+ dist(Q, P2) = 4 with respect to points P(0, -1), P2(0, 1).
-
When Jamal graduated from college recently, his parents gave him $1,110 and told him to use it wisely. Jamal decided to use the money to start a retirement account. After doing some research about...
-
A survey of 70 college freshmen asked whether students planned to take biology, chemistry, or physics during their first year. Use the diagram to answer each question. How many of the surveyed...
-
Modify What SUPs Double Declining Balance Depreciation Schedule You are to add another depreciation schedule for What SUP. Use the ch4-01_DDB_student_name worksheet you created in this chapter. Add a...
-
From where does most of the information in a form come?
-
What do validation rules do?
-
Write the following in standard form. a. \((4+5 i)(2-3 i)\). b. \((1+i)^{3}\). c. \(\frac{5+3 i}{1-i}\).
-
Write the following in polar form, \(z=r e^{i \theta}\). a. \(i-1\). b. \(-2 i\). c. \(\sqrt{3}+3 i\).
-
Find all \(z\) such that \(z^{4}=16 i\). Write the solutions in rectangular form, \(z=a+i b\), with no decimal approximation or trig functions.
Study smarter with the SolutionInn App