New Perspectives Excel 2019 | Module 3 - End of Module Project 1 #final-part
pls hlp with these qns:
Create lookup functions to complete the summary section. In cell I6, create a formula using the VLOOKUP function to display the number of hours worked in the selected week. Look up the week number in cell I5 in the range A17:G20, and return the value in the 2nd column. Use absolute references for cell I5 and the range A17:G20.
Copy the formula from cell I6 to the range I7:I11 and then edit the formula in cell I7 to return the value in the 3rd column, the formula in cell I8 to return the value in the 4th column, the formula in cell I9 to return the value in the 5th column, the formula in cell I10 to return the value in the 6th column, and the formula in cell I11 to return the value in the 7th column.
Add formulas to complete the balances section. In cell K8, create a formula using the SUM function that calculates the total of the range D17:D20 and subtracts it from the value in cell J8.
In cell K9, create a formula using the SUM function that calculates the total of the range E17:E20 and subtracts it from the value in cell J9.
In cell K10, create a formula using the SUM function that calculates the total of the range F17:F20, subtracts it from the value in cell J10, adds the total of the range G17:G20, and subtracts 160.
Copy the formula in cell J11 to cell K11.
In cell J16, create a formula using the AVERAGE function that calculates the average of the range B9:F12.
In cell J17, create a formula using the MAX function that returns the maximum value from the range B9:F12.
In cell J18, create a formula using the MIN function that returns the minimum value from the range B9:F12.
Mover the cursor towards corner of the cell, it will show + sign, then hold it and drag it up to I11. Now, click I7 change the column value to 3. i.e. in I7, enter this formula =VLOOKUP($I$5,$A$17:$G$20,3). Similarly, in I8, I9, I10, and I11 change 'em to 4, 5, 6, and 7th column.
Click K8 cell, enter =J8-SUM(D17:D20) formula.
Click cell K9, enter =J9-SUM(E17:E20)
Click cell K10, enter =J10-SUM(F17:F20)+SUM(G17:G20)-160
Click cell J11, copy the formula. And then click cell K11 and paste the formula in top bar (shown in video below). This will preserve the border style.
Here u go!
=VLOOKUP($I$5,$A$17:$G$20,2).=VLOOKUP($I$5,$A$17:$G$20,3). Similarly, in I8, I9, I10, and I11 change 'em to 4, 5, 6, and 7th column.=J8-SUM(D17:D20)formula.=J9-SUM(E17:E20)=J10-SUM(F17:F20)+SUM(G17:G20)-160=AVERAGE(B9:F12)=MAX(B9:F12)=MIN(B9:F12)Here is the video explanation: