Step 8: Set the final average where we want to get from. Step 7: Now select the range of those cells which has the values. Step 6: Open a For-Next loop as shown below. Step 5: Here our target is 50 Mins, so set the Target variable with this value. Step 4: Define another variable as Integer to set the Target we want to achieve. First will be used to track the current final average and second will be used to link the reference cell where we want the output.
Step 3: Now again define 2 more variables using DIM with data type Range. Step 2: Define a variable by which we can count from where we need to start Goal Seek as Long. Step 1: In Module, write the subprocedure for VBA Goal Seek as shown below. And we need to find how much time each employee needs to take to maintain the average completion time as 50 Mins or less. The target time is set for completing that activity is 50 Mins. 5 employees do the same activity from Monday to Friday. In this example, we have data of Turn Around Time (TAT or time to complete the activity) of some employee of a team who performs the same task every day.
This means, if that employee has managed to achieve even 81% of accuracy on Friday, then he will easily be able to achieve the accuracy of 90%. Step 6: Now once done, we will compile the code and run it. We will see cell C6 has the Changing Cell value as 81. Here that cell is C6 with cell reference to Friday. Step 5: Now, at last, select the range of that cell, where we need to see the required accuracy to be achieved. Step 4: Now select Goal Seek function and as per syntax, select the target value which we need to achieve. Step 3: As per syntax, we will select the Range cell which contains the current average to be fed.
Step 2: Now write the Sub procedure of VBA Goal Seek as shown below.
Step 1: Open Module from the Insert menu tab as shown below. In this example, where an employee’s accuracy of work in being monitored. Now, as per syntax of Goal Seek, we have Range cell which has the current value to feed and Target value which is the average accuracy of 90% which is to be achieved by that employee.
0 kommentar(er)
