Lynda com 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 01. Using the entire row-column references.mov

01 02. Copying column formulas instantly.mov

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

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

01 05. Updating values without formulas.mov

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

01 07. Highlighting formulas with two clicks.mov

01 08. Simplifying debugging formulas.mov

01 09. Creating range names to enhance readability.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 01. Exploring IF logical tests and using relational operators.mov

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

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

/04. Lookup and Reference Functions/

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

04 02. Using VLOOKUP for approximate matches.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 06. Locating data with MATCH.mov

04 07. Retrieving information by location with INDEX.mov

04 08. Using MATCH and INDEX together.mov

/05. Power Functions/

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

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

/06. Statistical Functions/

06 01. Finding the middle value with MEDIAN.mov

06 02. Ranking data without sorting with RANK.mov

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

06 04. Tabulating blank cells with COUNTBLANK.mov

/07. Date Functions/

07 01. Understanding Excel date-time capabilities in formulas.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

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

/08. Math Functions/

08 01. Working with rounding functions.mov

08 02. Finding the remainder with MOD.mov

08 03. Building random number generators with RAND and RANDBETWEEN.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 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

09 06. Combining multiple functions in arrays.mov

/10. Text Functions/

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

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

10 03. Removing excess spaces with TRIM.mov

10 04. Using CONCATENATE with functions.mov

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

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

10 07. Reviewing additional text functions.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 01. Working with the IS information functions.mov

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

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

12 04. Returning references with INDIRECT.mov

/13. Conclusion/

13 01. Goodbye.mov

/Exercise Files/

Ch 1 Tips.xlsx

Ch 10 Text.xlsx

Ch 11 Financial.xlsx

Ch 12 Information.xlsx

Ch 2 Functions.xlsx

Ch 3 IF.xlsx

Ch 4 LOOKUP.xlsx

Ch 5 Power.xlsx

Ch 6 Stat.xlsx

Ch 7 Date.xlsx

Ch 8 Math.xlsx

Ch 9 Array.xlsx

Total files 73