myCBSEguide

CBSE Class 10 Information Technology

Revision Notes
Electronic Spreadsheet (Advanced)
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.

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

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 1/7

 myCBSEguide

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 Figure 2.3.

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:

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 2/7

 myCBSEguide

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 64 want to subtotal.
5. In the Use function box, select the function that you want to use to calculate the subtotals.
6. Click OK.

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.

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

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 3/7

 myCBSEguide

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.

4. Optionally add some information to the Comment box. The example shows the default comment. This
information is displayed in the Navigator when you click the Scenarios icon and select the desired scenario.
5. Optionally select or deselect the options in the Settings section. See below for more information about these
options.
6. Click OK to close the dialog. The new scenario is automatically activated. You can create several scenarios
for any given range of cells.

Using Goal Seek

Usually, you run a formula to calculate a result based upon existing values. By contrast, using Goal Seek option
under Tools menu, you can discover what values will produce the result that you want.
To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections
for each quarter of the forthcoming year. She knows what the company’s total income must be for the year to
satisfy stockholders. She also has a good idea of the company’s income in the first three quarters, because of the
contracts that are already signed. For the fourth quarter, however, no definite income is available. So how
much must the company earn in Q4 to reach its goal? The CFO can enter the projected earnings for each of the
other three quarters along with a formula that totals all four quarters. Then she runs a goal seek on the empty
cell for Q4 sales, and receives her answer.
Other uses of goal seek may be more complicated, but the method remains the same. Only one argument can be
altered in a single goal seek.

Goal Seek example

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 4/7

 myCBSEguide

To calculate annual interest (I), create a table with the values for the capital (C), number of years (n), and
interest rate (r). The formula is I = C*n*r.
Let us assume that the interest rate r of 7.5% and the number of years n (1) will remain constant. However, you
want to know how much the investment capital C would have to be modified in order to attain a particular
return I. For this example, calculate how much capital C would be required if you want an annual return of
100,000
or it can be left blank; for number of years n, 1; for interest rate r, 7.5%). Enter the formula to calculate the
interest I in another cell. Instead of C, n, and r use the reference to the cell with the corresponding value. In our
example, this would be = B1*B2*B3.

1. Place the cursor in the formula cell (B4), and choose Tools > Goal Seek.
2. On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
3. Place the cursor in the Variable cell field. In the sheet, click in the cell that contains the value to be changed,
in this example it is B1.
4. Enter the desired result of the formula in the Target value field. In this example, the value is 15000. The
figure below shows the cells and fields.

5. Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the result in
the cell with the variable value. The result is shown below.

Using the Solver

Solver option under Tools menu amounts to a more elaborate form of Goal Seek. The difference is that the

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 5/7

 myCBSEguide

Solver deals with equations with multiple unknown variables. It is 68 specifically designed to minimize or
maximize the result according to a set of rules that you define.
Each of these rules sets up whether an argument in the formula should be greater than, lesser than, or equal to
the value 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, you can adjust the argument and
the results by clicking the Solve button.

Solver example
Let's say you have
1000?
To find the answer using Solver:

1. Enter labels and data: ● Row labels: Fund X, Fund Y, and total, in cells A2 thru A4.
Column labels: interest earned, amount invested, interest rate, and time period, in cells B1 thru E1.
Interest rates: 8 and 12, in cells D2 and D3.
Time period: 1, in cells E2 and E3.
Total amount invested: 10000, in cell C4.
2. Enter an arbitrary value (0 or leave blank) in cell C2 as amount invested in Fund X.
3. Enter the formulae given below:
In cell C3, enter the formula C4-C2 (total amount - amount invested in Fund X) as the amount
invested in Fund Y.
In cells B2 and B3, enter the formula for calculating the interest earned (see below).
In cell B4, enter the formula B2+B3 as the total interest earned. 69 Figure2.9: Example setup for
solver

4. Choose Tools Solver. The solver dialog opens as shown in Figure 2.10.

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 6/7

 myCBSEguide

5. Click in the Target cell field. In the sheet, click in the cell that contains the target value. In this example it is
cell B4 containing total interest value.
6. Select Value of and enter 1000 in the field next to it. In this example, the target cell value is 1000 because
your target is a total interest earned of $1000. Select Maximum or Minimum if the target cell value needs to
be one of those extremes.
7. Click in the By changing cells field and click on cell C2 in the sheet. In this example, you need to find the
amount invested in Fund X (cell C2).
8. Enter limiting conditions for the variables by selecting the Cell reference, Operator and Value fields. In this
example, the amount invested in Fund X (cell C2) should not be greater than the total amount available (cell
C4) and should not be less than 0.
9. Click OK. A dialog appears informing you that the Solving successfully finished. Click Keep Result to enter
the result in the cell with the variable value. The result is shown below.

Copyright © myCBSEguide.com. Mass distribution in any mode is strictly prohibited. 7/7