Excel 2010 Advanced Formulas and Functions – DVDRip Tutorial

Discussion in 'E-Books & Tutorials' started by RMTeam, Sep 24, 2012.

  1. RMTeam

    RMTeam Active Member

    Feb 18, 2012
    Likes Received:
    Trophy Points:

    In Excel 2010 Advanced Formulas and Functions, author Dennis Taylor demystifies formulas and some of the most challenging of the nearly 400 functions in Excel and shows how to put them to their best use. The course reviews the building-block functions, along with a few critical keyboard shortcuts that will speed up working with Excel data. It also covers how to perform advanced searching and data retrieval with LOOKUP functions, tabulate data with counting, statistical, and math functions, reformat data with text functions, and work with financial data using advanced formulas. Exercise files accompany the course. Course outline on next page



    Using the exercise files

    1. Formula and Function Tips and Shortcuts
    Using the entire row/column references
    Copying column formulas instantly
    Converting formulas to values with a simple drag
    Creating 3D formulas to gather data from multiple sheets
    Updating values without formulas
    Displaying and highlighting formulas
    Simplifying debugging formulas
    Enhancing readability with range names

    2. Formula and Function Tools
    Reviewing function basics
    Using and extending AutoSum
    Using absolute and relative references
    Using mixed references

    3. IF and Related Functions
    Exploring IF logical tests and using relational operators
    Creating and expanding the use of nested IF statements
    Using the AND, OR, and NOT functions with IF to create compound logical tests

    4. Lookup and Reference Functions
    Looking up information with VLOOKUP and HLOOKUP
    Finding approximate matches with VLOOKUP
    Finding exact matches with VLOOKUP
    Nesting LOOKUP functions
    Finding table-like information within a function with CHOOSE
    Locating data with MATCH
    Retrieving information by location with INDEX
    Using MATCH and INDEX together

    5. Power Functions
    Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
    Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

    6. Statistical Functions
    Finding the middle value with MEDIAN
    Ranking data without sorting with RANK
    Finding the magnitude data with LARGE and SMALL
    Tabulating blank cells with COUNTBLANK

    7. Data Functions
    Understanding Excel date/time capabilities in formulas
    Identifying the day of the week with WEEKDAY
    Counting working days with NETWORKDAYS
    Determining a completion date with WORKDAY
    Tabulating date/time differences with DATEDIF

    8. Math Functions
    Working with rounding functions
    Finding the remainder with MOD and using MOD with conditional formatting
    Building random number generators with RAND and RANDBETWEEN
    Converting a value between measurement systems with CONVERT
    Using the powerful new AGGREGATE function to bypass errors and hidden data

    9. Array Formulas and Functions
    Extending formula capabilities with arrays
    Counting unique entries in a range with an array formula
    Determining frequency distributions with FREQUENCY
    Flipping row/column orientation with TRANSPOSE
    Building analysis via regression techniques with TREND and GROWTH
    Combining multiple functions in arrays

    10. Text Functions
    Locating and extracting data with FIND and MID
    Extracting specific data with LEFT and RIGHT
    Removing excess spaces with TRIM
    Using CONCATENATE with functions
    Adjusting case within cells with PROPER, UPPER, and LOWER
    Adjusting character content with REPLACE and SUBSTITUTE
    Reviewing additional text functions

    11. Financial Functions
    Calculating payments with PMT
    Finding future values with FV
    Determining total amount of future payments with PV

    12. Information Functions
    Working with the IS information functions
    Using error-checking functions: ISERR, ISERROR, IFERROR

    13. Reference Functions
    Getting data from remote cells with OFFSET
    Returning references with INDIRECT


    Final thoughts

  2. amigo

    amigo Redbunker Premium Account Holder

    Feb 25, 2012
    Likes Received:
    Trophy Points:
    Thank you very much.

Share This Page