CS 100 Spreadsheets

Unit 1: Introducing the Spreadsheet

Lesson 2: Entering Data

How do you use formulas and functions to perform calculations?

Probably the most powerful type of data you can enter into a spreadsheet cell is a formula. A formula is an equation for calculating a new value from existing values. For example, here is a simple spreadsheet which calculates a course grade:

The formula for calculating the course grade is visible in the formula bar. The course grade is calculated from the student's scores (B2,B3,B4) and the weights (C2,C3,C4) given to each type of assignment or exam. The formula contains one type of operator (addition or "+") and cell references for the operands. Formulas can contain:

Operators, such as +, -, *, /, and ^.
Numbers or constants, such as 7, 3.1415, -16.
Built-in functions, such as SUM(), AVG(), and IF().
Cell and range references, such as B2, B2:B4, and A1:B2.

In a formula the operations are the mathematical operators and built-in functions, and the operands are the numbers and cell references.

Note: In the example above the "=" which precedes the definition of the formula. Cell values that begin with "=" are treated as formulas. If you don't start a formula definition with an "=" Excel will interpret the cell value as a number or text. (If a cell contains any characters other than 0-9, the cell value is interpreted as text.) The following examples should help you understand how '"=" is used to specify a formula,

=a7*3 is a formula
5*a3 is text
"=3+a2 is text

Two other ideas you need to understand to use formulas effectively are order of evaluation and how parentheses effect the order of evaluation. See the related concept "What are formulas?" for a complete discussion of these concepts.

Note: The example in this objective also illustrates the twin purposes of a spreadsheet. If the semester is over and you already know your assignment and examine scores you can use a spreadsheet to determine your final course grade. If, on the other hand, it is mid-semester and you realize you don't have the time or energy to complete the assignments for all your classes, you can try "what if scenarios" with a spreadsheet to help you decide where to compromise.