Excel 2007 Advanced Formulas And Functions

/00. Introduction/

00 01. Welcome.mov

00 02. Using the exercise files.mov

/01. Formula and Function Tips-Shortcuts/

01 09. Creating range names to enhance readability.mov

01 06. Showing all formulas with a single command.mov

01 04. Creating 3D formulas to gather data from multiple sheets.mov

01 08. Simplifying debugging formulas.mov

01 02. Copying column formulas instantly.mov

01 01. Using the entire row-column references.mov

01 07. Highlighting formulas with two clicks.mov

01 03. Converting formulas to values with a simple drag.mov

01 05. Updating values without formulas.mov

/02. Excel Functions/

02 01. Understanding and using basic Excel functions.mov

02 02. Using common functions SUM, AVERAGE, MAX, and MIN.mov

02 03. Using and extending AutoSum.mov

/03. IF and Related Functions/

03 03. Using the AND, OR, and NOT functions with IF to create compound logical tests.mov

03 01. Exploring IF logical tests and using relational operators.mov

03 02. Creating and expanding the use of nested IFs.mov

/04. Lookup and Reference Functions/

04 02. Using VLOOKUP for approximate matches.mov

04 08. Using MATCH and INDEX together.mov

04 01. Looking up information with VLOOKUP and HLOOKUP.mov

04 06. Locating data with MATCH.mov

04 03. Using VLOOKUP for exact matches.mov

04 04. Nesting Lookup functions.mov

04 05. Finding table-like information within a function with CHOOSE.mov

04 07. Retrieving information by location with INDEX.mov

/05. Power Functions/

05 02. Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS.mov

05 01. Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF.mov

/06. Statistical Functions/

06 02. Ranking data without sorting with RANK.mov

06 04. Tabulating blank cells with COUNTBLANK.mov

06 01. Finding the middle value with MEDIAN.mov

06 03. Finding the magnitude data with LARGE and SMALL.mov

/07. Date Functions/

07 01. Understanding Excel date-time capabilities in formulas.mov

07 05. Tabulating date-time differences with DATEDIF.mov

07 02. Identifying the day of the week with WEEKDAY.mov

07 03. Counting working days with NETWORKDAYS.mov

07 04. Determining a completion date with WORKDAY.mov

/08. Math Functions/

08 01. Working with rounding functions.mov

08 03. Building random number generators with RAND and RANDBETWEEN.mov

08 02. Finding the remainder with MOD.mov

08 04. Converting a value between measurement systems with CONVERT.mov

/09. Array Formulas and Functions/

09 01. Extending formula capabilities with Arrays.mov

09 02. Counting unique entries in a range with an Array formula.mov

09 06. Combining multiple functions in arrays.mov

09 03. Determining frequency distributions with FREQUENCY.mov

09 04. Flipping row-column orientation with TRANSPOSE.mov

09 05. Building analysis via regression techniques with TREND and GROWTH.mov

/10. Text Functions/

10 07. Reviewing additional text functions.mov

10 01. Locating and extracting data with FIND and MID.mov

10 06. Adjusting character content with REPLACE and SUBSTITUTE.mov

10 04. Using CONCATENATE with functions.mov

10 03. Removing excess spaces with TRIM.mov

10 05. Adjusting case within cells with PROPER, UPPER, and LOWER.mov

10 02. Extracting specific data with LEFT and RIGHT.mov

/11. Financial Functions/

11 01. Calculating payments with PMT.mov

11 02. Finding future values with FV.mov

11 03. Determining total amount of future payments with PV.mov

/12. Information Functions/

12 04. Returning references with INDIRECT.mov

12 02. Using error-checking functions ISERR, ISERROR, IFERROR.mov

12 03. Getting data from remote cells with OFFSET.mov

12 01. Working with the IS information functions.mov

/13. Conclusion/

13 01. Goodbye.mov

/Exercise Files/

Ex_Files_Excel07_Ad_Form.zip

Total files 62