Welcome to the Wonderful World of Excel Formulas

1. Welcome to the Wonderful World of Excel Formulas OH, NO! Not MATH !

2. Choose a Lesson or Quiz topic: Basic Formulas Functions All About Ranges Advanced Formulas If Formulas Formulas Quiz Functions Quiz Range Quiz Advanced Quiz If Quiz

3. Got numbers? You say you put numbers in an Excel spreadsheet, but now you need to do some calculations with them.

4. Simple Formulas You enter a formula into the cell where you want a calculation�s answer to be.

5. Let�s enter some numbers In cell B6, you key:=1+2 * 3 � 1

6. Excel Math Operators

7. Now let�s calculate with numbers already entered

8. Check this out

9. What else can we do?

10. What is the big deal?

11. Now your turn! Take the Formulas Quiz!

12. Quiz Question #1 What mathematical operations is indicated by an asterisk ( * )?

13. Yes! You knew that the asterisk means Multiplication!

14. Too bad! You might want to go back and check the Math Operations table!

15. Try another one� What symbol is used in spreadsheets to represent division?

16. Right! You knew that the backslash ( / ) is the symbol for Division.

17. Oh no� If you had looked on the Math Operation table, you would have seen that the operator for Division is the backslash ( / )

18. Quiz Question #2 What value will result from this formula? =10 � 4 / 2

19. Correct! You knew that because of the order of operations, you had to divide 4 by 2 first, then subtract the result from 10.

20. OOPS! Did you forget to use the Order of Operations?!

21. Try again� What value will result from the formula?=10 * (4 + 2)

22. You got it! You remembered that using parentheses forces the addition to go first

23. Oops again� The answer would be 60

24. Quiz Question #3 Given the following values: D6 = 4 D7 = 40 D8 = 3 What is the result given by the formula?= D6 * D7 + D8 * D6 * 2

25. You got it right! You used the values in the cells and the correct order of operations to get the answer !

26. Got this one wrong� Remember that a cell address refers to the value stored there

27. How about this one? Given the values: D6 = 14 D7 = 5 D8 = 7 What is the result given by the formula? = D6 / (D8 � D7)

28. Right! You used the values in the cells and the correct order of operations to get the answer !

29. Wrong again!? The answer is 7

30. Quiz Question #4 Write a formula to add the values in cells E6 and F6, then multiply the result by 10%.

31. You are good at this! You knew you had to use parentheses to force the addition to be done first.

32. Having trouble? You forgot that all formulas need to begin with an equals sign! Otherwise Excel doesn�t know you want it to do the math for you!

33. Try another one Write a formula to subtract the value in cells E6 from the value in F6, then multiply the result by 50%.

34. Good Job! You knew you needed parentheses to force the subtraction to be done first.

35. This one was tricky You needed parentheses to force the subtraction to be done first.

36. Bonus Question Write a formula to average the values in cells E8, E9, and E10.

37. Congratulations! You knew you needed to add the three values, then divide by 3 (the number of values)

38. Not so fast! You needed to add the three values, then divide by 3 (the number of values)

39. Think about this� What if you needed to add a column of 10 numbers?

41. What are Functions?

42. About ranges�

43. A range in a row Cells may be all in one row, for example (B8:F8) (note that the row number stays the same and the column letter changes)

44. A range in a column Cells may be all in one column, for example (C8:C17) (note that the column letter stays the sameand the row number changes

45. A range as block of cells Cells could be in a block of cells, for example (B7:E11)

46. A range as a list A range (list) of non-adjacent cells is listed with commas between them, as in (B20, B28, B37)

47. How do you use functions? A more efficient formula to add that column of 10 numbers would be: =SUM(B3:B12)

48. How to use functions

49. Common Functions

50. Let�s practice In cell B19, enter a function formula to add up the employee sales values.

51. Another example Average the row of sales figures for Cellular Phones

52. One more� Enter a formula function to find the minimum sales value

53. Now your turn! Take the Functions Quiz!

54. Quiz Question #1 Write a formula that uses the SUM formula to add a row of 6 values.

55. Right! You remembered all the parts:

56. Not right� You must have forgotten one of the parts:

57. Give this one a shot Write a formula that uses the AVERAGE function to average a column of 5 sales values

58. Good one! You chose the correct function

59. Not right again� You needed to

60. Quiz Question #2 Write a formula that counts the number of values there are in a column of eight numbers.

61. Yay for you! You chose the correct function

62. Nope� You needed to

63. Try again Write a formula that finds the largest value in a row of 4 values.

64. Yes! You chose the MAX function to find the largest value

65. No, not right You needed to

66. Quiz Question #3 Write a formula that uses a function to add the values in cells B8, C9, and D10

67. You are right! You knew to

68. Oops! Remember that nonadjacent cells in a range (list) must be separated by commas

69. How about another one� Write a formula that uses a function to average the values in cells D8, D18, and D28

70. Right this time! You knew to

71. Oops again�

72. Quiz Question #4 Write a formula that uses a function to add a range of 5 values and then subtracts the result from another value.

73. Correct! You knew to

74. Incorrect

75. One more time Write an efficient formula using a function to find 10% of the average of three values.

76. Good for you! You knew the AVERAGE function is the most efficient way to average the cells in the range

77. Too Bad

78. Bonus Question Write a formula that uses functions to find two averages and adds them together.

79. You are the Function Champ! You did great! You knew that you needed two separate AVERAGE functions to add together.

80. This one was tricky

81. Glossary - page 1 Argument � the data (values, cells, range) operated on by a function Cell � the intersection of a row and column in a worksheet Cell address � the address/name of a cell, also referred to as cell reference Column � a vertical group of cells, designated by a letter

82. Glossary - page 2 Formula � instructions that tell a spreadsheet how to perform calculations; can contain values, math operators, cell references, cell ranges and functions Function � a reserved word to be used in a formula; it is preprogrammed to perform a specific operation or group of operations List � of non-adjacent cells is listed with commas between them Example: (F8, G12, H10)

83. Glossary - Page 3 Range � a group of cells adjacent cells, specified to be acted on by a command Example (C3:F3) Row � horizontal group of cells, designated by a number Value � a number, either integer or decimal

84. Congratulations!! You know how to create basic Excel formulas!