SUM
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)
In the first and second spots you can enter any of the following (constant, cell, range of cells).
- 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. |
|
A |
| 1 |
25 |
| 2 |
50 |
| 3 |
75 |
| 4 |
test |
| 5 |
|
|
| 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
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.)
Text fields and blank entries are not included in the calculations of the Average Function.
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 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! |
|
A |
| 1 |
10 |
| 2 |
20 |
| 3 |
30 |
| 4 |
test |
| 5 |
|
|
| Example of Max | Cells to look at | Ans. Max |
| =max (A1:A4) | A1, A2, A3, A4 |
30 |
| =max (A1:A4, 100) | A1, A2, A3, A4 and 100 |
100 |
| =max (A1, A3) | A1, A3 |
30 |
| =max (A1, A5) | A1, A5 |
10 |
MinThe 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! |
|
A |
| 1 |
10 |
| 2 |
20 |
| 3 |
30 |
| 4 |
test |
| 5 |
|
|
| Example of min | Cells to look at | Ans. min |
| =min (A1:A4) | A1, A2, A3, A4 |
10 |
| =min (A2:A3, 100) | A2, A3 and 100 |
20 |
| =min (A1, A3) | A1, A3 |
10 |
| =min (A1, A5) | A1, A5 (displays the smallestnumber) |
10 |
Count
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! |
|
A |
| 1 |
10 |
| 2 |
20 |
| 3 |
30 |
| 4 |
test |
| 5 |
|
|
| Example of Count | Cells to look at | Answer |
| =Count (A1:A3) | A1, A2, A3 |
3 |
| =Count (A1:A3, 100) | A1, A2, A3 and 100 |
4 |
| =Count (A1, A3) | A1, A3 |
2 |
| =Count (A1, A4) | A1, A4 |
1 |
| =Count (A1, A5) | A1, A5 |
1 |
CountA.
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! |
|
A |
| 1 |
10 |
| 2 |
20 |
| 3 |
30 |
| 4 |
test |
| 5 |
|
|
| Example of CountA | Cells to look at |
Answer |
| =CountA (A1:A3) | A1, A2, A3 |
3 |
| =CountA (A1:A3, 100) | A1, A2, A3 and 100 |
4 |
| =CountA (A1, A3) | A1, A3 |
2 |
| =CountA (A1, A4) | A1, A4 |
2 |
| =CountA (A1, A5) | A1, A5 |
1 |
IF.
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! |
|
A |
B |
| 1 |
Price |
Over a dollar? |
| 2 |
$.95 |
No |
| 3 |
$1.37 |
Yes |
| 4 | comparing # | returning # |
| 5 |
14000 |
0.08 |
| 6 |
8453 |
0.05 |
|
Example of IF
typed into column B | Compares |
Answer |
| =IF (A2>1,"Yes","No") | is ( .95 > 1) |
No |
| =IF (A3>1, "Yes", "No") | is (1.37 > 1) |
Yes |
| =IF (A5>10000, .08, .05) | is (14000 > 10000) |
.08 |
| =IF (A6>10000, .08, .05) | is (8453 > 10000) |
.05 |
PMT
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.
=PMT(rate, NPER, PV, FV, type)
 | 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
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).
No comments:
Post a Comment