Electronic Spreadsheet (Advanced) Class – X

Part-1

In the first part of the lectures related to the Unit : Electronic Spreadsheet (Advanced) , the topics that have been covered include Analysing Data Using Scenarios and Goal Seek.

Short Notes

Data Consolidate :

  • Data consolidation provides a way to combine data from two or more ranges of cells into a new range while running one of several functions (such as Sum or Average) on the data. 
  • During consolidation, the contents of cells from several sheets can be combined into one place.  The effect is that copies of identified ranges are stacked with their top-left corners at the specified result position, and the selected operation is used in each cell to calculate the result value.

Sub Totals :

  • Subtotals are implemented in two ways : The SUBTOTAL Function
  • Data > Subtotals from the menu bar
  • A more comprehensive solution is to create subtotals using Data > Subtotals from the Menu bar, which opens the Subtotals dialog box. 
  • Subtotal creates totals for the data arranged in an array that is, a group of cells with labels for columns. 
  • Using the Subtotals dialog box, you can select up to three arrays, then choose a statistical function to apply to them. 
  • When you click on OK, Calc adds subtotal and grand total rows to the selected arrays using the Result and Result2 cell styles to differentiate those entries. 
  • By default, matching items throughout your array will be gathered together as a single group above a subtotal.

Using “What if” Scenarios

  • The scenario is 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 are 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.

Click Below to watch the Video Containing the Explanation of the above Content

Questions and Answers

Q 1 . Define the following :

a) Subtotals

b) Consolidating

Ans.  a) Subtotals: Subtotal adds data arranged in an array—that is, a group of cells with labels for columns and/or rows. It is available in Data Tab, using the Subtotals dialog, you can select arrays, and then choose a statistical function such as Count, Min, Max, etc. to be applied on arrays. For efficiency, one must choose up to three groups of arrays to which to apply a function.

b) Consolidating Data: Consolidate provides a graphical interface for copying data from one range of cells to another, then running one of a dozen functions on the data. During consolidation, the contents of cells from several sheets can be combined in one place.

Q 2. What are the two ways of creating subtotals?

Ans.  Subtotals can be implemented in two ways :

  1. The SUBTOTAL function
  2. Data > Subtotals from the menu bar

Q 3. Write the syntax of  SUBTOTAL.

Ans. SUBTOTAL(Function; Range)

Q 4. What is the shortcut key to Insert a function?

Ans. CTRL +F2

Q 5. Which menu has the Consolidate option?

Ans. Data menu

Q 6. Write the name of the function indexes for available functions.

Ans.  The function indexes are given in the following table :

Function index

Functions

1

Average

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV

8

STDEVP

9

SUM

10

VAR

11

VARP

Q 7. Describe “What if” scenarios.

Ans.  The scenario is 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 are 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.  

Q 8. Name the two aspects of scenarios that can be altered independently.

Ans.   Steps are as follows :

  • Scenario properties
  • Scenario cell values (the entries within the scenario border)

Q 9. Write the steps to create a scenario.

Ans. Steps are as follows :

  • Select the cells with the data.
  • Click on Tools>Scenarios. They create scenarios dialog box appears. Give some name to the scenario.  Press OK after the scenario dialog is filled.

Click on the following links to view the next parts of this Unit :

error: Content is protected !!
Back to Top