Question

Concept Check Questions Chapter 6

1. You have delete data in a speardsheet that you are going to analyze? What are some tips for working with the data set to help prevent and correct errors? pg 329
2. You are talking with a colleague who mentions that the SUBTOTAL function performs sum, average, count, or the other function and questions why a person would use that function when the SUM, AVERAGE and COUNT functions already exist. What is the difference between using the SUBTOTAL to sum data and using the SUM Function? PG 343
3. What type of data would work well for the row and column labels within a PivotTable? What are some examples? Pg 347
4. Describe two benefits of using an excel pivotchart rather than an excel pivotable? Pg362

Answer 1

1Q Answer:

Data plays an important role in any organization and to protect such data and send the consolidated data without any errors are very important because data can be used at any point of time for future requirement and reference. There are different types of errors in the sheet need to analyse and to Prevent and correct errors are:

· General error: The sheet is well defined and well organized automatically to throw some general errors. One has to identify such errors and correct those errors before sharing or sending anyone such data

· Value error: If a range contains different data types than including such data at one group in the formulae shows an error compute same data types for accurate results

· Cell error: The cells are very small where large data in the cell are not properly displayed in such case wide the cell by dragging with the mouse to expand the cell to view the complete data

· Syntax errors: if there is any error with the syntax, that is formulae is not written than it displays error. Such errors can be corrected by taking the help of formulae options by refereeing to the syntax we can correct those errors

· Reference: when a cell is not properly referred than this type of errors occurs. This might cause even with the deleted cells or improper range. Than refer to that particular cell correct them and give the range these errors can be corrected

· Number error: this happens because of the invalid numeric values in the cell. Other than numbers if any symbols are used such error throws. Identify such numbers and remove those values to rectify the error

· Division error: If any of the cell contains null or zero than these errors occurs. Look for such cells and change the value other than null to get the proper value

2Q Answer: Every function has its own features and operations as per given instructions. Needs to understand the difference between purpose of such functions. So that it will be easy while computing and using right formulae for right data analysis

SUBTOTAL	SUM, AVERAGE & COUNT
This kind of function is used when sum, average and count are the solution for that data	These are the very common and general functions that are used for computing the data
The subtotal contains different types of functions in it	Where here are only individual functions that perform single task
If someone want to find the grand total in the data where in between there are so many subtotal in that columns than sum will not work in such case	Only it sums the defined range with proper values in it else consider as null or throw error
But here in subtotal we can add number of new cells in between	If any new cell of data is added than it is not suitable because it is already summed.
less time compared with sum and easy to provide input values	It’s a time taking process in taking input values
Even for average it works better than the actual average	Average works for simple range for complicated data this function is not suitable
There is an options which can consider work by including and excluding hidden values. Example =SUBTOTAL (9, range) for including hidden values and =SUBTOTAL (109, range) for excluding hidden values. This function provides dynamically results.	Whereas the sum function works and shows static results.

3Q Answer:

The pivot table is one of the best tool option for data processing which displays the summarised data for huge data sets. This can be worked for grouping any type of data field. The plug and play system for a selected data. The rows can be turned into columns and columns can be changed to rows. Its operations are huge like we can sort, organise, rearrange and count etc. functions can be easily performed through the pivot table tool. The best in grouping the data and to view the summarized results and output time is very less for sending detailed report for any individual or group.

Example of Pivot table:

· Good at Providing summarise data in an easy understanding

· Rearrange the data as per the requirement

· Organize the data as per the business need

· Less time consume in consolidating the summary of the data

4Q Answer:

Examples of Pivot Table	Examples of Pivot Chart
This is one of the powerful tool for providing and reporting summarise data	Whereas this is good at showing the summary report in a pictorial format
Easy to derive the summary for any given data set. Irrespective of its size or data. Query large data in many ways by using pivot table	Without data it is no use for representing. Only good at showing the details for people who are not good at metrics can understand simply by looking the pictorial charts
It is very user-friendly and less time consuming in proving the summary results.	Attractive way of displaying the data
Good at data representation and data analysis. Even querying makes more easy through pivot table	Good at visualization
We can change rows to columns and columns as rows as per our requirement in the summary table	Provide conclusions

Pivot table can do multiple benefits in representing and reporting data for better understanding by generating a summary.