Docstoc

excel_lessons_intermediate

Document Sample
excel_lessons_intermediate Powered By Docstoc
					Earnings 2000

Name      January
Fred       6185.3     2783.6    7104.57     5251.37    2925.96     7134.98     3946.3      7850.45   4551.81
Tom        4512.7     2556.4     2011.8     5133.94     7722.2     4999.32    5563.66      4012.07   5844.79
Tahu       6217.6      6704      7069.9     6757.95    3077.33     5774.42      3834        2936.9   6552.76
Lee        4791.4     3626.2    2238.29     3515.37    5723.14     4256.47     6080.2      6917.77   6687.55
Tania      5250.8     3407.8    5698.56     2801.04    4260.82     6768.78    7470.82      2157.48   7883.51
Tina       5620.4     3053.1    2030.39     7684.49    6602.05     7999.19    7324.93      3419.09    2134.8
Jack       5864.6     7227.5      6470      7169.44    6357.76     2858.65    6067.53      2437.66   5544.66



                Instructions
                1 Use the drag handle to create the series January - December
                2 Centre heading "Earnings 2000" across the table
                3 Make headings bold
                4 Adjust data to fit columns
                5 Format all values to two decimal places
                6 Wrap the heading "Yearly Total" within the cell
                7 Use Auto Sum to sum yearly total for Fred
                8 Using the drag handle fill this total down
                9 Use Auto Sum to sum the column headed January
                10 Use the drag handle to fill this total across
                11 Format all totals to currency(not accounting) with two decimal places
                12 Sort into order of yearly total
                              Yearly Total
  2613    5574.46   2043.06
4991.25   4350.82   2643.62
3934.42    5195.6   7226.71
7663.42   3757.85   5153.85
5268.17   6010.55   6835.19
7131.74   2061.09   3207.47
3371.36   7393.11   7015.73
             1 Day 1        Monday         1/1/2001              0           20 Jan
                                                                 2           18


Interval 1                                                           Continue
               Interval 1   Interval 1   Interval
                                                                     the pattern   Interval
                            day          Week                                      Month
                                                      Continue
                                                      the
                                                      pattern




                                                                      Instructions
                                                                      Continue the series using the quickest method:
                                                                      either
                                                                      Highlight => Edit => Fill =>Series
                                                                      or
                                                                      Right drag by drag handle
                                                                      or
                                                                      Left drag by drag handle
                                                                      Custom format Column D to ddd d mmm yyyy
                                                                      widen column to fit the fuller date format
sing the quickest method:




       ddd d mmm yyyy and
Relative Absolute

Country NZ             Australia US           Korea      Taiwan    Hong Kong China
Currency Dollar        Dollar    Dollar       Won        Dollar    Dollar     Yuan
                  $1.00 0.814691        0.437    551.428   14.0583    3.40825   3.61242
Name     Pay
Fred        $57,964.82
Tom         $54,342.61
Tahu        $65,281.61
Lee         $60,411.52
Tania       $63,813.56
Tina        $58,268.75
Jack        $67,777.96



          Instructions
          Enter a formula into cell C8 to calculate Fred's pay in
          Australian dollars.
          Make sure that this cell can be copied across and down.
          Hint you will need two "$" signs in your formula to make
          appropriate cell references absolute.
          Copy the formula in C8 across to H8
          Copy the formulae in row 8 down to row 14 (one operation)
          Sort into Ascending order of name. (a=>z)
Possible            50
Name       Test 1         Test 2        Test 3        Test 4        Average   Maximum Minimum
Fred                38             34            45            42
Tom                 39             48            24            18
Tahu                20             44            20            25
Lee                 36             22            37            20
Tania               28             12            14            39
Tina                38             32            44            39
Jack                44             31            47            34

Possible            100
Name       Test 1         Test 2        Test 3        Test 4        Average   Maximum Minimum   Marks over 50
Fred
Tom
Tahu
Lee
Tania
Tina
Jack

                           Instructions
                           Enter a formula to calculate Average in cell F3
                           Enter a formula to calculate Maximum in cell F3
                           Enter a formula to calculate Minimum in cell F3
                           Copy these formulae down
                           Produce the second table by scaling the marks so that they are now out of 100
                           instead of 50. Use absolute references here. ie cells B2 and B12 must be used
                           rather than values 50 and 100.
                           Enter the formula into cell B14, then copy across and down.
                           Calculate marks over 50 by using the CountIF function in I14 and then copying this
                           down
  Marks over 50




ow out of 100
must be used


then copying this
Pass Calculator
Pass mark          50
Name      Test 1        Test 2        Test 3        Test 4        Average     Result
Fred               65            87            87            82       80.25   Pass
Tom                83            79            64            82          77   Pass
Tahu               62            45            49            43       49.75   Fail
Lee                66            66            84            72          72   Pass
Tania              44            83            57            50        58.5   Pass
Tina               63            42            40            40       46.25   Fail
Jack               81            86            78            51          74   Pass

                                                                  Passes               5


       Instructions
       1 Enter a function into cell F4 to calculate the average mark and copy this down.
       2 Enter an IF function into cell G4 to return Pass if the average mark is over the
         pass mark which is entered in B2. Use absolute cell references where
         necessary.
       3 Enter a CountIF function into cell G12 to return the number of passes.
                                Smoking Statistics New Zealand
Age              1976 1981 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
15–24 years        35   33     34       35       31       31       32       27       28   30   28   25   28   28   25   27   27   27   30
25–34 years        43   38     38       39       35       32       34       38       30   32   35   34   35   36   35   33   30   34   33
35–54 years        44   38     37       35       31       29       34       31       28   30   27   29   30   30   29   26   28   28   27
55+ years          35   30     29       27       24       24       22       20       20   18   18   18   19   17   19   19   16   16   16
Total Males        40   35     35       34       30       29       31       29       27   28   27   27   28   28   27   26   26   26   26
Females
15–24 years        34   35     40       40       40       37       36       39       37   34   33   33   30   34   33   36   35   30   33
25–34 years        38   34     35       34       34       34       35       35       33   32   30   36   34   33   36   35   36   31   32
35–54 years        35   32     31       29       29       32       26       28       25   28   24   27   26   25   25   26   26   26   26
55+ years          21   19     21       22       19       21       19       16       16   16   16   14   16   16   14   13   14   12   13
Total Females      32   29     31       31       30       31       28       29       27   27   26   27   26   26   26   26   27   24   25
Both Sexes
15-19 years        30   28     30       31       31       30       27       27       28   27   26   24   24   25   24   25   21   23   25
Mäori              58   56 -        -        -        -        -        -        -        51   50   52   54   52   51   49   51   49   51
Pacific people     35   33 -        -        -        -        -        -        -        32   30   33   33   34   34   32   34   33   30
European/other -    -    -    -    -    -    -    -    -                                  25   24   24   24   24   24   23   23   22   22
ethnic groups
Total Population 36   32   33   32   30   30   30   29   27                               28   26   27   27   27   27   26   26   25   26
(15+ years)



                 Instructions
                 1 Create a column chart similar to "Male 76-89" that covers Males for the period 1990 to 1999
                 2 Create a line graph similar to "Male Female 76-99" but also includes total population (purple)
                 3 Create a pie graph similar to "Female Smokers 99" for Males Smokers in 1999.
the period 1990 to 1999
s total population (purple)
                                    Smoking Trends in Males 1976 - 1989


          50


          45


          40


          35


          30
                                                                                           15–24 years
Percent




                                                                                           25–34 years
          25                                                                               35–54 years
                                                                                           55+ years

          20


          15


          10


           5


           0
               1976   1981   1983    1984       1985       1986       1987   1988   1989
                                                 Year
                                 Smoking Trends in Males and Females

             45


             40


             35


             30
Percentage




             25
                                                                                                                   Males
                                                                                                                   Females
             20


             15


             10


              5


              0
                  1976 1981 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999

                                                              Year
                  Age Grouping of Female Smokers in 1999




              55+ years
                12%

                                                            15–24 years
                                                               32%




35–54 years
   25%




                                              25–34 years
                                                 31%

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:12/1/2011
language:English
pages:14