New Perspectives Excel 2019 | Module 3: End of Module Project 2 Ramandeep Singh 1

Subject:MS ExcelPrice:20.01 Bought7

Share With

New Perspectives Excel 2019 | Module 3: End of Module Project 2

Ramandeep Singh

1. Ying Chou is an intern with the Sandhills-Sand River Water District. Ying has collected data on

precipitation and reservoir storage and wants to use Excel to perform some analysis on her data.

Switch to the 2021 Water Projections worksheet. In cell C5, insert the TODAY function to record the

current date.

2. In cell J4, create a formula using the MONTH function to display the numerical month based on the date in cell C4

3. In cell J5, create a formula without using a function that subtracts the value in cell J4 from the number 12

.

4. Use the values in the range C9:D9 to extend the list of years to the range E9:G9.

5. Use the values in the range B10:B11 to extend the list of reservoir numbers to the range B12:B15.

6. Use AutoFill to fill the range C11:J15 with the formatting from the range C10:J10.

7. In cell G10, create a formula that uses the AVERAGE and ROUND functions to calculate the average of cells C10:F10 and round the result to 1 decimal place. Copy the formula you created to the range

G11:G15.

8. In cell J10, create a formula without using a function that divides the value of cell I10

by the value of cell H10 . Copy the formula you created to the range J11:J15.

9. In cell G16, create a formula that uses the AVERAGE and ROUND

functions to calculate the average of the range G10:G15 and round the result to 1

decimal place. Copy the formula to cell H16.

10. In cell D21, enter a formula without using a function that multiplies the value of cell

D22 by 2.83 and then adds the value of cell D19 to the result.

11. Use Goal Seek to identify the value for cell D22 that results in a value of

100 for cell D21.

12. Create lookup functions to complete the summary section. In cell J20, create a formula using the

VLOOKUP function to display the measured precipitation for the selected reservoir. Lookup the reservoir number (cell J19 ) in the range B10:J15 , and return the value in the 2 nd column number. Use absolute references for cell J19 and the range B10:J15.

13. Copy only the formula from cell J20 to the range J21:J25, and then edit the formula in cell J21 to return the value in the 3 rd column, the formula in cell J22 to return the value in the 4 th column, the formula in cell J23 to return the value in the 5 th column, the formula in cell J24 to return the value in the 8 th column, and the formula in cell J25 to return the value in the 9 th column.

Final Figure: create a 2021 Water Projections