- =SUM(first value, second value, etc), or
- =SUM(first value:last value)
- 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 be disregarded.
|
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 should 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, A2, A3, A4 | 150 |
| =sum(A1:A2, A5) | A1, A2, A5 | 75 |
 |
 |
 |
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 |
|
|
|
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.
- 0 Values ARE 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 | Answer |
| =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 |
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.
- 0 Values ARE 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 min functions can be used! |
| Example of min | Cells to look at | Answer |
| =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 smallest number) | 10 |
|
|
|
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.
- If you want to count text entries as well as number data, use the function CountA.
- If you only want to count blank entries, use the function CountBlank.
|
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 | 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 |
|
|
|
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
- the logic test is always a comparison between two values
|
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) |
A Nested IF function is when a second IF function is placed inside the first in order to test additional conditions. "Nesting" IF functions increases the flexibility of the function by increasing the number of possible outcomes. The syntax is
- =IF (condition, IF (condition, value-if-true2, value-if-false2), value-if-false1)
Based on above table, let us check, if the amount is above one dollar (condition 1 or outer condition) but within 20% of one dollar (condition 2 or inner condition):
| Example of IF typed into column B |
Compares | Answer |
| =IF (A2>1, IF (A2<=1.2,"Yes","No"),"No") | is ( .95 > 1) | No |
| =IF (A3>1, IF (A3<=1.2,"Yes","No"),"No") | is (1.37 > 1), then is (1.37<=1.2) | First Yes, then No => Final answer is No |
Nested IF functions are very powerful as they allow us to make multiple comparisons. However, they can become difficult to read if we nest too many conditions. Additionally, you can place a maximum of 7 nested IF statements into each other.
A more elegant way of creating multiple IF statements with no limitation on how many conditions can be combined is the "Boolean IF" or "Boolean multiplication". This technique takes advantage of the fact that, when multiplying a conditional statement, TRUE is treated as 1 and FALSE is treated as 0. Here's are some examples:
| Examples of BOOLEAN IF | Compares | Answer |
| =(A2>1) | is ( .95 > 1) | FALSE |
| =(A2>1)*1 | is ( .95 > 1) | 0 |
| =(A3>1)*(A3<=1.2) | is (1.37 > 1) and is (1.37<=1.2) | First TRUE*second FALSE => Final answer is 0 |
| =(A3>1)*(A3>1.2) | is (1.37 > 1) and is (1.37>1.2) | First TRUE*second TRUE => Final answer is 1 |
|
|
|
The next functions we will discuss are CountIF and SumIF. CountIF will return the number of entries (actually counts each cell that contains number data) in the selected range of cells given a certain condition is met. SumIF will add all entries in the selected range of cells given a certain condition is met.
 |
We will look at several different specific examples that show how the CountIF ans SumIF functions can be used! |
| Example of Count | Cells to look at | Answer |
| =COUNTIF(C5:C16,"No") | C5 to C16, count if "No" | 4 |
| =COUNTIF(C5:C16,C18) | C5 to C16, count if C18, i.e. "Yes" | 8 |
| =SUMIF(C5:C16,"No",D5:D16) | if C5 to C16 is "No", sum respective cells in D5 to D16 | 9 |
| =SUMIF(C5:C16,C19,D5:D16) | if C5 to C16 is C19, i.e. "Yes", sum respective cells in D5 to D16 | 35 |
|
|
|
SUMPRODUCT is an extremely versatile function which can be used in different ways. MS Excel Help gives only a limited idea as to the many uses of this tool. Primary purpose of SUMPRODUCT is to multiply corresponding components in the user defined arrays(lists), and return the sum of those products. It takes the format
- =SUMPRODUCT((array1)*(array2)* ...), or
- =SUMPRODUCT((array1),(array2), ...), or
- =SUMPRODUCT((array1),--(array2="condition1"),--(array3="condition2"), ...), or
- =SUMPRODUCT((array1),(array2),--(array3="condition1"),--(array4="condition2"), ...).
- It can contain up to 30 different arrays.
SUMPRODUCT() can be used to add up or count a selected range of values which meet specified 'IF' criteria/ conditions so it is a more versatile SUMIF that works in all versions of Excel (the new SUMIFS() function will not work in Excel 2003 or older!). SUMPRODUCT() can perform calculations using any number of different conditions (within the limit of 30 different arrays). Instead of each array argument being a range of cells to be multiplied, some of the arrays become a condition (or criteria) specifying the items to be summed or multiplied. Furthermore, the function is ideally suited to calculating weighted averages - something which Excel does not provide a specific function for. (Further background: The "minus-minus" or double negation (--) in front of each Boolean-IF statement array are there in order to convert the "TRUE", "FALSE" components in that array to a sequence of 0, 1 statements)
In the below example the first two arrays have the double negation (--) in front and check for a condition; only the last array specifies the range that should be summed up.
|
|
|
The NPV function returns the net present value of an investment if cash flows occur annually. Otherwise, we either need to adjust the discount rate or we make use of the XNPV function, where you simply supply the date values that correspond to the cash flow values to the function as arguments. The syntax for the NPV functions is:
- =NPV( discount_rate, value1, value2, ... value_n )
- =XNPV( discount_rate, values, dates)
discount_rate is the discount rate for the period.
value1, value2, ... value_n are the future payments and income for the investment (ie: cash flows). There can be up to 29 values entered (Tip: if your values are subsequent, enter value1:value_n to prevent this limitation)
dates are the exact dates when a cash flow occurs (usually you would use your time line that is on top of each cash flow schedule).
Note:
Microsoft Excel's NPV function does not account for the intial cash outlay, or may account for it improperly depending on the version of Excel. However, there is a workaround. This workaround requires that you NOT include the initial investment in the future payments/income for the investment (ie: value1, value2, ... value_n), but instead, you need to subtract the amount of the initial investment from the result of the NPV function.
The workaround formula is also different depending on whether the cash flows occur at the end of the period (EOP) or at the beginning of the period (BOP). If the cash flows occur at the end of the period (EOP), you would use the following formula:
- =NPV( discount_rate, value1, value2, ... value_n ) - Initial Investment
If the cash flows occur at the beginning of the period (BOP), you would use the following formula:
- =NPV( discount_rate, value2, ... value_n ) - Initial Investment + value1
The IRR function returns the internal rate of return for a series of cash flows. The cash flows must occur at regular intervals, but do not have to be the same amounts for each interval. XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic or annual.The syntax for the IRR functions is:
- =IRR( range, guess )
- =XIRR(values, dates, guess)
range is a range of cells that represent the series of cash flows.
guess is optional and in most cases can be disregarded. It is the your guess at the internal rate of return. If this parameter is omitted, the IRR function assumes an estimated_irr of 0.1 or 10%.
dates is a schedule of payment dates that corresponds to the cash flow payments.
Note:
Excel tries to recalculate the IRR until the result is accurate within 0.00001 percent. If after 20 tries Excel has not calculated an accurate value, it will return the #NUM! error.
|
|
|
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 D8 =PMT(D5/12,D6,D4)
D5 is the yearly interest and since it's compounded monthly we divide by 12 D6 is the number of months (# of payments) D4 is the amount of money we borrow |
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 (assumes simple interest rate, i.e. no compounding).
|
|
|
The CHOOSE function returns a value from a list of values based on a given position.
The syntax for the Choose function is:
- =CHOOSE( position, value1, value2, ... value_n )
position is position number in the list of values to return. It must be a number between 1 and 29.
value1, value2, ... value_n is a list of up to 29 values. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value. To avoid the limitationof 29 values, simply refer to the whole list via its first and last value: value1:value_n.
Note:
- If position is less than 1, the Choose function will return #VALUE!.
- If position is greater than the number of the number of values in the list, the Choose function will return #VALUE!.
- If position is a fraction (not an integer value), it will be converted to an integer by dropping the fractional component of the number.
 |
The equation in cell H12, H13,... multiplies the respective values from columns F * G * the correct currency, based on the choose selector in cell C4 and the possible options 1-4 in cells C6:C9. In this case, the chosen currency is currency 4, i.e. Japanese Yen and the table values are reported in Yen. |
CHOOSE switches also play a very important part in scenario analysis.
TheVLOOKUP function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number. The HLOOKUP function searches for value in the top row of table_array and returns the value in the same column based on the index_number. Note that parentheses (row) are used to highlight where HLOOKUP refers to rows rather than columns.
The syntax for the VLOOKUP (and HLOOKUP) functions is:
- =VLOOKUP( value, table_array, index_number, match_type )
- =HLOOKUP( value, table_array, index_number, match_type )
value is the value to search for in the first column (row) of the table_array.
table_array is two or more columns (rows) of data that is sorted in ascending order.
index_number is the column (row) number in table_array from which the matching value must be returned. The first column (row) is 1.
match_type determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value.
Note:
- If index_number is less than 1, the VLOOKUP function will return #VALUE!.
- If index_number is greater than the number of columns (rows) in table_array, the VLOOKUP function will return #REF!.
- If you enter FALSE for the match_type parameter and no exact match is found, then the VLOOKUP function will return #N/A.
|
|
|
The INDEX function can return an item from a specific position in a list.
The MATCH function can return the position of a value in a list.
The INDEX and MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.
INDEX Function -- Arguments
The INDEX function has three arguments:
- =INDEX(array, row_num, column_num)
- array: Where is the list?
- row_num: Which row has the value you want returned?
- column_num: Which column has the value you want returned?
MATCH Function -- Arguments
The MATCH function has three arguments:
- =MATCH(lookup_value, lookup_array, match_type)
- lookup_value: What value do you want to find in the list? You can type the value, or refer to a cell that contains the value.
- lookup_array: Where is the list?
- match_type: Match_type can be -1, 0, or 1. It tells Excel how to match the lookup_value to values in the lookup_array.
1 -- find the largest value less than or equal to lookup_value (the list must be in ascending order)
0 -- find the first value exactly equal to lookup_value. Lookup_array (the list can be in any order)
-1 -- find the smallest value greater than or equal to lookup_value. (the list must be in descending order)
Note: If match_type is omitted, it is assumed to be 1.
INDEX and MATCH used together
One advantage of the INDEX/MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.
- =INDEX(array, MATCH(lookup_value, lookup_array, match_type))
In our example, we are looking up the property valuations of various companies. We specify which asset we want to look at in cells F11 to F15. By typing in the respective asset numbers, the valuations are reported in column G and the names are reported in column H. To report valuations and company names the functions are as follows:
- =INDEX($D$6:$D$29,MATCH(F11,$B$6:$B$29,0))
- =INDEX($C$6:$C$29,MATCH(F11,$B$6:$B$29,0))
The 0 in the MATCH function states that we only want precise matches as else, we might run the risk of having a wrong asset/company reported which has a similar valuation.
|
|
|
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 FORMULAS -- select FUNCTION or INSERT 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.
|
|
|
