New Perspectives Excel 365/2021 | Module 3: End of Module Project 2 Sandhills-Sand River Water District
PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_2021_EOM3-2_FirstLastName_1.xlsx as NP_EX365_2021_EOM3-2_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file NP_EX365_2021_EOM3-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
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 2024 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 2nd 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 3rd column, the formula in cell J22 to return the value in the 4th column, the formula in cell J23 to return the value in the 5th column, the formula in cell J24 to return the value in the 8th column, and the formula in cell J25 to return the value in the 9th column.
Your workbook should look like the Final Figures on the following pages. The value in cell C5 has been intentionally blurred as it will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Final Figure 1: 2024 Water Projections Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning. All Rights Reserved.