Embedded Files
Consolidating data
Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet. In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that you can update easily
Combines data from one or more independent cell ranges and calculates a new range using the function that you specify.
Enter Name and Marks in PWT-1,PWT-2 and PWT-3 sheets and Click on Consolidate sheet tab
Select Consolidate from data menu
Function
Select the function that you want to use to consolidate the data.
Consolidation ranges
Displays the cell ranges that you want to consolidate.
Source data range
Specifies the cell range that you want to consolidate with the cell ranges listed in the Consolidation ranges box. Select a cell range in a sheet, and then click Add. You can also select a the name of a predefined cell from the Source data range list.
If you select link to source data, any values modified in the source range are automatically updated in the target range.
Sub Total
The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data.
What if Analysis Tools: Scenarios, Goal Seek and Solver
Scenarios
Scenarios are a tool to test “what-if” questions.
Each scenario is named, and can be edited and formatted separately.
When you print the spreadsheet, only the contents of the currently active scenario is printed.
A scenario is essentially a saved set of cell values for your calculations.
You can easily switch between these sets using the Navigator or a drop-down list which can be shown beside the changing cells.
For example, if you wanted to calculate the effect of different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. Formulas that rely on the values changed by your scenario are updated when the scenario is opened. If all your sources of income used scenarios, you could efficiently build a complex model of your possible income.
Goal Seek
Usually, you run a formula to calculate a result based upon existing values. you can discover what values will produce the result that you want. using Goal Seek.
Goal Seek tool need three parameters
The formula cell: This is the cell contains the formula we want to resolve
The target value: The required value that we want our formula to to resolve to
The variable cell. The cell that contains the unknown data that we want to find a value that meets the requirements.
Currently the average grade is 62 but he needs at least a 65 to pass the class. What is the minimum score he must achieve in the Information Technology exam in order to pass the class? Instead of manually trying random values he can use Goal Seek to find out what grade he needs on the final exam to pass.
Select the cell containing the Formula (in our example the Average grade) and Choose Tools > Goal Seek from the main menu.
In the Goal Seek dialog window fill in the parameters. The Formula cell is already filled in. Type in the Target value and click the Variable cell to autofill the cell reference value in the dialog.
3. Goal Seek now calculates and displays the Result in another pop up dialog. Choose Yes to insert the result to the variable cell
Now the student knows that he must achieve a grade 84 or greater in the IT exam in order to pass the course.
There is no guarantee that Calc will be able to find a solution. If this happens, you will be notified in the Goal Seek Status dialogue box.
We want to find find the rate of interest for amount of Interest 500 ?
Solver
Solver option under Tools menu amounts to a more elaborate form of Goal Seek.
The difference is that the Solver deals with equations with multiple unknown variables.
It is specifically designed to minimize or maximize the result according to a set of rules that you define.
Each of these rules defines whether an argument in the formula should be greater than, lesser than, or equal to the figure you enter. If you want the argument to remain unchanged, you enter a rule that the cell that contains it should be equal to its current entry.
For arguments that you would like to change, you need to add two rules to define a range of possible values: the limiting conditions. For example, you can set the constraint that one of the variables or cells must not be bigger than another variable, or not bigger than a given value. You can also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed).
Once you have finished setting up the rules, click the Solve button to begin the automatic process of adjusting values and calculating results. Depending on the complexity of the task, this may take some time..
Page updated
Google Sites
Report abuse