Formulas and Functions in Excel (Mac)

Uploaded by springsteen8591 on 03.10.2010

Hi, this is Millie! Welcome to my screencast about how to use Excel’s abilities for entering,
manipulating, and storing data. Formulas, functions, and their order of operations are
the basic data manipulation tools we will cover. The first step is to enter your data.
This might be class grades on various assignments if you are a teacher. Here, I have a simple
spreadsheet comparing temperatures in Celsius and Fahrenheit. Notice it’s not all filled
in yet! We’ll learn how to do that automatically. Keep the data organized with the columns and
rows; you can create headings, like “Fahrenheit” and “Celsius” (but don’t change the
actual column and row names—make the headings in the cells themselves). Now, let’s learn
about formulas first. Formulas are user-created strings of data connected by operations, resulting
in a final value. For instance, here, if we wanted to add up a certain set of Fahrenheit
values—say 35 to 39 degrees—we could do so. The syntax is as follows: simply type
in an equals sign, followed directly by the cell addresses, which are the alphanumerical
names of the cells (in this case A7 to A11, 35 to 39), and the operations that we want
to use (addition, subtraction, division, multiplication, exponents). Parentheses are also useful.
Check out Erin Cooke’s screencast for more information on cell addresses. Let’s pull
up our formula bar for future reference. Just go to view-->formula bar, and now we can see
the formulas used to get a cell value when we click on that cell. Looks like we already
had it up. Now let’s say we want to find Celsius values using the conversion formula
from Fahrenheit to Celsius. So we’ll type the formula (using the correct syntax) and
include the appropriate cell addresses or values. For example, to calculate the Celsius
equivalent of 32 degrees Fahrenheit, I will type the formula into the first Celsius cell,
B4, right here. You might remember this formula from science class: (F-32)*5/9. Instead of
“F,” we’ll write A4, because that’s the address of the value of 32 degrees Fahrenheit.
Let’s see what we get: zero degrees Celsius. That’s right! If we were to enter the formula
in the Celsius column wrong, we would get the wrong answer. For instance, what if we
leave out the parentheses? That’s not the right answer! We need to use the right syntax
to take advantage of the order of operations. The order of operations for formulas and functions
both in Excel and in the math world is PEMDAS, which is parentheses, exponents, multiplication,
division, addition, and subtraction. Excel will automatically perform formulas and functions
in this order. Now, if we edit the formula to have parentheses, we can use it correctly
again. We know that’s correct because 32F and 0C are both freezing. You might be wondering,
do we have to type this conversion formula to find every single Celsius value? No way!
Let’s use a shortcut, called the fill handle. This will enable us to fill in a whole lot
of data at once. Grab the lower right hand corner of this Celsius
cell (this little corner’s called the fill handle)—notice how your cursor changes shape,
from this…to this. Also notice that since we’ve highlighted this cell, we can see
the formula we used to get it in the formula builder up here. Now pull down to as many
cells as you want values for, which for this is a lot, all the way to boiling, which is
212 Fahrenheit, then let go. This is really neat because the cell addresses change relative
to the row when we fill down, and relative to the column when we fill across. We can
also check this in the formula bar. For instance, to find the Celsius equivalent for 52 degrees
Fahrenheit, which is right here, the conversion formula will use row 24 instead of row 4.
Now that we have covered the basics of formulas and operations, let’s learn how we can greatly
reduce our workload and the involvedness of using Excel’s operational capabilities.
To do this, we will use functions. Functions are built-in formulas that Excel already knows,
so we don’t have to type out a formula for them. We are going to learn four simple functions:
average, minimum, maximum, and sum. All follow the same function syntax: an equals sign directly
followed by the function name in ALL CAPS, directly followed by a summary, in parentheses,
of all the cell addresses being included in the function, usually with a colon. Now, for
the next part of this screencast, we will switch to a different spreadsheet that depicts
students’ grades on several tests. The first function we will examine is the AVERAGE function,
which adds up a set of cell values and divides by the number of cells. We’ll use the same
syntax as for formulas, but remember to include AVERAGE in between the equals sign and the
parenthesis. Let’s take the average of Billy’s four test grades. We’ll type in the “test
average” cell in Billy’s row, which is row 2, and use =AVERAGE(B2:E2). Close the
parentheses, hit enter, and there’s Billy’s test average. Notice that the letters change
this time, not the numbers, in the cell addresses (B2:E2)—we’re going across a row, not
down a column. Don’t want to do this for all 16 students? Use fill handle! Just grab
the corner of Billy’s test average and pull down. Let’s check a few kids and make sure
the fill handle worked. Brandon—the formula uses the tests in Brandon’s row, 8. Good.
We now have each student’s average for all four tests. Let’s find the class average
for each individual test. This time we’re going down the columns—for Test 1, we’ll
type =AVERAGE( in the “class average” row and highlight all 15 students’ grades.
Close parentheses and…there we go! The class average for test 1 is 84.625. Not too bad.
We’ll fill in the averages for the other three tests using fill handle and the major
four-test average at the end. We have a lot of information about students’ average grades
now. What if we want to know the highest and lowest grades, or the range, for each test
and for the test averages? We would use the Minimum and Maximum functions, which find
the lowest and highest values of a data set for us. Let’s find the class maximum for
Test 1. In B20, right here, we’ll enter =MAX(B2:B17). We can also highlight those
cells. Close parentheses, and hit enter. Check to make sure it’s right—yep, it looks
like Michelle had the highest score with a 98. Good job, Michelle. Drag fill handle across
to fill in the other test highs. We can do the same thing one row down for minimum test
One final function we haven’t discussed is the SUM function. This forms part of the
AVERAGE function. Let’s use a new spreadsheet—this one tells us about school fundraising sales.
If we want to find the total for kindergarten’s sales, we’ll go to B5, in this spreadsheet,
and use the usual syntax: =SUM( and then we’ll highlight the cells we want to total, these
three, and end with a parenthesis. $255 is the total sales for the kindergartners. Nice
job. Let’s make it easy with fill handle to find the totals for the other grades, just
like this…and then for the totals for each fundraiser. Look at H5. $4,138, the total
amount of money raised from sales in all grades, would be the same whether we dragged fill
handle across from the “grade totals” row (here)…or the “fundraiser totals”
column. Graphs can be a helpful way to view fundraising data like this—for more information,
see Paige’s video. Thanks for watching my screencast—hope you’ve learned a lot about
formulas, functions, and order of operations in Excel.