240 likes | 384 Views
The Function. Functions are prewritten formulas. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. When using a function, remember the following:.
E N D
Functions are prewritten formulas. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. When using a function, remember the following: • Use an equal sign to begin a formula. • Specify the function name. • Enclose arguments within parentheses. Arguments are values on which you want to perform the calculation. For example, arguments specify the numbers or cells you want to add. • Use a comma to separate arguments. • Here is an example of a function: • =SUM(2,13,A1,B2:C7)
=SUM(2,13,A1,B2:C7) The equal sign begins the function. SUM is the name of the function. 2, 13, A1, and B2:C7 are the arguments. Parentheses enclose the arguments. Commas separate the arguments.
List of Functions and description Mathematical Functions SUM - Adds up all the values in a range SUMIF - Adds all the values in a range that meet specific criteria SUMIFS (2007 and 2010 only) - Adds values in a range based on multiple criteria SUMPRODUCT - Sum a range of cells that meet multiple criteria ROUND - Round a number to a specified number of digits ROUNDUP - Round a number up to a specified number of digits ROUNDDOWN - Round a number down to a specified number of digits CEILING - Round a number up to a multiple of significance FLOOR - Round a number down to a multiple of significance
Statistical Functions COUNT - Counts all the values in a range AVERAGE - Calculates the average number from a range of values MAX - Finds the maximum value in a range MIN - Finds the minimum value in a range COUNTA - Counts all non-empty cells in a range COUNTBLANK - Counts all blank cells in a range COUNTIF - Counts all the cells in a range that meet specific critera COUNTIFS (2007 and 2010 only) - Counts all the cells in a range that meet multiple criteria AVERAGEIF (2007 and 2010 only) - Calculates the average of a range of values that meet specific criteria AVERAGEIFS (2007 and 2010 only) - Calculates the average of a range of values that meet multiple criteria LARGE - Return a value dependent upon its ranking in a range of values in descending order SMALL - Return a value dependent upon its ranking in a range of values in ascending order RANK - Returns the rank or position of a number within a range of numbers
Text Functions LEN - Returns the length, in number of characters, of the contents of a cell REPT - Repeats a character a specified number of times TRIM - Remove unwanted spaces from cells LEFT - Extracts a specific number of characters from the start of a cell RIGHT - Extracts a specific number of characters from the end of a cell MID - Extracts a specific number of characters from the middle of a cell UPPER - Converts the contents of a cell to uppercase LOWER - Converts the contents of a cell to lowercase PROPER - Converts the contents of a cell to proper case REPLACE - Replace existing characters in a cell with a different set of characters SUBSTITUTE - Replace existing characters with a different set of characters
Financial Functions PMT - Calculates loan repayments based on constant payments and a constant interest rate RATE - Returns the interest rate per period of a loan or investment PV - Returns the present value of an investment based on a constant interest rate and payments FV - Returns the future value of an investment based on constant payments and a constant interest rate IPMT - Calculates the interest paid during a period of a loan or investment PPMT - Calculates the principal payment made in a period of an investment IRR - Returns the internal rate of return on a series of regular investments XIRR - Returns the internal rate of return on a series of irregular payments on an investment NPV - Returns the net present value of an investment based on a series of cash flows and a discount rate XNPV - Returns the net present value of an investment based on a series of cash flows, the dates of the cash flows and a discount rate
Logical Functions IF - Tests a condition and takes an alternative action depending on the result AND - Test up to 30 conditions using logical And OR - Test up to 30 conditions using logical Or IFERROR - Performs a specified action if a formula evaluates to an error, and displays the formula result if not Date & Time Functions TODAY - Returns the current date NOW - Returns the current date and time DATE - Returns the sequential serial number for the specified date and formats the result as a date DAY - Returns the day corresponding to a date represented by a number between 1 and 31
MONTH - Returns the month corresponding to a date represented by a number between 1 and 12 YEAR - Returns the year corresponding to a date represented by a number in the range 1900 to 9999 WORKDAY - Returns the date a specified number of workings days before or after a date WEEKDAY - Returns the day of the week corresponding to a specified date NETWORKDAYS - Returns the number of workdays between two dates EOMONTH - Calculates the last day of the month a specified number of months before or after a date Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.
The IF Function in Excel 2007 • The IF function can be quite useful in a spreadsheet. • It is used when you want to test for more than one value Syntax: IF(logical_test, value_if_true, value_if_false,) Example: =IF(A1 > 5, "Greater than Five", "Less than Five")
Example: =IF(A1 > 5, "Greater than Five", "Less than Five")
List of Conditional Operator < Less Than >= Greater than Or Equal To <= Less than Or Equal To <> Not Equal To Another Example: =IF(A1 >= 5, "Greater than or Equal to Five", "Less than Five")
Complex If Functions =IF(B2>=80, "A", IF(B2>=60, "B", IF(B2>=45, "C", IF(B2 >=30, "D", "Fail" ) ) ) )
The CHOOSE Function in Excel 2007 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, ... value29 ) A example of using Choose would be =Choose(1,"Cars","Boats","Planes") - would return Cars =Choose(2,"Cars","Boats","Planes") - would return Boats =Choose(3,"Cars","Boats","Planes") - would return Planes
The syntax for the CONCATENATE function is: = CONCATENATE ( text1, text2, ... text255 )