Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    Calc Lesson 1 Notes-1

    Uploaded by

    thangamgopalakrishnan

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Calc Lesson 1 Notes-1

    Uploaded by

    thangamgopalakrishnan
    4 views4 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views4 pages

    Calc Lesson 1 Notes-1

    Uploaded by

    thangamgopalakrishnan

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 4

    UNIT – 2

    ELECTRONIC SPREADSHEET
    LESSON – 1
    DATA ANALYSIS AND GOAL SEEK
    1. INTRODUCTION

    Data analysis is the process to extract useful information for making


    effective decisions. The spreadsheet is one of the best software used
    for data analysis. The spreadsheet component in LibreOffice is
    known as Calc includes several tools for data analysis.
    2. We need to check the following before consolidating data
    a) Data types of the data which we want to consolidate should
    be same in all spreadsheet.
    b) We should match the labels from all the sheets which are
    used for consolidating.
    c) The first column should be same in all spreadsheet on the
    basis of which the data is to be consolidated.
    3. Steps to consolidate:
    a) Open the spreadsheet which has the data to be
    consolidated.
    b) Data > Consolidate > Consolidate dialog box
    c) Choose the required function from the drop-down list like
    sum, average etc.
    d) Add the ranges from the sheets which is to be consolidated.
    e) Select the cell under ‘copy results to’ where we want to
    display result.
    f) Select the appropriate checkbox under options and click ok
    button.
    NOTE:

    1. ‘Consolidate by’ has two options Row labels and Column labels.
    Check row label or column label or both if you want to consolidate
    it by matching the label.
    2. If “link to source data” is checked, then it will keep on updating
    the data of the consolidate sheet automatically if there is any
    change made in the selected ranges.
    4.Groups and Outline
    It is used to create group of rows and columns together so that
    one can collapse (-) to hide it or expand (+) it using a single click
    on it.
    Steps to do so,
    ✓ Select the data to be grouped.
    ✓ Data > Group and outline > Group.
    ✓ Choose Rows or Columns on the basis of which you want to
    group the data and click ok.
    NOTE:

    ✓ Short cut to group data is F12 and to ungroup in Ctrl + F12


    5.Subtotals
    The subtotal tool in calc creates the group automatically and
    applies common functions like sum, average on the grouped data.
    We can use any type of function for each column as per the
    requirement of data analysis.
    Steps for applying subtotal,
    ✓ Open the spreadsheet, Data > Subtotals
    ✓ Choose the column in the Group by list which is to be
    used for grouping the data.
    ✓ Select the column by clicking the checkbox under
    calculate subtotals for, to create subtotals.
    ✓ Click on ok button.
    NOTE:

    ✓ Columns should have labels (column heading) on which we


    are applying subtotals.
    ✓ After performing subtotal, u can see outline to the left of the
    row number.
    ✓ This outline shows the hierarchical structure where we can
    expand or collapse the data by clicking on ‘+’ or ‘-‘sign
    respectively.
    ✓ If u want to remove the outline feature then click on Data >
    Group and Outline > Remove Outline.
    6. What-if scenarios
    ✓ What-if scenario is a set of values that can be used within the
    calculations in the spreadsheet.
    ✓ It can be used in the beginning of any project to optimise the
    output.
    ✓ This tool is used to predict the output while changing the inputs
    and thus one can choose the best plan.

    STEPS TO CREATE SCENARIO


    1) Select the cells which contains values in the sheet that
    needs to be changed.
    2) Choose Tools > Scenario
    3) Enter a name for the new scenario
    4) Click on ok button.

    7. What-if Analysis Tool


    ✓ What-if tool uses Data > Multiple operations and is a planning
    tool for what-if questions.
    ✓ The Multiple operations tools create a formula array to display
    the list of results on a list of 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.
    ✓ What-if analysis tool is very helpful to know how much profit
    we earn for a particular product for a series of selling units.
    STEPS FOR WHAT-IF ANALYSIS TOOL
    1) Enter the data in the cells and then enter a formula to calculate a
    result from the data entered.
    2) Create an array of input values on the basis of which the output is to
    be generated.
    3) Select the cell range of input array and output array.
    4) Click on Data > Multiple operations.
    5) Enter the cell address where we applied formula in the dialog box.
    6) Enter the address of variable cell.
    7) Click on ok.
    8. GOAL SEEK
    ✓ In general, we fill in the values in the cells and then create
    formula on these values to get the required result.
    ✓ Goal seek helps in finding out the input for the specific output.

    For ex,
    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 to check how many marks he has to score in the 5th subject to
    get the required percentage.

    STEPS FOR GOAL SEEK


    1) Enter the values and write the formula in the cell.
    2) Choose Tools > Goal Seek
    3) Enter the address of cell in the “Formula cell “box.
    4) Enter the address of cell whose value is to be predicted or unknown
    in “variable cell” box.
    5) Enter the target value and click on ok.
    9. SOLVER
    ✓ Solver tool is more elaborate form of Goal seek.
    ✓ While goal seek seals with single input variable to reach to a specific
    output, 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.
    STEPS TO DO SOLVER
    ✓ Enter the data in a spreadsheet.
    ✓ Select the cell, on which you have applied the formula.
    ✓ Select Tools > Solver option.
    ✓ Enter the required details in the dialog box and click on solve
    option.
    ✓ You will see it will give the desired result.

    You might also like