Data Analysis in Spreadsheets 2007
Data Analysis in Spreadsheets 2007:
Data Analysis in Spreadsheets 2007:A worksheet can be use to analyze important data. Some important techniques of dta analysis are as follows:
What- if Analysis
What- if analysis is a process of using a spreadsheet to text the effect of alterative scenarios on numeric results. Almost all worksheets provide the facility of what–if analysis. so The user can change one part of a formula or cell and check how it affects the remaining worksheet. So the user can create a table that automatically calculates the results based on any number of assumptions.
The user can also create several scenarios or versions of the saem spreadsheet. Each scenario may contain different assumptions reflected in its formulas.
Goal seeking is a technique to find values for one or more cells that make the result o formula equal to specified value. The following figure shows result of PMT formula in B7
In above figure, maximum monthly payment that can be affords is 1200. The amount written in B7 and the cell becomes staring point. The bank offers an interest rate of 8.25%
Over 15 years. So The total mortgage shown in B3 can be calculated from monthly payment, years paid and interest rate. The figure shows that user can afford a mortgage of $123,693.
Spreadsheet provides the facilities to create, arrange and select data in lists. They do not provides database capabilities as relational database management programs. However, they can handle many simple databases task such as sorting. They records can be sorted in a specific manner based on certain criteria such as by date or alphabetically etc.
Pivot Tables are interactive worksheet tables that are use to summarize data. They display information in summary form.
Pivot tables divide data in a list of categories according to the requirement. They are useful to look at different views of the same data. The orientation of can be changed by simply dragging different row and column buttons to different location on pivot table.So And an organization may collect a large volume of data over time. A pivot table is a useful tool for spotting patterns and trends that emerge over time.
The process of arranging data or records in a sequence is known as sorting. Sorts are not case sensitive. For example, words beginning with “a” and “A” will be mix together in the sort. The data can be sorts in two ways:
- Ascending sort
Ascending sort is a sorting technique in which the smallest data is placed at first position and the largest data is placed at last position.
- Descending Sort
Descending sort is a sorting technique in which the largest data is placed at first position and the smallest data is placed at last position.
The following procedure is use to sort by multiple columns
- Highlight the cells, rows, or columns to be sorted.
- Select Data > sort form the menu bar.
- and Select the first column for sorting from drop-down menu and choose ascending or descending.
- Select second column and if necessary the third sort column from Then By menus.
- If highlighted cells included the heading in first row, mark My list has header row. The first row will remain at the top of the worksheet. C
- Click options buttons for special non-alphabetic or numeric sorts such as months and days of week.
- Click OK.