Week 4 Final Assignment >> Excel Skills for Business: Intermediate II
1. We have been given a vehicle spare parts ordering spreadsheet that has many errors. This is in the file attached here.
By first activating the top left cell and selecting Error Checking, and Next, count the number of cells with suspected errors in them. Remember to count the first error found. Report the number here:
11
2. Does the number reported above represent the number of cells we need to repair? Explain.
- Yes, there is a one-to-one correspondence between errors counted to cells in need of repair
- No, sometimes errors produce other errors in a chain for a particular calculation.
3. Activate the cell M2, one of the cells containing an error, then select Trace Dependents. What happens and why?
- Nothing except for a ping. There are no cells that depend on this cell as an input.
- Arrows appear from the cell, with at least one being red indicating the error.
- Nothing except for a ping because that cell is not in error.
4. There are 3 (other) cells that should agree with the value in cell B2. Which cells are they?
- D2
- G2
- L17
- C2
- L1
- A1
5. What is the name given to the type of checking being done between the cells in question 4?
- Error matching
- Tracing precedents
- Tracing precedents and error checking options
- Cross-checking (totals and subtotals)
6.Is there another group of cells that should agree in their value? If so, list their addresses:
- Yes: H2, M2, Q2 and U2 only.
- Yes: H2 and M17 only.
- No.
- Yes: H2, M2, Q2 and U2 as well as the group F2, L2, P2 and T2.
7. Activate H2 and use the Trace Error to find the root cause of the error report in that cell. Was there anything to edit in cell H2 itself?
- H111 had a spelling error (syntax error), H2 itself did not need to be changed.
- H2 had a spelling error (syntax error) that had to be changed.
- H2 and H111 both contained errors that had to be corrected.
- H2 did not need to be changed.
8. Activate M2, then, by repeated use of the Trace Precedents, determine the number of generations behind the result. Report the answer here.
03
9. Go ahead and fix all errors. And provide the values of the cells requested.
The cells with an obvious error message are not the only cells with errors. Look back to your answers to questions 4-6. Do all of the cells that should have the same value actually do so?
What is in cell B2 after the errors have been fixed?
2823
10. What is in cell H2 after the errors have been fixed?
Use the format ###.##. Do not include a currency symbol or thousands separator.
8290886.79
11. What is in cell M16 after the errors have been fixed?
Use the format ###.##. Do not include a currency symbol or thousands separator.
80291.17
12. We wish to protect this spreadsheet from further errors after we pass it on, as our duty of care. The data resides in columns B to G.
What facility would allow us to protect the formulas in column H as well as the summary tables to the right of this?
- Hide Sheet
- Protect Sheet
- Add a comment
13. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column B:
- Dropdown list
- Date only
- Whole number
- Positive value
14. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column C:
- Dropdown list
- Date only
- Whole number
- Positive value
15. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column D:
- Dropdown list
- Date only
- Whole number
- Positive value
16. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column E:
- Dropdown list
- Date only
- Whole number
- Positive value
17.The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column F:
- Dropdown list
- Date only
- Whole number
- Decimal
18. The errors found were all due to user input. Data Validation could have been used to minimise their occurrence.
State the type of Data Validation that might have been used for each of the columns requested:
Column G:
- Dropdown list
- Date only
- Whole number
- Positive value