CS 100 Spreadsheets

Unit 2: Creating More Effective Spreadsheets

Lesson 6: Working With Formulas and Functions

How do you figure out what formulas and functions to use?

The first step in understanding what formulas to use is understanding what formulas are available. You can of course define your own formulas from the primitive operators (+,=,*,/) that are supported, but for most calculations you will want to use the built-in functions (SUM(), AVG(), etc.). The best way to find out what functions are defined by Excel is to select the Function... command form the Insert menu. This will bring up the Paste Function dialog box pictured below:

From the image above you can see that the functions are grouped by category and when a function is selected in the scrolling window on the right, a short description for that function is displayed below. Also note that for each function definition, example parameters are given (in the example above PMT has the following parameters: rate, nper, pv, fv, type). These example parameters also help define what the function does and how it is used.

The remainder of this objective will look at the most popular non-financial functions. Financial functions are covered in a separate concept. If you understand the functions presented here you will be well on your way to solving most problems requiring formulas. You should also browse through the list of available functions in Excel so you understand what is available. Functions not covered here will have many of the same characteristics.

AVERAGE(RangeRef...)
The AVERAGE() function computes the average of a number of values. Valid reference values for AVERAGE() are cell references, numbers, range references, or formulas. See the online help topic to see how blank cells are counted.

STDEV(RangeRef...)
STDEV() computes the standard deviation of the values given as input. STDEV() has the same valid reference values as AVERAGE() and SUM().

AND(logicalvalue,...) OR(logicalvalue,...)
AND() and OR() are useful for combining expressions within an IF() function. For example, =IF(OR(A1>70,EXACT(A2,"Pass")),"Graduate","Don't Graduate"). This function returns "Graduate" if A1 is greater than 70 or A2 contains the value "Pass."
SUM(RangeRef...)
The SUM() function computes the total of all the values given as parameters. SUM() has the same valid reference values as AVERAGE().

VLOOKUP(LookupValue,RangeRef,ColNum)
VLOOKUP() is used to implement reference tables. This function is covered in a future lesson.

IF(logical_test_value,value_if_true,value_if_false)
IF() is a powerful function used to conditionally display a value. For example, =IF(A1>10000,6%,5%) would evaluate to .06 if A1 has a value greater than 10,000, and .05 otherwise. If you were keeping track of student grades with a spreadsheet and wanted to display a letter grade in place of a numeric grade, this is the function you would use.