Exercise 4. The worksheet “Hours” (see attached Excel file) contains the number of hours that 7 employees have worked over the past 100 days. Some employees have not worked in some days. a) Create a function named employee that takes an employee name as parameter and returns the range of cells starting day 1 until the last consecutive day that the employee has worked from day 1. For instance, this range for Alex should be “B2:B27”. b) Create a sub that takes an employee name as input from the user, checks to ensure the name is valid (returns error message if the name is not one of the 7 names) and if the name is valid then calls on function employee to get the range of consecutive days that the employee has worked starting day 1 and then computes the sum of the number of hours the employee has worked over consecutive days since day. This sum should then be written in a new row under the same data with row name “Cons Days” c) Create a sub that builds a table somewhere on the right side of the data in the same worksheet using range variables. The table should include the name of the 7 individuals and the average, median, and standard deviation of the number of the number of hours each individual has worked over the consecutive days the employee has worked starting day 1. I want you to use for loops for this part. You may use call on built in functions of Excel in VBA to perform calculations for average, median, and standard deviation.
Exercise 4. The worksheet “Hours” (see attached Excel file) contains the number of hours that 7 employees have worked over the past 100 days. Some employees have not worked in some days.
a) Create a function named employee that takes an employee name as parameter and returns the range of cells starting day 1 until the last consecutive day that the employee has worked from day 1. For instance, this range for Alex should be “B2:B27”.
b) Create a sub that takes an employee name as input from the user, checks to ensure the name is valid (returns error message if the name is not one of the 7 names) and if the name is valid then calls on function employee to get the range of consecutive days that the employee has worked starting day 1 and then computes the sum of the number of hours the employee has worked over consecutive days since day. This sum should then be written in a new row under the same data with row name “Cons Days”
c) Create a sub that builds a table somewhere on the right side of the data in the same worksheet using range variables. The table should include the name of the 7 individuals and the average, median, and standard deviation of the number of the number of hours each individual has worked over the consecutive days the employee has worked starting day 1. I want you to use for loops for this part. You may use call on built in functions of Excel in VBA to perform calculations for average, median, and standard deviation.
Please give proper explanation and typed answer only.
Step by step
Solved in 4 steps
