Practical Excel 2010 – Intermediate & Advanced

Explore countif, sumif, and averageif functions to count, sum, or average values that meet a condition, using gender, age, and exam scores, with concatenation and absolute references.

Explore Excel 2010 math functions, including absolute values, rounding, logarithms, exponentials, trigonometric calculations, and the Roman conversion function. Learn where to access these functions on the formula ribbon.

Explore int, round, ceiling, and floor in practical excel 2010, demonstrating rounding behaviors, negative numbers, and rounding to decimals and multiples.

Explore the absolute value, square root, and sum of squares functions in Excel. Learn how abs converts negatives, sqrt finds roots, and sums of squares support distance calculations.

Explore ln and exp functions, mastering natural logarithms to base e and the exponential function, with positive input requirements, inverses, and Excel functions like log10 and log with a base.

Explore rand and ran between functions to generate random or pseudo random numbers for spreadsheet simulations, with examples of dice and two-dice sums, and techniques to freeze results.

Discover Excel's text functions and the exotic function that converts a number to Thai text, accessed via the text dropdown on the formulas ribbon.

Apply the lower, upper, and proper text functions to normalize case in Excel data. Use proper for names, and pass a text or cell reference as the single argument.

Trim leading and trailing spaces in text to prevent extra categories in pivot tables. Use the value function to convert text that looks like numbers into real numbers for arithmetic.

Parse text in Excel 2010 with Len, Left, Right, Mid, and Find by identifying patterns to extract names. Build reusable parsing formulas and handle simple or complex name formats.

Learn how Y2K affects Excel's two-digit year interpretation, with 29 cutoff placing 29 or less in the current century and 30 or more in the previous; fix by four-digit years.

Learn how to use the today and now functions in Excel 2010, format their outputs, and calculate days alive by subtracting a birthdate from today, recognizing dates as serial numbers.

Explore Excel date functions year, month, day, and weekday on dates, understand errors when dates are invalid, format results with lookups and custom formats, use absolute addresses, copy formulas down.

Explore the datedif function, an unofficial Excel date tool that calculates the number of days, months, or years between two dates using three arguments: earlier date, later date, and interval.

Build dates with the Date function from year, month, and day, returning serial values you format as dates, and use DateValue to convert legacy date labels.

Use networkdays to count workdays between dates, excluding weekends and holidays, and use workday to find the next start date after a project; Excel 2010 adds variants to exclude Sundays.

Explore excel's statistical functions for data summarization, including averages, standard deviations, and correlations, and compare pre-2010 and 2010 versions with the formulas ribbon updates.

Use min and max to find smallest and largest values in numeric lists, illustrated with January and February sales from six salespeople; min and max work for numbers, not text.

Discover how median, quartiles, and percentiles describe data distribution, with the median as the 50th percentile and the 25th and 75th percentiles matching the first and third quartiles.

Explore standard deviation and variance in Excel 2010, compare sample and population versions, and apply empirical rules to interpret variability around the mean with one to three standard deviations.

Learn to rank observations in ascending or descending order with Excel 2010, and use large and small to find the five largest and smallest values, including tie handling.

Explore how Excel 2010 introduces new statistical functions with period names, retains old ones for backward compatibility, and shows how to access them via the formulas ribbon.

Explore Excel's financial functions and learn how to access the financial category on the formulas ribbon to master the full set of tools used by financial analysts.

Use the PMT function to calculate loan payments from the rate, term, and principal (negative); for a $30,000 loan at 5% over 36 months, payments are about $900.

Explore the time value of money by calculating NPV and XNPV for cash flow streams using a discount rate, including upfront outflows and irregular dates.

Learn to evaluate investments using IRR and XIRR by equating discounted cash inflows to the initial outlay in Excel, and compare to NPV.

Discover four reference functions: index, match, offset, and indirect, found in the look up and reference group on the formulas ribbon to power up your Excel skills.

Master the index function in a rectangular range by using its three arguments—the row index, the column index, and the range—to return a specific item, with single-row or single-column options.

Use the offset function to return a range offset from a starting cell, creating copyable formulas that automatically update when delays in receipts or payments change.

Master the iferror function to check expressions for errors and return a friendly value like not found, improving efficiency over nested if and iserror formulas in lookups.

Learn the r1c1 notation for Excel formulas, using row and column references with relative and absolute addressing. See how to switch to this style in Excel options and apply sums.

Learn how the formula auditing group uses the evaluate formula tool to unravel complex formulas, including nested ifs with and/or conditions, and verify logic to categorize status and spend thresholds.

Explore external formula references to cells in other worksheets and workbooks, including sheet names, exclamation marks, and square-bracket workbook notation, and how file moves affect links.

Explore how array formulas in Excel perform multiple calculations on ranges, using Ctrl+Shift+Enter, and apply matrix multiplication and matrix inverse to solve systems of equations.