SUM Functions
Probably the most popular function in any spreadsheet is the SUM function. The Sum function takes all of the values in each of the specified cells and totals their values. The syntax is:
- =SUM(first value, second value, etc)
- Blank cells will return a value of zero to be added to the total.
- Text cells can not be added to a number and will produce an error.
| Let's use the table here for the discussion that follows: We will look at several different specific examples that show how the typical function can be used! Notice that in A4 there is a TEXT entry. This has NO numeric value and can not be included in a total. |
|
| Example | Cells to ADD | Answer |
| =sum(A1:A3) | A1, A2, A3 | 150 |
| =sum(A1:A3, 100) | A1, A2, A3 and 100 | 250 |
| =sum(A1+A4) | A1, A4 | #VALUE! |
| =sum(A1:A2, A5) | A1, A2, A5 | 75 |
Average Function
There are many functions built into many spreadsheets. One of the first ones that we are going to discuss is the Average function. The average function finds the average of the specified data. (Simplifies adding all of the indicated cells together and dividing by the total number of cells.) The syntax is as follows.
- =Average (first value, second value, etc.)
| Let's use the table here for the discussion that follows: We will look at several different specific examples that show how the average function can be used! |
|
| Example | Cells to average | Answer |
| =average (A1:A4) | A1, A2, A3, A4 | 62.5 |
| =average (A1:A4, 300) | A1, A2, A3, A4 and 300 | 110 |
| =average (A1:A5) | A1, A2, A3, A4, A5 | 62.5 |
| =average (A1:A2, A4) | A1, A2, A4 | 58.33 |
Max Function
The next function we will discuss is Max (which stand for Maximum). This will return the largest (max) value in the selected range of cells.
- Blank entries are not included in the calculations of the Max Function.
- Text entries are not included in the calculations of the Max Function.
| Let's use the table here for the discussion that follows. We will look at several different specific examples that show how the Max functions can be used! |
|
| Example of Max | Cells to look at | Ans. Max |
| =max (A1:A4) | A1, A2, A3, A4 | |
| =max (A1:A4, 100) | A1, A2, A3, A4 and 100 | |
| =max (A1, A3) | A1, A3 | |
| =max (A1, A5) | A1, A5 |
MIN Function
The next function we will discuss is Min (which stands for minimum). This will return the smallest (Min) value in the selected range of cells.
- Blank entries are not included in the calculations of the Min Function.
- Text entries are not included in the calculations of the Min Function.
| Let's use the table here for the discussion that follows. We will look at several different specific examples that show how the min functions can be used! |
|
| Example of min | Cells to look at | Ans. min |
| =min (A1:A4) | A1, A2, A3, A4 | |
| =min (A2:A3, 100) | A2, A3 and 100 | |
| =min (A1, A3) | A1, A3 | |
| =min (A1, A5) | A1, A5 (displays the smallest number) |
COUNT FUNCTION
The next function we will discuss is Count. This will return the number of entries (actually counts each cell that contains number data) in the selected range of cells.
- Blank entries are not counted.
- Text entries are NOT counted.
| Let's use the table here for the discussion that follows. We will look at several different specific examples that show how the Count functions can be used! |
|
| Example of Count | Cells to look at | Answer |
| =Count (A1:A3) | A1, A2, A3 | |
| =Count (A1:A3, 100) | A1, A2, A3 and 100 | |
| =Count (A1, A3) | A1, A3 | |
| =Count (A1, A4) | A1, A4 | |
| =Count (A1, A5) | A1, A5 |
COUNT A FUNCTION
The next function we will discuss is CountA. This will return the number of entries (actually counts each cell that contains number data OR text data) in the selected range of cells.
- Blank entries are not Counted.
- Text entries ARE Counted.
| Let's use the table here for the discussion that follows. We will look at several different specific examples that show how the CountA functions can be used! |
|
| Example of CountA | Cells to look at | |
| =CountA (A1:A3) | A1, A2, A3 | |
| =CountA (A1:A3, 100) | A1, A2, A3 and 100 | |
| =CountA (A1, A3) | A1, A3 | |
| =CountA (A1, A4) | A1, A4 | |
| =CountA (A1, A5) | A1, A5 |
IF FUNCTION
The next function we will discuss is IF. The IF function will check the logical condition of a statement and return one value if true and a different value if false. The syntax is
- =IF (condition, value-if-true, value-if-false)
- value returned may be either a number or text
- if value returned is text, it must be in quotes
| Let's use the table here for the discussion that follows. We will look at several different specific examples that show how the IF functions can be used! |
|
| Example of IF typed into column B | Compares | |
| =IF (A2>1,"Yes","No") | is ( .95 > 1) | |
| =IF (A3>1, "Yes", "No") | is (1.37 > 1) | |
| =IF (A5>10000, .08, .05) | is (14000 > 10000) | |
| =IF (A6>10000, .08, .05) | is (8453 > 10000) |
PMT (loan stuff)
The PMT function returns the periodic (in this case monthly) payment for an annuity (in this case a loan). This is the PMT function that was used for the car purchase in the first example. There are a few things that we must know in order for this function to work. To calculate the loan we must know a combination of the following
- (rate) interest rate per period
- (NPER) number of payments until repaid
- (PV) present value of the loan (amount we are borrowing)
- (FV) future value of the money (for saving or investing)
- (type) enter 0 or 1 to indicate when payments are due.
 | equation goes into c7 =PMT(C4/12,C5,-C3) C4 is the yearly interest and since it's compounded monthly we divide by 12 C5 is the number of months (# of payments) -C3 is the amount of money we have (borrow - negative) |
Note that the rate is per period. If we have an annual interest rate of 9.6% and we are calculating monthly payments, we must divide the annual interest rate by 12 to calculate the monthly interest rate.
SIN COS TAN etc.
Excel has most of the math and trig functions built into it. If you need to use the SIN, COS, TAN functions, they can be typed into any cell. If you wanted to find:
| angle | sin | cos | tan |
| REF | =sin(REF) | =cos(REF) | =tan(REF) |
| 0 | 0.00 | 1.00 | 0.00 |
| 30 | 0.50 | 0.87 | 0.58 |
| 45 | 0.71 | 0.71 | 1.00 |
| 90 | 1.00 | 0.00 | |
| 180 | 0.00 | -1.00 | 0.00 |
format for degrees formula = sin (angle * pi()/180) the argument angle is in degrees
format for radians formula = sin (angle) the argument angle is in radians
To calculate trig functions in degrees you must convert them - otherwise excel will calculate them in radians.
You can type in either an actual number for the REF or you can also type in a reference from the excel spreadsheet (like A2).
Function Wizard
In Excel there is a help tool for functions called the Function Wizard. 
There are two ways to get the function wizard. If you look at the Standard Toolbar, the function wizard icon looks like the icon on the right.
The other way to get to the function wizard is to go to the Menu INSERT -- down to FUNCTION.
Either way you get there, at this point Excel will list all of the functions available. Upon choosing the function, Excel will prompt you for the information it needs to complete the function. Mini descriptions are available for each of the cells. It is often necessary for you to understand the functions in order to be able to figure out these descriptions.
Yeah, I know it would have been nice to know this earlier, but it is important for you to understand how the functions work before you start using the Function Wizard. It is faster to type the basic function in from the keyboard as opposed to going through the steps of this tool.
Well, that is all of the functions we are going to cover.
On to the next phase.
No comments:
Post a Comment