Electronic Spreadsheet (Advanced) | Information Technology for Class 10 PDF Download

 Page 1


 
 
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK 
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. 
1) Open the worksheet that contains the cell ranges to be consolidated. 
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The 
Consolidate dialog box is shown in Figure 2.2. 
 
Figure 2.1: Consolidate option under Data Menu 
 
 
      Figure 2.2: Consolidate Dialog box defining the data to be consolidated 
Page 2


 
 
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK 
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. 
1) Open the worksheet that contains the cell ranges to be consolidated. 
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The 
Consolidate dialog box is shown in Figure 2.2. 
 
Figure 2.1: Consolidate option under Data Menu 
 
 
      Figure 2.2: Consolidate Dialog box defining the data to be consolidated 
 
 
 
3) If the Source data range list contains named ranges, you can select a source cell 
range to consolidate with other areas. 
If the source range is not named, click in the field to the right and either type a 
reference for the first source data range or use the mouse to select the range on the 
sheet. (You may need to move the Consolidate dialog to reach the required cells.) 
4) Click Add. The selected range now appears on the Consolidation ranges list. 
5) Select additional ranges and click Add after each selection. 
6) Specify where you want to display the result by selecting a target range from the Copy 
results to box. 
If the target range is not named, click in the field next to Copy results to and enter 
the reference of the target range or select the range using the mouse or position the 
cursor in the top left cell of the target range. 
7) Select a function from the Function list. The function specifies how the values of the 
consolidation ranges are linked. The Sum function is the default setting. 
Most of the available functions are statistical (such as AVERAGE, MIN, MAX, 
STDEV), and the tool is most useful when you are working with the same data over 
and over. 
8) Optionally click More in the Consolidate dialog to display additional settings. 
• Select Link to source data to insert the formulas. This generates the results in the 
target range instead of the actual results. If you link the data, any values modified in 
the source range are automatically updated in the target range. The corresponding cell 
references in the target range are inserted in consecutive rows, which are 
automatically ordered and then hidden from view. Only the final result, based on the 
selected function, is displayed. 
• Under Consolidate by setting, select either Row labels or Column labels, if the cells 
of the source data range are not to be consolidated corresponding to the identical 
position of the cell in the range, but instead according to a matching row label or 
column label. To consolidate by row labels or column labels, the label must be 
contained in the selected source ranges. The text in the labels must be identical, so 
that rows or columns can be accurately matched. If the row or column label does not 
match any that exist in the target range, it will be appended as a new row or column. 
9) Click OK to consolidate the ranges. 
10) If you are continually working with the same range, then you probably want to use 
Data > Define Range to give it a name. Define Range option is available under the 
Data Menu. 
Page 3


 
 
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK 
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. 
1) Open the worksheet that contains the cell ranges to be consolidated. 
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The 
Consolidate dialog box is shown in Figure 2.2. 
 
Figure 2.1: Consolidate option under Data Menu 
 
 
      Figure 2.2: Consolidate Dialog box defining the data to be consolidated 
 
 
 
3) If the Source data range list contains named ranges, you can select a source cell 
range to consolidate with other areas. 
If the source range is not named, click in the field to the right and either type a 
reference for the first source data range or use the mouse to select the range on the 
sheet. (You may need to move the Consolidate dialog to reach the required cells.) 
4) Click Add. The selected range now appears on the Consolidation ranges list. 
5) Select additional ranges and click Add after each selection. 
6) Specify where you want to display the result by selecting a target range from the Copy 
results to box. 
If the target range is not named, click in the field next to Copy results to and enter 
the reference of the target range or select the range using the mouse or position the 
cursor in the top left cell of the target range. 
7) Select a function from the Function list. The function specifies how the values of the 
consolidation ranges are linked. The Sum function is the default setting. 
Most of the available functions are statistical (such as AVERAGE, MIN, MAX, 
STDEV), and the tool is most useful when you are working with the same data over 
and over. 
8) Optionally click More in the Consolidate dialog to display additional settings. 
• Select Link to source data to insert the formulas. This generates the results in the 
target range instead of the actual results. If you link the data, any values modified in 
the source range are automatically updated in the target range. The corresponding cell 
references in the target range are inserted in consecutive rows, which are 
automatically ordered and then hidden from view. Only the final result, based on the 
selected function, is displayed. 
• Under Consolidate by setting, select either Row labels or Column labels, if the cells 
of the source data range are not to be consolidated corresponding to the identical 
position of the cell in the range, but instead according to a matching row label or 
column label. To consolidate by row labels or column labels, the label must be 
contained in the selected source ranges. The text in the labels must be identical, so 
that rows or columns can be accurately matched. If the row or column label does not 
match any that exist in the target range, it will be appended as a new row or column. 
9) Click OK to consolidate the ranges. 
10) If you are continually working with the same range, then you probably want to use 
Data > Define Range to give it a name. Define Range option is available under the 
Data Menu. 
 
 
 
The data from the consolidation ranges and target range are saved when you save the 
worksheet. If you later open a worksheet in which consolidation has been defined, 
this data will again be available. 
 
Creating Subtotals 
SUBTOTAL is a function listed under the Mathematical category when you use the 
Function Wizard (Insert > Function). Because of its usefulness, the function has a 
graphical interface. It is accessible from Data menu as shown in Figure2.3.   
 
 
Figure 2.3: Subtotal option under Data Menu 
SUBTOTAL, totals/adds data arranged in an array—that is, a group of cells with labels 
for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then 
choose a statistical function to apply to them. For efficiency, you can choose up to three 
groups of arrays to which to apply a function. When you click OK, Calc adds subtotals 
and grand totals to the selected arrays, using the Result and Result2 cell styles for them. 
Steps to insert subtotal values into a sheet: 
1) Ensure that the columns have labels. 
2) Select the range of cells that you want to calculate subtotals for, and then choose Data 
-> Subtotals. 
3) In the Subtotals dialog (Figure 2.4), in the Group by box, select the column that you 
want to add the subtotals to. If the contents of the selected column change, the 
subtotals are automatically recalculated. 
4) In the Calculate subtotals for box, select the columns containing the values that you 
Page 4


 
 
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK 
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. 
1) Open the worksheet that contains the cell ranges to be consolidated. 
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The 
Consolidate dialog box is shown in Figure 2.2. 
 
Figure 2.1: Consolidate option under Data Menu 
 
 
      Figure 2.2: Consolidate Dialog box defining the data to be consolidated 
 
 
 
3) If the Source data range list contains named ranges, you can select a source cell 
range to consolidate with other areas. 
If the source range is not named, click in the field to the right and either type a 
reference for the first source data range or use the mouse to select the range on the 
sheet. (You may need to move the Consolidate dialog to reach the required cells.) 
4) Click Add. The selected range now appears on the Consolidation ranges list. 
5) Select additional ranges and click Add after each selection. 
6) Specify where you want to display the result by selecting a target range from the Copy 
results to box. 
If the target range is not named, click in the field next to Copy results to and enter 
the reference of the target range or select the range using the mouse or position the 
cursor in the top left cell of the target range. 
7) Select a function from the Function list. The function specifies how the values of the 
consolidation ranges are linked. The Sum function is the default setting. 
Most of the available functions are statistical (such as AVERAGE, MIN, MAX, 
STDEV), and the tool is most useful when you are working with the same data over 
and over. 
8) Optionally click More in the Consolidate dialog to display additional settings. 
• Select Link to source data to insert the formulas. This generates the results in the 
target range instead of the actual results. If you link the data, any values modified in 
the source range are automatically updated in the target range. The corresponding cell 
references in the target range are inserted in consecutive rows, which are 
automatically ordered and then hidden from view. Only the final result, based on the 
selected function, is displayed. 
• Under Consolidate by setting, select either Row labels or Column labels, if the cells 
of the source data range are not to be consolidated corresponding to the identical 
position of the cell in the range, but instead according to a matching row label or 
column label. To consolidate by row labels or column labels, the label must be 
contained in the selected source ranges. The text in the labels must be identical, so 
that rows or columns can be accurately matched. If the row or column label does not 
match any that exist in the target range, it will be appended as a new row or column. 
9) Click OK to consolidate the ranges. 
10) If you are continually working with the same range, then you probably want to use 
Data > Define Range to give it a name. Define Range option is available under the 
Data Menu. 
 
 
 
The data from the consolidation ranges and target range are saved when you save the 
worksheet. If you later open a worksheet in which consolidation has been defined, 
this data will again be available. 
 
Creating Subtotals 
SUBTOTAL is a function listed under the Mathematical category when you use the 
Function Wizard (Insert > Function). Because of its usefulness, the function has a 
graphical interface. It is accessible from Data menu as shown in Figure2.3.   
 
 
Figure 2.3: Subtotal option under Data Menu 
SUBTOTAL, totals/adds data arranged in an array—that is, a group of cells with labels 
for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then 
choose a statistical function to apply to them. For efficiency, you can choose up to three 
groups of arrays to which to apply a function. When you click OK, Calc adds subtotals 
and grand totals to the selected arrays, using the Result and Result2 cell styles for them. 
Steps to insert subtotal values into a sheet: 
1) Ensure that the columns have labels. 
2) Select the range of cells that you want to calculate subtotals for, and then choose Data 
-> Subtotals. 
3) In the Subtotals dialog (Figure 2.4), in the Group by box, select the column that you 
want to add the subtotals to. If the contents of the selected column change, the 
subtotals are automatically recalculated. 
4) In the Calculate subtotals for box, select the columns containing the values that you 
 
 
want to subtotal. 
5) In the Use function box, select the function that you want to use to calculate the 
subtotals. 
6) Click OK. 
 
Figure 2.4: Setting up subtotals 
If you use more than one group, then you can also arrange the subtotals according to 
choices made on the dialog‘s Options page (Figure 2.5), including ascending and 
descending order or using one of the predefined custom sorts defined under Tools menu 
as Tools-> Options-> OpenOffice.org Calc-> Sort Lists. 
 
 
Figure 2.5: Choosing options for subtotals 
Page 5


 
 
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK 
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. 
1) Open the worksheet that contains the cell ranges to be consolidated. 
2) Choose the Consolidate option under the Data menu as shown in Figure2.1. The 
Consolidate dialog box is shown in Figure 2.2. 
 
Figure 2.1: Consolidate option under Data Menu 
 
 
      Figure 2.2: Consolidate Dialog box defining the data to be consolidated 
 
 
 
3) If the Source data range list contains named ranges, you can select a source cell 
range to consolidate with other areas. 
If the source range is not named, click in the field to the right and either type a 
reference for the first source data range or use the mouse to select the range on the 
sheet. (You may need to move the Consolidate dialog to reach the required cells.) 
4) Click Add. The selected range now appears on the Consolidation ranges list. 
5) Select additional ranges and click Add after each selection. 
6) Specify where you want to display the result by selecting a target range from the Copy 
results to box. 
If the target range is not named, click in the field next to Copy results to and enter 
the reference of the target range or select the range using the mouse or position the 
cursor in the top left cell of the target range. 
7) Select a function from the Function list. The function specifies how the values of the 
consolidation ranges are linked. The Sum function is the default setting. 
Most of the available functions are statistical (such as AVERAGE, MIN, MAX, 
STDEV), and the tool is most useful when you are working with the same data over 
and over. 
8) Optionally click More in the Consolidate dialog to display additional settings. 
• Select Link to source data to insert the formulas. This generates the results in the 
target range instead of the actual results. If you link the data, any values modified in 
the source range are automatically updated in the target range. The corresponding cell 
references in the target range are inserted in consecutive rows, which are 
automatically ordered and then hidden from view. Only the final result, based on the 
selected function, is displayed. 
• Under Consolidate by setting, select either Row labels or Column labels, if the cells 
of the source data range are not to be consolidated corresponding to the identical 
position of the cell in the range, but instead according to a matching row label or 
column label. To consolidate by row labels or column labels, the label must be 
contained in the selected source ranges. The text in the labels must be identical, so 
that rows or columns can be accurately matched. If the row or column label does not 
match any that exist in the target range, it will be appended as a new row or column. 
9) Click OK to consolidate the ranges. 
10) If you are continually working with the same range, then you probably want to use 
Data > Define Range to give it a name. Define Range option is available under the 
Data Menu. 
 
 
 
The data from the consolidation ranges and target range are saved when you save the 
worksheet. If you later open a worksheet in which consolidation has been defined, 
this data will again be available. 
 
Creating Subtotals 
SUBTOTAL is a function listed under the Mathematical category when you use the 
Function Wizard (Insert > Function). Because of its usefulness, the function has a 
graphical interface. It is accessible from Data menu as shown in Figure2.3.   
 
 
Figure 2.3: Subtotal option under Data Menu 
SUBTOTAL, totals/adds data arranged in an array—that is, a group of cells with labels 
for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then 
choose a statistical function to apply to them. For efficiency, you can choose up to three 
groups of arrays to which to apply a function. When you click OK, Calc adds subtotals 
and grand totals to the selected arrays, using the Result and Result2 cell styles for them. 
Steps to insert subtotal values into a sheet: 
1) Ensure that the columns have labels. 
2) Select the range of cells that you want to calculate subtotals for, and then choose Data 
-> Subtotals. 
3) In the Subtotals dialog (Figure 2.4), in the Group by box, select the column that you 
want to add the subtotals to. If the contents of the selected column change, the 
subtotals are automatically recalculated. 
4) In the Calculate subtotals for box, select the columns containing the values that you 
 
 
want to subtotal. 
5) In the Use function box, select the function that you want to use to calculate the 
subtotals. 
6) Click OK. 
 
Figure 2.4: Setting up subtotals 
If you use more than one group, then you can also arrange the subtotals according to 
choices made on the dialog‘s Options page (Figure 2.5), including ascending and 
descending order or using one of the predefined custom sorts defined under Tools menu 
as Tools-> Options-> OpenOffice.org Calc-> Sort Lists. 
 
 
Figure 2.5: Choosing options for subtotals 
 
 
Using “What If” 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 content 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. 
 
Creating Scenarios 
Use Scenarios option under Tools menu to enter variable contents—scenarios—in the 
same cell.  To create a scenario: 
1) Select the cells that contain the values that will change between scenarios. To select 
multiple cells, hold down the Ctrl key as you click each cell. 
2) Choose Tools > Scenarios. 
3) On the Create Scenario dialog (Figure 2.6), enter a name for the new scenario. It‘s 
best to use a name that clearly identifies the scenario, not the default name as shown 
in the illustration. This name is displayed in the Navigator and on the title bar of the 
scenario on the sheet itself. 
 
Figure 2.6: Creating a scenario