1107241834360331

ANALYSING DATA
• Analysing data is the process to extract

useful information for making effective
decisions.
• The spreadsheet is one of the best
software used for data analysis.
• It is used to retrieve, correlate, explore and
visualise data to identify patterns, trends
and relationships.
Consolidating Data
• Consolidate is a function used to
combine information from multiple
sheets of the spreadsheet into one
place
• to summarize the information. It is
used to view and compare variety of
data in a single spreadsheet for
identifying trends and relationships.
Steps to consolidate the data
• Open the spreadsheet which has the data to
be consolidated.
• Step 2. Create a new sheet where the data
has to be consolidated.
• Step 3. Choose Data > Consolidate option
that will open Consolidate dialog
• Step 4. Observe that the default function
“Sum” is seen in the Function dropdown.
You will be able to see the list of functions
Steps to consolidate the data
• Step 5. Choose the required function from the
drop-down list.
• Step 6. The sources data range list contains
existing named ranges to quickly select from
that. But if the source
• range is undefined, then click and select the
range from the sheet which is to be
consolidated.
• Step 7. Click on Add to add this range under
Steps to consolidate the
data
• Step 8. Repeat steps 6 and 7 to add more
sheets to be consolidated.
• Step 9. Remember to check the target
range specified under Use ‘Copy results
to’. If it is not mentioned, then click on
the cell of sheet where the final data has
to be produced.
• Step 10. Click on Options that will list two
checkbox under Consolidate by “Row labels”
and “Column labels”and “Link to source
data” under Options . The option consolidate
by rows and columns are checked to
consolidate data as per row labels and
column label. Link to source data is checked
to make the modification automatically in
the consolidated (target) sheet while making
any changes in the source data.
• Step 11. Finally click on OK button.
SUBTOTA
LS
• The Subtotal tool in Calc creates the group
automatically and applies Common functions
like sum, average on the grouped data.
• One can use any type of summary function
for each column as per the requirement of
data analysis.
• It can group subtotals by using category and
sorts them in ascending or descending order
so that one need not to use filters.
What-if Scenarios
• What-if Scenarios What-if scenario is a
set of values that can be used within
the calculations in the spreadsheet. A
name is given to the scenario and
several scenarios can be created on
the same sheet.
• Follow the following steps to create scenario.
• Step 1. Select the cells which contains
values in the sheet that needs to be changed.
To select multiple cells, hold Ctrl key and click
on the cell to be selected.
• Step 2. Choose Tools>Scenarios will open
scenario dialog window as shown in Fig. 4.17.
• Step 3. Enter a name for the new scenario
and leave the other fields unchanged.
• Step 4. Click on OK button.
• Step 5. This will create a new scenario which
is activated automatically.
What-if Analysis Tool
• What-if Analysis Tool What-if tool uses Data > Multiple
Operations and is a planning tool for what-if questions.
• In this, the output is not shown in the same cells,
whereas it uses a drop-down list to display the output
depending upon the input.
• The Multiple Operations tool creates a formula array
to display the list of results applying the formula on a
list of alternative values used in the formula
• This tool uses two arrays of cells, one
array contains the input values and the
second array uses the formula and display
the result.
• It is useful to check in the beginning to
understand from the output for the
efficiency.
• Step 1. Enter the data in the cells and then enter a formula to
calculate a result from values in other cells.
• Step 2. Create an array of input values on the basis of which the
output is to be generated using the formula.
• Step 3. Select the cell range of input array and output array
• Step 4. Click on Data>Multiple Operations will display the multiple
operations dialog window as shown in Fig. 4.22.
• Step 5. Enter the cell address in the Formulas box from the sheet
which contains the formula
• Step 6. Now, enter the cell address of the cell which is a variable and
is used in the formula in Column input cell box.
• Step 7. Click on OK will generate all the possible outputs based on the
formula
Goal Seek
• It general we fill in the values in the cells
and then create formula on these values to
get the required result. T
• o predict the output, we keep on changing
all the input values to obtain the desired
output.
• Let us take a very simple example to
perform the backward calculation to find out
an input depending upon the specific output
• A student has received marks in 4 subjects and
has to appear for the 5th subject and plans an
aggregate as 70. So, he can use goal seek tool to
check how many marks he has to score in the 5th
subject to get the required percentage
• Goal seek dialog window helps in finding out the
input for the specific output. For example, if you
want to know the number of units produced to get
the desired output then use Goal seek analysis
tool.
• Step 1. Enter the values in the worksheet.
• Step 2. Write the formula in the cell where the
calculation has to be used.
• Step 3. Place the cursor in the formula cell, choose Tools
> Goal Seek.
• Step 4. the Formula cell box will have the correct
formula.
• Step 5. Place the cursor on the Variable cell box and click
on the cell that contains the value to be changed.
• Step 6. Enter the desired result in the Target value box.
• Step 7. Click on OK button.
Answer the following questions
1.Define the terms (a) Consolidate function (b)
What-if analysis (c) Goal seek
2. Give one point of difference between (a) Subtotal
and What-if (b) What-if scenario and What-if tool
3. Give any two advantages of data analysis tools.
4. Name any two tools for data analysis.
5. What are the criteria for consolidating sheets?
6. Which tool is used to create an outline for the
selected data?

1107241834360331

Uploaded by

Copyright:

Available Formats

1107241834360331

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

1107241834360331

Uploaded by

Copyright:

Available Formats

ANALYSING DATA

• Analysing data is the process to extract

You might also like