Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Need explanations and correct examples on how to complete these steps Part 3 - EXCEL Project Instructions Create a new Excel workbook on your PC
Need explanations and correct examples on how to complete these steps
Part 3 - EXCEL Project Instructions Create a new Excel workbook on your PC (or flash drive) named 18PR_excel_LastFirst, where LastFirst is your actual Lastname and Firstname (For example, if your name is Mary Smith, you will name the file 18PR_excel_SmithMary) a. Enter the following data into cell range A1 : F7 into Sheet1 worksheet of your empty, newly created Excel file b. AutoFit the column width for columns A through F Note: to AutoFit the columns, do not use the AutoFit Column Width menu option under Format. Instead, select the entire Sheet1 worksheet by clicking the diagonal down arrow 1 (left of column A) and simply position the mouse over the column line in the column headers, between any two columns so the cursor becomes a double headed arrow 2 and double-click so all columns will Autofit at the same time. Below is a sample of what your file should look like at this time NOTE: Verify function result with the image in step 48 below Enter the following data: a. In cell I 2 enter 100 b. In cell I 3 enter 150 c. In cell I 4 enter 200 d. In cell I5 enter 20 e. In cell I 6 enter 10 f. In cell I7 enter 1 g. In cell I9 enter Discount: h. In cell I10 enter Highest Value: i. In cell I11 enter Lowest Value: j. In cell I 12 enter Sum: k. In cell I 13 enter Average: I. In cell J9 enter 5 NOTE: Verify data entered with the image in step 48 below Format as follows: a. Autofit Column Width for column I b. Format J9 to Currency Number Format with 2 decimal places c. Select row 1 and Wrap Text which will display column headings in G1, H1 and J1 as multiple 3 lines within the cell. d. Select the column headings A1 : J1 and apply Cell Style of Heading 3. NOTE: Verify formatting with the image in step 48 below In cell J2, insert an IF function that calculates the value for Net Income as follows: a. If the Possible Discount (H2) is equal to " " ", i) (true) The gross income is the (Profit per Unit-Discount) multiplied by the Quantity 43. (G2$J$9)I2 ii) (false) Or else, the gross income is the Profit per Unit multiplied by the Quantity. b. Use the fill handle to copy this formula down to row 7. c. Format the cells in J2: J7 to Currency Number Format with 2 decimal places NOTE: Verify function result with the image in step 48 below Use appropriate functions to do the following: a. In cell J10 enter a function to display the highest value for the range J2 : J7 b. In cell J11 enter a function to display the lowest value for the range J2 : J7 c. In cell J12 enter a function to sum the cells in the range J2 : J7 d. In cell J13 enter a function to average the cells in the range J2 : J7 NOTE: Verify function results with the image in step 48 below Insert a 3-D Clustered Column chart (see image in step 48 below) as follows: a. Simultaneously select the following data: B1:B7, D1:D7, E1:E7, G1:G7, I1:I7 b. Insert a 3-D Clustered Column chart c. Move and resize the pie chart so that it is located in cells B9:G23 d. Change the Chart Title to Excel Project e. Change the Chart Style to Style 6 . f. Remove the data labels as follows: i) On the Chart Tools Design tab, in the Chart Layouts group, click Add Chart Element, click Data Labels, and select None. Change the Page Layout of the Updated Products worksheet so that it (see image in step 48 below): a. Is set to Landscape orientation and b. Will FIT to one Page, and c. Has a Customer Header with Your Name in the left section, Sheet Name in the center section, and Current Date on the right section, and d. Will print with Gridlines. e. Will print with Row and column headings Creating a new worksheet and using VLOOKUP: a. Copy the data from the Products worksheet to a new worksheet named: Vlookup i) Note: See step 38 for information on how to copy data from one worksheet to another b. Enter the following text i) In cell G1: Handling Fee ii) In cell A12: Category iii) In cell A13: AT iv) In cell A14: DE v) In cell A15: MD vi) In cell A16: WD vii) In cell B12: Handling Fee in Dollars viii) In cell B13: 0 ix) In cell B14: 2 x) In cell B15: 5 xi) In cell B16: 10 c. AutoFit the column width for column G d. In cell G2, insert a VLOOKUP function that determines the correct handling fee as follows: i) The function will refer to the Lookup_value in C2 ii) The function with use the Table_array A12 : B16 (though it will need to be absoluted so that each row will reference the same table array once it's copied) iii) The function will take the value from the table array from column 2 iv) The function must contain a Range_lookup of FALSE so that the categories are identical matches between the table array and the main data (if not, AT could be matched in more than one place ... ask your instructor to explain this) e. Use the fill handle to copy this formula down to row 7. f. Format G2: G7 to Currency Number Format with 2 decimal placesStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started