The Keyboarders Guide to Excel Lesson 1a
Before we move on, it is important to understand what is meant by a database in Excel. A database consists of some number of columns and rows, where each row is an individual person, transaction, stock, etc. and each column provides specific information about that person, transaction, or stock. It is often called a variable. For example, the age column gives the age of each individual.
Use the command Ctrl-PgDn) to move to the next sheet in the workbook called Report.
In Cell A1 type the formula =data. This should return Student, the first cell of the database. Copy this over one column and it will return Grade, the second column of the database. Note this must be done starting in cell A1 which corresponds to the Cell in which the original database started.
Try these formulas. Put them in B2 and B3
=rows(data) This will tell you the number of cases (rows ) in the database.
=columns(data) will show you how many columns are in the database.
Now you know the database has 21 rows and 2 columns. You already know that the first column of the database is called Student and there are two columns in the database.
Go back to cell A1 and copy it to B1. It will give you the name of the second column in the database or Grade.
If the columns(data) function had returned 3 instead of 2 you would know the database contained 3 columns and you would copy B1 to C1 and get the name of the third variable. Note that as the number of variables increases as shown by =columns(data), you will copy more columns to the right.
What can you say about grade? All of the usual functions you already use can be used more easily with range names.
Put these formulas in cells B4 and down. =min(grade) and =max(grade) Will return the highest and lowest scores.
=average(grade),=median(grade), and =mode(grade) will give you the mean, median, and mode.
Note that they use =average and not =mean in the function.
=Stdev(grade) will return the standard deviation of the grades.
This lesson was a demonstration of the power of range names. Excel has a wide range of procedures and range names make it easier to use. To ask questions and make comments, go to the Forum.
To move on to the next lesson, download the next database and move on to lesson 2.
