The distinguishing feature of a spreadsheet program such as Excel is
that it allows you to create mathematical formulas and execute functions.
Otherwise, it is not much more than a large table for displaying text.
This page will show you how to create these calculations.
Formulas
![](formula1_small.GIF) |
Formulas are entered in the worksheet cell and must begin with an equal
sign "=". The formula then includes the addresses of the cells whose values
will be manipulated with appropriate operands placed in between. After
the formula is typed into the cell, the calculation executes immediately
and the formula itself is visible in the formula bar. See the example below
to view the formula for calculating the sub total for a number of textbooks.
The formula multiplies the quantity and price of each textbook and adds
the subtotal for each book. |
Relative, Absolute, and Mixed Referencing
Relative Functions.
Calling cells by just their column and row labels (such as "A1") is called
relative
referencing. When a formula contains relative referencing and it
is copied from one cell to another, Excel does not create an exact copy
of the formula. It will change cell addresses relative to
the row and column they are moved to.
For example, if a simple addition formula in cell B2 ("=B1+A2") is copied
to cell C2, the formula would change to "=(C1+B2)" to reflect the new column.
If cell B2 is copied to cell B3, the formula changes to "=B2+A3" to reflect
the new row.
Examine
all the remaining cells, and be certain you understand how Relational Functions
are incremented! |
|
Absolute Functions.
To prevent Relational formulas from being incremented, the contents of
a cell may be defined by absolute referencing. This
is accomplished by placing dollar signs "$" within the cell addresses in
the formula.
For example, an Absolute Function in cell B2 might read "=(B1+$A$2)".
If this function is copied to cell C2, the formula would change to "=(C1+$A$2)"
..... in other words the first factor still increments, but the second
factor remains unchanged. If this function is pasted to the other cells
in Row 2, the first factor will increment, but the second will not in each
case.
Examine
all the remaining cells, and be certain you understand how Absolute Functions
are not incremented! |
|
Mixed referencing can also be
used where only the row OR column fixed. For example, in the formula "=($A2+B$1)",
row A in the first factor is fixed and column 1 in the second factor is
fixed.
For example suppose that cell B2 is defined as "=($A2+B$1)". If B2 is
copied and pasted into B3, the contents of B3 will be ("=$A3+B$1"). In
the first factor, the row has incremented by one because it is not
preceded by a $ sign. In the second factor, the row has not incremented
because it is preceded by a $ sign.
Examine
all the remaining cells, and be certain you understand how Excel is calculating
the contents of each one!
|
Basic Functions
Functions can be a more efficient way of performing mathematical operations
than formulas. For example, if you wanted to add the values of cells D1
through D10, you would type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10".
A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)".
Several other functions and examples are given in the table below:
Function |
Example |
Description |
SUM |
=SUM(A1:100) |
finds the sum of cells A1 through A100 |
AVERAGE |
=AVERAGE(B1:B10) |
finds the average of cells B1 through B10 |
MEDIAN |
=MEDIAN(B1:B10) |
finds the median of cells B1 through B10 |
STDEVP |
=STDEVP(B1:B10) |
finds the standard deviation of cells B1 through B10 |
MAX |
=MAX(C1:C100) |
returns the highest number from cells C1 through C100 |
MIN |
=MIN(D1:D100) |
returns the lowest number from cells D1 through D100 |
SQRT |
=SQRT(D10) |
finds the square root of the value in cell D10 |
COUNTA |
=COUNTA(D1:D100) |
counts the number of cells, D1 through D100, which are not empty |
RAND |
=RAND() |
returns a random number >0 and <1 |
ROUND |
=ROUND() |
returns the value of the argument rounded to a specified number of
places. |
TRUNC |
=TRUNC() |
truncates a number to a whole integer. |
IF |
=IF(C3<20,TRUE,FALSE) |
if the value in C3 is < 20, enter TRUE; if value > 20 enter
FALSE |
|
Many other functions
are provided by Excel. They can be accessed from the Insert menu,
and selecting Paste Function. A dialog
box appears which shows all available functions, and gives a definition
of each.
Categories most useful for scientific applications
are:
-
Math and Trig
-
Statistical
-
Logical
-
Most Recently Used
|
Conditional Formatting.
Cells can be programmed to display a certain format only when the cell
contains a specified value. Go to the Insert menu and select
Conditional
Formatting. This will open a dialog
box which provides choices for conditions, arguments and formatting
options. The format will be active when the specified conditions are met.
Open a new Excel spreadsheet. Type a "3" into cell C2. Enter a formula
for D2 so that it contains a value of 5 greater than C2. Paste this function
into the cells below until you have a list of 10 numbers.
Click
on the thumbnail! |
Sum. In cell C12, calculate the
sum
of cells C2:C11 as follows:
-
Select cell C12.
-
Type "=SUM()"' into C12; then place the cursor between the parentheses.
-
Move the cursor to C2 and drag down to C11. The 'marching ants' show the
cells you have selected. Notice that the formula for C12 is now "=SUM(C2:C11)"
-
Hit "ENTER". the sum of the 10 numbers (255) now appears in C12.
|
![](Spacer.GIF) |
|
|
Average. In cell C13, calculate the average
of cells C2:C11 as follows:
-
Select cell C13.
-
Type "=AVERAGE()"' into C13; then place the cursor between the parentheses.
-
Move the cursor to C2 and drag down to C11. The 'marching ants' show the
cells you have selected. Notice that the formula for C13 is now "=AVERAGE(C2:C11)"
Hit "ENTER". the average of the 10 numbers (25.5) now appears
in C13.
|
![](Spacer.GIF) |
|
|
High. In cell C14, calculate the high value
of cells C2:C11 as follows:
-
Select cell C14.
-
Type "=MAX()"' into C14; then place the cursor between the parentheses.
-
Move the cursor to C2 and drag down to C11. The 'marching ants' show the
cells you have selected. Notice that the formula for C14 is now "=MAX(C2:C11)"
Hit "ENTER". the highest of the 10 numbers (48) now appears
in C14.
|
![](Spacer.GIF) |
|
|
Low. In cell C15, calculate the low value
of cells C2:C11 as follows:
-
Select cell C15.
-
Type "=MIN()"' into C15; then place the cursor between the parentheses.
-
Move the cursor to C2 and drag down to C11. The 'marching ants' show the
cells you have selected. Notice that the formula for C15 is now "=MIN(C2:C11)"
Hit "ENTER". the lowest of the 10 numbers (3) now appears in
C15.
|
Click
on the thumbnail! |
Open a new Excel spreadsheet.
-
Starting with cell B2, insert 16 Random Numbers.
-
Program this spreadsheet to calculate the values which are shown in the
graphic.
-
Program the spreadsheet to display a light black border and yellow fill
for each cell which contains a calculated value. The font should be Geneva
9 point Regular.
-
IF the rounded value is greater than the truncated value, display
YAHOO!
-
If the rounded value is not greater than the truncated value display
pflltpppp!!
-
Use conditional formatting for the fill and pattern
shown in the cells in which YAHOO!
is displayed. The font is Geneva 9 point Bold in white.
|
Goal Seek
Spreadsheets were originally created to help business executives do
"what if" analyses. That is if you hypothesize a set of variable for your
business, what will be the sales, revenue, profits, taxes, etc. after some
length of time such as 6 months, 1 year, 3 years, 10 years, and so on.
Excel has the ability to turn the process on its head! If this case
a spreadsheet is programmed to use a set of input variables to calculate
a result. Then the desired result is entered and the "Goal
Seek" function calculates the variables which must be
satisfied in order to attain that goal!
The "Goal Seek" dialog box is accessed through the "Tools" menu.
Set up the following simple demonstration:
-
Program a spreadsheet to find the product of "5" and "10" as
shown.
-
Open the "Goal-Seek" dialog box. Click on G2; then set the desired value
to "75"; then click on E2.
-
Excel will now find the value for E2 which will produce the desired product
of "75" in cell G2.
Click
on the thumbnail! |
Open a new Excel spreadsheet.
-
Set up the spreadsheet as shown in the graphic to the left ( do not include
the callouts). Formatting is:
-
Geneva 9 point regular
-
No decimals; commas to indicate thousands
-
In last column, light grey fill and dark grey borders
-
Program this spreadsheet to calculate the values which are shown in the
graphic.
-
Use the Goal-Seek function to calculate the following:
-
If you wanted to have $100,000 after 10 years, what annual rate of interest
would be required?
-
If you were less greedy, and wanted to take fewer risks with your money
you might be happy to get only $65,000. What rate of interest would be
required?
-
If you wanted $100,000 after 10 years, but wanted a relatively safe 7%
rate of return, how much would you have to invest each year?
|
|