create a function that will find the element symbol for the element in cell A2 using the table (A1:E119)

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Module 2 Project Instructions

1. In cell B2 in the Sample Summary worksheet, create a function that will find the element symbol for the element in cell A2 using the table (A1:E119) on the Elements worksheet. (5 points) 

1. Note to select the range in the elements table 

2. Argument expression meaning, = Vlookup(A2,Range in Elements table, position of column, match parameter) Example…

3. =Vlookup(C2, C2:H119, 3, 0) 

2. Fill the formula for all cells up to B27 for all the elements.

B.

1. In cell M10 on the Sample Summary worksheet in Module_2_Project.xlsx, create a function that will count how many rates in column H are 5 or greater. (2 points)

1. Note to use countif function =COUNTIF(Range,"Criteria ")

2. Example =COUNTIF(B2:B10,">=4")

 

2. In cell M11, create a function that will count how many samples for the rates are at least 5 and the sample result is greater than 100. (2 points)

C.

1. In cell I2 on the Sample Summary worksheet in Module_2_Project.xlsx, create an IFS function that will return the level for the rate in H2. (5 points) 

1. You need to the IF function absolute reference for the values in a columns K and L 

2. =IFS(the values in H>=value in K, return the value in L,…..) You need to specify the criterium five times for values in K2 to K6) 

2. Fill the formula in the cells for all elements. (1 point)

 

1. Examine the contents of cell B31 on the Sample Summary worksheet in Module_2_Project.xlsx.

2. In cell C31, create an INDEX and MATCH function to return the symbol for the contents of cell A31 (use the formula in B31 as guidance). (5 points) 

3. Repeat steps 1 and 2 for Group and Periodic table row. (You can use copy and paste and edit the formula if you want to.) Complete for Cells B31:E40

 

D

1. In the Wait Times worksheet, Obtain the Weekly average wait times for range I16:I39.  (2 points) Note= use the average function of all values for Days of week 

2. Use the IF function. In cell J16 on the Wait Times worksheet in Module_2_Project.xlsx, create a function that will return the wait time measure (Values in column A for the weekly average in I16:I39  (5 points)

1. Hint: similar steps as in Question C

3. Count the number of days with excellent wait times (<=30 sec) in I16:I39  for each week for cells F5:L5    (3 points)

 

E. 

1. Name the Wait Times Table in the Wait Time worksheet “WaitTime”. Reference the “WaitTime” Table to create the average, minimum and maximum values in the Summary Wait Time Worksheet. 

2. Obtain the average wait times for each day of the week =Average(Table name[column]) for each day in Cell B6:H6.Repeat to obtain the minu=imum and maximum values. Format as Table using Green, Table Style Medium, 7. 

3. Convert to a range. (10 points)  

 

 John wants to determine how the sales of each month contributed to the total sales. Calculate this information for her as follows: 

1. In the Sale worksheet, copy the formula in cell I6 and paste it in the range I7:I10, pasting only the formula and number formatting, to include sales totals.

a. In cell B13, insert a formula without using a function that divides the total sales for January (cell B10) by the total sales to date (cell I10).

b. Use an absolute reference to cell I10 in the formula.

c. Use the Fill Handle to fill the range C13:G13 with the formula in cell B13. (5 points)

John also needs to calculate the dividends earned each month. If the company earns $200,000 or more in a month, the dividend is 35% of the sales. If the company earns less than $200,000 in a month, the dividend is 27% of the sales. Calculate the dividends as follows: Use the Sale worksheet,

d. In cell B17, enter a formula that uses the IF function and tests whether the total sales for January (cell B10) is greater than or equal to 200000.

e. If the condition is true, multiply the total sales for January (cell B10) by 0.35 to calculate a dividend of 35%.

f. If the condition is false, multiply the total sales for January (cell B10) by 0.27 to calculate a dividend of 27%.

g. Use the Fill Handle to fill the range C17:G17 with the formula in cell B17 to calculate the dividends for February through June. (Use the example if the IF statement tab as a guide) (5 points) 

 

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme