CS130/230 Lecture 3

Excel Built-In Functions

 

Tuesday, February 10, 2004

Cell Filling and Absolute and Relative References

As a review of the material that we covered last week, I want to start class with a problem. You should attempt this problem on your own but I will be walking round class checking with you. If you have any questions just raise your hand.

Problem 1

In an Excel spreadsheet, enter the following information (either type it in or copy and paste):

 

Name

Base Salary

Projected Sales

Tim Baker

$ 6,000.00

$ 225,456.00

Joseph Learner

$ 7,500.00

$ 264,888.00

Barbara Albright

$ 8,500.00

$ 235,250.00

Lynn Mourissee

$ 7,250.00

$ 258,450.00

Richard Noble

$ 4,250.00

$ 325,456.00

 

The information in the table represents the names of the employeeÕs in the Woodbridge Furniture Company, their base salary and their projected sales. The company now wants to pay a commission to each of its employees based on their sales. The amount of this commission will change depending on the profits the company makes. For this year it will be 5% but you want to make it easy to change this value and reflect the changes in the table.

 

Add two columns to the table:

-       Commission Amount: will be the commission percentage times the projected sales

-       Quarterly Salary: will be the base salary plus the commission amount

Built-in Functions

Functions are special routines provided by Excel to do simple and complex calculations. There is no way we can cover all of the functions, but we will look at several which will make life much simpler and the rest you can discover on a need basis.

 

Question: Where do we go to get a feel for the functions that exist in Excel?

 

The basic syntax required to invoke a function is carried over from algebra and includes the following:

 

-  The function name

-  A left parenthesis

-  A list of arguments where each argument is separated by a comma

-  A right parenthesis

 

In general, we are looking at something of the form: name(arg1, arg2,...)

 

As an example, if we have a worksheet that has values in cells A1 to A5 that we want to sum up, we can do this in a couple of ways:

 

  1. with the formula =A1+A2+A3+A4+A5
  2. =sum(A1:A5)

 

In case 2) above, the name of the function is sum and the argument is A1:A5.

 

The colon (:) between the cell references indicates a range. So, A1:A5 means A1, A2, A3, A4 and A5.

 

As you can imagine, this function is invaluable if you want to sum up the contents of 100 cells in a column.

 

Notice that Excel is not case-sensitive with names of functions or cells.

 

Question: What does case-sensitive mean?

 

A couple of other things to note:

 

-  There can be up to 30 arguments in a function. These depend on the function itself, and some of the arguments can be optional.

-  Cell entries have a 255 character limit

-  The number and type of arguments depend on the function being used

 

We know of the following types of arguments for various functions:

 

-  literal numbers

-  text

-  ranges

-  other functions

-  arrays

-  logical values

 

Excel has over 350 built-in functions divided into 10 categories. There are really two different ways to enter functions into a cell:

 

  1. If you know the function well and the options for parameters, then probably just typing away is the best.
  2. If you don't quite know everything about the function, then use the (paste function) command on the toolbar. This command will paste the function into the worksheet with the dummy arguments. You then must replace the dummy arguments with the actual arguments.

Problem 2

Two incredibly useful functions are INT and MOD. You may or may not have heard of these functions, but I would like you to use both of these functions in a worksheet and then if I call on you, I want you to be able to describe what they do. Also, one of these will be useful in an upcoming project.

Problem 3

Add the following to the Excel worksheet you created for problem 1:

-       Cell to calculate the total salary that will be paid to employees

-       Cell to calculate the average salary

-       Cell to calculate the highest salary

-       Cell to calculate the lowest salary

-       Cells to calculate the total, average, highest and lowest amount paid in commission

Nested Functions

As was mentioned above, it is possible to have functions as arguments in other functions.

 

Question: Can you think of an example of when we would do this[SK1] ?

 

 [SK1]Give the example =ROUND(AVERAGE(A1:A10),1)