The concepts of programming in Alice can apply to many of the mainstream computer applications that people use today, regardless if they’re computer engineers, secretaries, analysts, lab researcher, etc. Over the next few modules we will be learning basic functions in Google Spreadsheets (you can use them interchangeably with Microsoft Excel and other spreadsheet software) and how to utilize them in everyday life. We will refer to this project as Excel as that’s the most common application that people use for spreadsheet management and every function is compatible.
In this module, we will learn basic functions in utilizing Excel to increase productivity. In addition, we will be building a grade calculator, where the user can input grades and have Excel calculate what they have and what they need to get a certain grade in the class.
Things you will learn in this module:
1. How to run functions in Excel
2. Relative and Absolute Addressing
3. Sum and Average Functions
4. Count, CountA, CountIf
5. Holding Constant and Values with $$
6. VLOOKUP
Vocabulary:
1. Formula – An evaluator that performs calculations or other actions on the data in the worksheet. To create one just type the = sign before doing anything else.
2. Functions – Builtin formulas that can be used to take values, perform operations, and return results (similar to alice!)
3. Relative Addressing – Cell references that change relative to the direction in which the formula is copied
4. Absolute Addressing – Cell references that do not change when formula gets copied. Requires holding constant and values
5. Syntax – Grammatical structure of a formula
6. Average Function – Calculates the average in a range of cells
7. Min Function – Calculates the minimum value in a range of cells
8. Max Function – Calculates the maximum value in a range of cells
9. Median Function – Calculates the midpoint value in a range of cells
10. Sum – Calculates the sum of values in a range of cells
11. Count – a function that counts the number of cells that contains numbers and return that number.
12. Counta - a function that counts the number of nonempty cells and return that number
13. Countif – a function that counts the number of cells that meets the specified criteria and returns that number
14. VLOOKUP – a function that searches the first column of a range of cells and return a value from any cell on the same row of the range.