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...
-
Determine the normal force, shear force, and moment at a section passing through point C. Assume the support at A can be approximated by a pin and B as a roller. Units used: kip = 103 lb Given: F1 =...
-
She is single with no dependents. She worked for the same camp last year and received a full refund of all the federal taxes they withheld because she had no tax liability. She expects this year to...
-
Repeat Problem 9.4 without centering the regressors and compare the results. Which approach do you think is better? Data From Problem 9.4 Find the condition indices and the variance decomposition...
-
It is known that the connecting rod AB exerts on the crank BC a 2.5-kN force directed down and to the left along the centerline of AB. Determine the moment of that force about C. 144 mm 56 im m 42 mm
-
Ariel files his tax return as married filing jointly ( MFJ ) . He runs a general store as a sole proprietor with no employees. In 2 0 2 3 , his qualified business income ( QBI ) of $ 2 0 0 , 0 0 0...
-
Daniel Fowler, senior vintner at Napa Winery, had been put in charge of developing an optimal blending plan for the upcoming season. This assignment was the result of a recent Napa Winery board...
-
Wooltech Industries is considering the purchase of two knitting machines. The Super X Knitter costs $25,000 and is expected to generate annual profits of $6.000 for five years. The Knitting Bee will...
-
Discuss the stability of the systems described by the impulse responses below: (a) \(h(n)=2^{-n} u(n)\) (b) \(h(n)=1.5^{n} u(n)\) (c) \(h(n)=0.1^{n}\) (d) \(h(n)=2^{-n} u(-n)\) (e) \(h(n)=10^{n}...
-
Has the concept of SD achieved the balance between all three pillars: environmental protection; economic development; and social issues?
-
What, if any, is the normative content of the concept of SD?
-
Please reflect on and explain the role and usefulness of the concept of SD in relation to the protection of the environment.
-
If there is a separate principle of precaution, do we need a principle of prevention?
-
Consider the following definitions: public class Brush implements Drawable {...} public class Canvas extends JPanel { } private Brush current Brush; public class Painter extends Person { a. b. C....
-
A new car sold for $31,000. If the vehicle loses 15% of its value each year, how much will it be worth after 10 years?
-
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?
-
A vertical belt is moving upward continuously through a liquid bath at a velocity $V$. A film of the liquid adheres to the belt, which tends to drain downward due to gravity. The equilibrium...
-
A slider bearing can be described as one plate moving with a velocity $V$ parallel to a stationary plate, with a viscous lubricant in between the plates. The force applied to the moving plate is $F$,...
-
Acrylic latex paint can be described as a Bingham plastic with a yield stress of $200 \mathrm{dyn} / \mathrm{cm}^{2}$, a limiting viscosity of $50 \mathrm{cP}$, and a density of $0.95 \mathrm{~g} /...
Study smarter with the SolutionInn App