html5-img
1 / 23

The Function

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:.

vega
Télécharger la présentation

The Function

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Function

  2. 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)

  3. =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.

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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.

  10. 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")

  11. Example: =IF(A1 > 5, "Greater than Five", "Less than Five")

  12. 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")

  13. Complex If Functions =IF(B2>=80, "A", IF(B2>=60, "B", IF(B2>=45, "C", IF(B2 >=30, "D", "Fail" ) ) ) )

  14. 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

  15. The syntax for the CONCATENATE function is: = CONCATENATE ( text1, text2, ... text255 )

More Related