Susan Fuschetto
CIS 151 Excel For I.T.
Excel Tutorial 7 - Using Advanced Functions and Conditional
Formatting
Now we get to some of the more advanced
functions in Excel. Logical functions such as IF, IFERROR,
COUNTIF, AVERAGEIF, SUMIF, AND, and OR will be covered along with the
VLOOKUP/HLOOKUP functions. Please be sure to read everything as
you go through this tutorial. It is important to understand how
these advanced functions work and not just how to type them in to a
spreadsheet.
Also, this tutorial continues to show you ways
to use conditional formatting which can be a very powerful tool in
analyzing data.
Please read all of the directions before
getting started on the assignment.
- Please note: Throughout the book it
will instruct you to save your files. In the computer labs on
campus you will need to save the files to your USB drive. If
you are using your own computer, then you may save the file to the
drive of your choice (hard drive, USB, etc.).
- Data file notes: In order to do this
assignment you will need a set of files. The files are in TalonNet
on the Resources page in the Data Files folder. The data file links
are .zip files (compressed
set of files). You will need to download and unzip
(uncompress) the files before using them. Right-click the file
link to get the shortcut menu. Select the Save Target As
option and save the file to your drive. You will then need to
unzip (uncompress) the .zip file. If you are not
familiar with unzipping files please read pages FM17 - FM19 or read the
Unzipping Files
document for a
general set of instructions. The example is from my CIS 101
class.
- Read and do the steps in Excel Tutorial
7 pages
EX363 - EX411.
- Do Case Problem 1 (PC Modem) on page EX413. When you are done your
spreadsheets should look like this
document.
Also, since these formulas are more complex I give you the formulas
in the answer document. Challenge yourself by attempting to
create the formulas by yourself without peaking at the answer
sheets. If you then have trouble well of course use the
formulas in the answer sheet. Pay attention to punctuation and
spelling. Most errors are simple typos. Please note
there are multiple pages in the answer documents so click the Next
Page arrow to see all of the pages.
- Online students: Upload the
Talent Tracs.xlsx and
PC Modem.xlsx files to
the Excel Tutorial 7 assignment on the class website in TalonNet.
Please note that you must submit both files at the same time to the
assignment in TalonNet. So complete both the tutorial and
the Case Problem 1 before logging in to TalonNet and submitting the assignment.
- Traditional students:
During the lab time show the
above
files listed in step 5 on your computer screen to me.
Last update:
02/12/11 |
