A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

1 | BEFORE YOU BEGIN, YOU NEED TO GO TO THE FILE MENU (NOT AT TOP OF SCREEN) THAT IS DIRECTLY BELOW "SUMMERCORE GOOGLE SS EXERCISES" AND MAKE A COPY! | |||||||||||||||||||

2 | ||||||||||||||||||||

3 | Skill Name | Description | Sample | Now You Do Your Work on this Side -- the Yellow rectangles show where you need to work | ||||||||||||||||

4 | ||||||||||||||||||||

5 | (1) Adding numbers | A formula such as =sum(C6:C8) is used to add up a block of numbers. Note that there no spaces in the formula. Also, upper vs lowercase is irrelevant! | (1) Adding numbers | Please type in the correct formula in the yellow cell so that you get the SUM of the 3 numbers above it. | ||||||||||||||||

6 | 33 | 33 | ||||||||||||||||||

7 | 34 | 34 | ||||||||||||||||||

8 | 35 | 35 | ||||||||||||||||||

9 | 102 | |||||||||||||||||||

10 | ||||||||||||||||||||

11 | (2) Averaging numbers | A formula such as =AVERAGE(C12:C14) is used to add up a block of numbers. Note that there no spaces in the formula. Also, upper vs lowercase is irrelevant! | (2) Averaging numbers | |||||||||||||||||

12 | 23 | 23 | ||||||||||||||||||

13 | 33 | 33 | ||||||||||||||||||

14 | 53 | 53 | ||||||||||||||||||

15 | 1033 | 1033 | ||||||||||||||||||

16 | 285.5 | |||||||||||||||||||

17 | ||||||||||||||||||||

18 | (3) Sorting words or names | You can put a list of words or names in order by choosing the right feature under the DATA menu. First you need to HIGHLIGHT or SELECT the desired cells. Then you choose the fourth feature under DATA where it says SORT RANGE by ???. Note that the first two items under DATA sort the entire row and not just a few cells. | (3) Sorting words or names | |||||||||||||||||

19 | Georgia | Larry | ||||||||||||||||||

20 | Kevin | Kevin | ||||||||||||||||||

21 | Larry | Robert | ||||||||||||||||||

22 | Mariah | Georgia | ||||||||||||||||||

23 | Robert | Mariah | ||||||||||||||||||

24 | ||||||||||||||||||||

25 | ||||||||||||||||||||

26 | (4) Sorting Rows | In our next exercise, we have data spanning rows from A to F. So this time you are going to HIGHLIGHT or SELECT the rectangle from A27 to F31 and then choose the fifth feature under DATA called SORT RANGE. | (4) Sorting Rows | |||||||||||||||||

27 | Georgia | Bird | 33 mom | French Lick | IN | |||||||||||||||

28 | Kevin | McHale | 32 | Hibbling | MN | |||||||||||||||

29 | Larry | Bird | 33 | French Lick | IN | |||||||||||||||

30 | Mariah | Bird | 33 daughter | Naples | FL | |||||||||||||||

31 | Robert | Parish | 00 | Shreveport | LA | |||||||||||||||

32 | ||||||||||||||||||||

33 | (5) Making text wrap in 2 cells | In this example, we have typed a sentence about the fact that Larry Bird was born on Pearl Harbor Day Dec 7th and made it wrap into 2 cells B31 and C31. | (5) Making text wrap in 2 cells | In this example, we have typed a sentence about the fact that Larry Bird was born on Pearl Harbor Day Dec 7th and you need to make it wrap into 2 cells B31 and C31. | ||||||||||||||||

34 | ||||||||||||||||||||

35 | ||||||||||||||||||||

36 | ||||||||||||||||||||

37 | ||||||||||||||||||||

38 | (6) Making text fancy | On the right you will see samples of what you can do make text look fancy -- Larry1 is red, Larry2 has a color fill, Larry 3 has both and Larry 4 has a border and is centered both left/right and up/down. Larry 5 is wrapped in one cell so you need to choose WRAP for the example on the right. | (6) Making text fancy | See if you make Larry 1 thru Larry 4 match the ones on the left | ||||||||||||||||

39 | Larry1 | Larry1 | ||||||||||||||||||

40 | Larry2 | Larry2 | ||||||||||||||||||

41 | Larry3 | Larry3 | ||||||||||||||||||

42 | Larry4 | Larry4 | ||||||||||||||||||

43 | Larry5 is wrapped in the one cell | Larry5 is wrapped in just one cell. | ||||||||||||||||||

44 | ||||||||||||||||||||

45 | ||||||||||||||||||||

46 | ||||||||||||||||||||

47 | ||||||||||||||||||||

48 | ||||||||||||||||||||

49 | (7) Making your own formula: the TIP | To multiple a cell by 18%, you use a formula such as =C48*1.18 which increases the number by 18%. Some people prefer the formula =C48+(C48*.18) | (7) Making your own formula: the TIP | See if you can find out what the $65 meal would cost with a 19% tip | to reveal answer click on the black rectangle, then go to the FILL BUCKET and choose RESET | |||||||||||||||

50 | restaurant bill | $65.00 | restaurant bill | $65.00 | ||||||||||||||||

51 | with 18% tip | $76.70 | with 19% tip | $77.35 | ||||||||||||||||

52 | ||||||||||||||||||||

53 | ||||||||||||||||||||

54 | to reveal answer click on the black rectangle, then go to the FILL BUCKET and choose RESET | |||||||||||||||||||

55 | (8) Making your own formula: INTEREST RATE | (8) Making your own formula: INTEREST RATE | ||||||||||||||||||

56 | Here is a typical spreadsheet problem: $100 is deposited in the bank for 9 years at 8% interest. Calculate how much money will be in the bank at the end of each of the next 9 years | $100.00 | See if you can do the same. One trick for the years is to put =F54+1 into cell F55. Do you see why? Yes, it will increase 2013 by 1 so if you then fill this formula down, you will get all your years! | Once you have the years, you can focus on the bank interest which is surprisingly the same as the tip in the restaurant. | $100.00 | You put the formula = 1.08*G53 into cell G54 and then push ENTER or RETURN. You then fill this formula down | ||||||||||||||

57 | 2013 | $108.00 | 2013 | |||||||||||||||||

58 | 2014 | $116.64 | ||||||||||||||||||

59 | 2015 | $125.97 | ||||||||||||||||||

60 | 2016 | $136.05 | ||||||||||||||||||

61 | 2017 | $146.93 | ||||||||||||||||||

62 | 2018 | $158.69 | ||||||||||||||||||

63 | 2019 | $171.38 | ||||||||||||||||||

64 | 2020 | $185.09 | ||||||||||||||||||

65 | 2021 | $199.90 | ||||||||||||||||||

66 | ||||||||||||||||||||

67 | (9) Formats of Numbers | So you now in the previous example can see how money can be formatted. Here are a variety of numerical formats, each one applied to the same number 12.345 | (9) Formats of Numbers | Now you try it using highlighting one cell at a time and choosing the FORMAT menu and the subchoice of NUMBER. Note that the NUMBER subchoice itself has items called OTHER CURRENCIES and MORE FORMATS. | ||||||||||||||||

68 | normal | 12.345 | 12.345 | |||||||||||||||||

69 | scientific notation | 1.23E+01 | 12.345 | |||||||||||||||||

70 | money | $12.35 | 12.345 | |||||||||||||||||

71 | per cent | 1234.50% | 12.345 | |||||||||||||||||

72 | date | 1/11/1900 | 12.345 | |||||||||||||||||

73 | euros | € 12.35 | 12.345 | |||||||||||||||||

74 | yen | ¥ 12.35 | 12.345 | |||||||||||||||||

75 | date | 12/7/2013 | 12/7/2013 | |||||||||||||||||

76 | date altered | Dec-7 | 12/7/2013 | |||||||||||||||||

77 | and altered again | 12-07-13 | 12/7/2013 | |||||||||||||||||

78 | ||||||||||||||||||||

79 | (10) Conditional Formatting | (10) Conditional Formatting | ||||||||||||||||||

80 | You can make a cell change to a different color based on how big it is. in this example, we have program these numbers to be green or yellow or red according to the rules of green=90 or above, yellow = 80 to 89.9 and red below 80. | It all begins by going to CONDITIONAL FORMATTING under the FORMAT menu. You need to program 3 rules. Click the 75 in cell F77, then click CONDITIONAL FORMATTING and see if you can program the one rule "if value is over 90 then green" | picture on right should help you | |||||||||||||||||

81 | 75 | 75 | ||||||||||||||||||

82 | 85 | 85 | ||||||||||||||||||

83 | 95 | 95 | ||||||||||||||||||

84 | 78 | 78 | ||||||||||||||||||

85 | 88 | 88 | ||||||||||||||||||

86 | 98 | 98 | ||||||||||||||||||

87 | ||||||||||||||||||||

88 | ||||||||||||||||||||

89 | (11) Random Picking | The formula =RANDBETWEEN(#,#) lets you pick a random number between the lower one and the higher one. In this example, each formula is (1.6) to simlulate a die. Then the formula below adds up the two dice to get a number between 2 and 12. | (11) Random Picking | Try to generate the two individual dice here and then add them. Did you know that 7 is the most likely outcome when you roll two dice? | ||||||||||||||||

90 | One Die | 4 | ||||||||||||||||||

91 | Another Die | 3 | ||||||||||||||||||

92 | TOTAL | 7 | ||||||||||||||||||

93 | ||||||||||||||||||||

94 | (11) Random Picking of a Name | You can use random numbers to make the computer pick a random student. | See if you can make this happen using the last names of the same 5 people. | |||||||||||||||||

95 | Georgia | Bird | ||||||||||||||||||

96 | Kevin | McHale | ||||||||||||||||||

97 | Larry | Bird | ||||||||||||||||||

98 | Mariah | Bird | ||||||||||||||||||

99 | Robert | Parish | ||||||||||||||||||

100 | Pick a random number from 1 to 5 | 2 |