Formulas
 
 

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.
	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.
	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.
	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:

1. Program a spreadsheet to find the product of "5" and "10" as shown.
2. Open the "Goal-Seek" dialog box. Click on G2; then set the desired value to "75"; then click on E2.
3. 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?