Excel 2016 Advanced

Master countif, sumif, and averageif to count, sum, or average values that meet a condition, noting single range for countif and two ranges for the others.

Master countifs, sumifs, and averageifs to count, sum, or average with multiple criteria and concatenated condition pairs across ranges, with examples such as gender, age, and exam scores.

Explore common and specialized math functions in Excel 2016 advanced, including absolute values, rounding, logarithms, exponential values, and lists of math, trig, and engineering group functions.

Explore int, round, ceiling, and floor functions in Excel 2016 advanced to master rounding numbers, including integer truncation, decimal precision, and rounding up or down to the nearest multiple.

Excel 2016 advanced covers the abs, sqrt, and sumsq functions, showing how to compute absolute values, square roots, and sums of squares, with notes on handling negatives and distance calculations.

Use the lower, upper, and proper text functions to convert text to lowercase, uppercase, or proper case (first letter of each word uppercase) by passing text or a cell reference.

Discover how the trim and value functions clean spaces and convert text that looks like numbers into real numbers, preventing miscounts in pivot tables and large data sets.

Learn how Excel stores dates as serial numbers and times as fractions of a day, and how formatting and custom formats affect display and recognition.

Explore how Excel interprets two-digit years under the Y2K rule, with examples like 29 versus 30, and learn to avoid ambiguity by using four-digit years.

Explore the today and now functions in Excel 2016 advanced, with no arguments and formatting, and learn to subtract birthdate from today to get days lived, since dates are numbers.

Learn the datedif function to measure days, months, or years between dates with arguments: earlier date, later date, and an interval such as D, M, or Y, though not official.

Explore the most commonly used statistical functions in Excel 2016, compare old and new versions, and learn how to analyze data with averages, standard deviations, and correlations.

Explore Excel's extensive financial functions used by analysts, and learn to access them via the formulas ribbon's financial category to expand your toolkit beyond the basics.

Explore the pmt function in Excel 2016 advanced to calculate loan payments using the rate, term, and principal, treated as a negative outflow, with payments covering principal and interest.

Master four reference functions—index, match, offset, and indirect—in Excel 2016 advanced. Discover their power for specific tasks in the look up and reference group on the formulas ribbon.

Master the index function to locate an item in a rectangular range using the range, row index, and column index; apply it to shipping costs and single-row or single-column cases.

Explore how logical functions test true or false outcomes and return boolean results. Use the is functions and error-checking tools to assess conditions such as even or odd numbers.

Explore Excel's is functions in the information group, including is number, is text, is non-text, is ref, isna, and iserror, and learn to handle lookup errors with the if function.

Use iferror and ifna to handle errors in formulas, returning not found for any error or #N/A in lookups, more efficient than if with iserr or isna in large sheets.

Discover how range names function as absolute references, apply named ranges to revenue formulas, and manage scope with the name manager across workbook and worksheet levels.

Use the formula auditing group to unravel spreadsheet logic, trace dependents and precedence, locate dangling constants and fix deficiencies across worksheets with show formulas, watch, and remove arrows.