# excel_lessons_intermediate

Document Sample

```					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
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