Advanced Functions Obj 4 01 Advanced Functions are
Advanced Functions – Obj. 4. 01 Advanced Functions are used in higher-level operations, such as conditional and comparison equations to compute interest rates, due dates and payment terms, and financial projections. 1
Types of Advanced Functions � IF Statement Functions � Date Functions � Lookup Functions � List Functions � Count Functions 2
IF Statements are conditional operators. � Results are returned IF the data specified in an equation meets conditions set by the formula � IF statements can be written to carry out an action Example: IF a value in a cell is greater than or equal to another value, insert the word “Pass” in a cell. Example: The answer is one value IF a condition you specify evaluates to TRUE and returns another value IF the condition evaluates to FALSE. 3
Date Functions are used to calculate a period of time. Types of Date Functions: (1) NOW – returns the current date (2) DATE 360 – calculates the difference in days between 2 dates Examples: • The number of days that have elapsed since a value was entered into a specific cell • To calculate a 30 day due date for a record of spreadsheet invoices
Lookup Functions A Lookup function is used to compare a cell value to an ARRAY of cells and return a value that matches the location of the value in the array. (Note: the values MUST be placed in Ascending order) Types of Lookup Functions: Lookup – used for two column vectors (2) VLook. Up – used when there are more than two columns in the array (lookup table) (1) 5
List Function A List Function is used to: ◦ Assist in organizing spreadsheet information. ◦ Create a more user-friendly spreadsheet atmosphere. ◦ Control the size or content of data entries. ◦ Filter for specific content within a list. 6
List Function Types of Lists: (a) Validated �A validated list limits data entry to specific choices programmed into the function (b) Non-Validated �A non-validated list allows additional entries other than those provided in the drop-down menu. Example: displaying only the Southeast region vice presidents from a spreadsheet instead of displaying all of the vice presidents 7
Count Function The COUNT Function is used to return the number of cells in a range. Types of Count Functions: ◦ Count – returns the number of cells in a range that contain numbers ◦ Count. A – returns the number of cells in a range that contain any value/label ◦ Countlf – returns the number of cells that meet a condition set forth in a formula. 8
- Slides: 8