Using Other Functions

[ LiB ]

As mentioned at the beginning of this chapter, Excel includes over 230 different built-in functions that are divided into categories according to the function's purpose. The SUM function, for example, is considered a mathematical function.

Calculating with Mathematical Functions

You have learned how to create a mathematical function to add a series of numbers together by using the SUM function. Excel includes many other mathematical functions, including some for complex trigonometry calculations. The next section illustrates a couple of other commonly used mathematical functions.

Using the INT Function

The INT function rounds a number down to the nearest integer. The number can be a specific number you type or, more commonly, the reference to a specific cell . The syntax is =INT( cell address or number ). For example, to find the integer of cell B3, you would enter =INT(B3).

NOTE

Functions can be nested. For example, to find the integer of the SUM of a range of cells , you might type =INT(=SUM(B3:B10)). Excel will add each cell and round down the total.

Using the ROUND Function

Whereas the INT function displays whole numbers for you, the ROUND function takes a value and rounds it to a specified number of digits. The ROUND function contains two different arguments; one to specify which cell you want to round and the second to tell Excel how many decimal places you want to display. The syntax is =ROUND( number,num of digits ). For example, if cell B10 has a value of 79.3264 and you want it rounded to two decimal places, you would enter =ROUND(B10,2); then Excel would display the answer of 79.3300.

Analyzing with Statistical Functions

Statistical-based functions provide a means for analysis of data. Statistical analysis helps you explore, understand and visualize your data.

Using the AVERAGE Function

The AVERAGE function finds an average of a range of values. The syntax for this function is =AVERAGE( range of values to average ). An example might be =AVERAGE(B7:D7), which would add the values in the three cells B7, C7, and D7, then divide that total by three to get the average value.

Using the MAX and MIN Functions

Two other common statistical functions are the MAX and MIN functions. The MAX function will display the largest value in a range of cells, whereas the MIN function will display the smallest value in a range of cells. The syntax is =MAX( range of values ) or =MIN( range of values ).

Using the COUNT and COUNTA Functions

The COUNT function is handy to find out how many numerical entries are in a specified area. The COUNTA function is similar except it is not limited to numerical entries; it will count any non-blank cell, no matter what type of information the cell contains. The syntax of these functions is very similar: =COUNT( range of cells to count ) and =COUNTA( range of cells to count ).

Using Date Functions

Date functions are commonly used to enter the current date into a worksheet, or to calculate the difference between two or more dates. Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and September 16, 2003 is serial number 37880, because it is 37,880 days after January 1, 1900. When you type a date in Excel, it will display the date in a regular date format, such as 9/16/2003, but behind the scenes Excel still considers that date a serial number.

Because dates and times are values, they can be added, subtracted, and included in other calculations.

Using the NOW Function

If you enter the NOW function in a cell, Excel will display the current date and time. The date and time are dynamic in that the current date and time will change whenever you recalculate anything in the worksheet. By default, Excel recalculates the worksheet whenever any changes, additions, or deletions are made. The NOW function does not contain any arguments, so the syntax is =NOW().

Finding the Difference between Two Dates

A very popular use for entering dates in an Excel worksheet is to find the number of days between two dates. Since Excel stores dates as serial numbers, you can create a simple mathematical function to figure the number of days between two dates. While this is more of a formula instead of an Excel function, the mathematical calculation can include a cell with a date function, such as =NOW.

The example you see here is a formula to subtract the project start date from today's date, so the formula is A1-D4.

NOTE

You cannot subtract a date that is later than another date, or the error #### appears in the cell.

Since both of the original dates are in a date format, Excel will display the difference in a date format. You will need to tell Excel to display that value as a number.

NOTE

Unfortunately, there is no easy method to calculate the exact difference in years. While you can change the mathematical formula to divide the total by 365, this calculation does not take into account leap years. It will, however, give a pretty close calculation of years .

Figuring with Financial Functions

Financial functions perform elaborate calculations such as returns on investments or cumulative principal or interest on loans. Functions exist to calculate future values or net present values on investments or to calculate amortization.

Using the PMT Function

The PMT function calculates the payment for a loan based on a constant interest rate. You will need to enter the interest rate, the number of payments, and the amount of the loan. The syntax is =PMT( rate,nper,pv,fv,type ) where rate is the interest rate, nper is the number of payments and pv is the loan amount. There are two other optional arguments, including fv, (future value),which Excel assumes to be zero unless you enter an fv . The other optional argument is for type, which refers to when the payment is due.

NOTE

Be uniform about the units you use for specifying rate and nper. If you make monthly payments on a six-year loan at an annual interest rate of 8 percent, use 8%/12 for rate and 6*12 for nper. If you make annual payments on the same loan, use 8 percent for rate and 6 for nper.

NOTE

The payment amount returned by PMT includes principal and interest only

Understanding Logical Functions

You have seen that most functions work in basically the same way. You enter the equal sign, enter the function name , and then tell the function which data to use. Most functions involve some sort of mathematical calculation. Logical functions are different in that they use operators such as equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

Using the IF Function

The IF function is a logical function that evaluates a condition and returns one of two answers, depending on the result of the evaluation. The IF function has three parts . The first part determines if a situation is true or false; the second part determines the result to display if the first part is true; and the third part determines the result to display if the first part is false. It's really not as confusing as it may sound. The syntax is =IF( item to test, value if true, value if false ).

For example, in cell C1 you want to find out if the value in cell B5 is greater than the value in cell B6. If B5 is larger than B6 (true), you want to enter "Yes" in cell C1; if B5 is not larger than B6 (false), you want the answer "No" in cell C1. You would enter the function as =IF(B5>B6,"Yes","No").

NOTE

If you want the result to be text, the result must be enclosed in quotation. The quotation marks will not be displayed in the answer. No quotation marks are needed if the result is numeric.

[ LiB ]