Page 149 - IT_V1.0_Class10
P. 149

The Subtotal command is used to automatically create groups and apply inbuilt functions like SUM, COUNT,
                 AVERAGE, etc., to summarise 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 use filters.

                 For  example,  the  Subtotals  command can  be  used  to  calculate  the  number  of  Jerseys  of  different  sizes
                 required for players.
                 To insert Subtotal values into a sheet:

                 Step 1:  Select the range of cells with values for which Subtotal function needs to be implemented. Ensure that
                         the columns must have labels. The subtotal function has a graphical interface and is accessible from
                         Data menu. Using the Subtotals, you can select arrays, and then choose a statistical function to apply
                         to them. For efficiency, you can choose up to three group of arrays to which a function can be applied.

                 Step 2:  Click on Data menu. Select Subtotals option. Subtotals dialog box appears on the screen.

                 Step 3:  In 1st Group tab, from the Group by drop down box, select the column for which subtotal is required
                         or if you wish to select all the columns check in Select all columns. If the content of the selected
                         column changes, the subtotals will automatically be updated.

                 Step 4:  In the Calculate subtotals for box, select the columns for which subtotal needs to be calculated.

                 Step 5:  In Use function box, select the desired function to calculate the subtotals. (Here, we have used
                         Count function.)
                 Step 6:  Click on the OK button.








                                                                  3

                                                                  4
                                                                                                                      5
                  1


                                                              2





                                                                                                                      6



                                                   rd
                 You  can  use  the  2   Group  and  3   Group  tabs  to  group  the  data in  further  levels in same  sheet. For
                                   nd
                 example, if we add colour and cost to the sheet, we can make another group showing sum of cost of
                 jerseys colour-wise for each size.
                 First group finds the count of sizes available, second group displays colour and third group displays cost for
                 the different jersey sizes available.







                                                                        Analyse Data using Scenarios and Goal Seek   147
   144   145   146   147   148   149   150   151   152   153   154