Excel Functions

The following tutorial is an introduction to Excel functions such as average, standard deviation, maximum, minimum, and conditional counting.

There are many functions in Excel that extend the power of spreadsheet analysis. One additional function is VLOOKUP or HLOOKUP to retrieve values from large tables. This tutorial covers sorting and lookup functions to programmatically select desired data from larger sets.

Data analysis can also be completed with scripting programming languages like MATLAB and Python where the data sets are arrays or matrices instead of tables in a spreadsheet. Click on the appropriate link for additional information and source code.

Generative AI Learning

Use these prompts to test your understanding after completing the tutorial. Direct the AI - build the formulas yourself first.

"Quiz me with 4 questions, one at a time, on spreadsheet functions: what AVERAGE, MEDIAN, STDEV.S, MAX, and MIN each report and when the median beats the mean, how COUNTIF and SUMIF apply a condition and how the criteria string is written, what VLOOKUP does with its table, column index, and range-lookup arguments, and why VLOOKUP with approximate match (TRUE) silently returns wrong values from an unsorted table. Grade my answers and list my misconceptions."
"Build me a 6-row example table of pump test data (flow and power) and a VLOOKUP that should read power for a given flow, but plant ONE subtle error (wrong column index, wrong match mode, or unsorted table with approximate match). I will find it by predicting the returned value before checking. Then reveal the error and show the corrected formula."

Discussion Questions

  1. A teammate deletes an outlier and the standard deviation drops by half. What questions must be answered before that deletion is legitimate?
  2. VLOOKUP with range-lookup TRUE returned a plausible but wrong value. Why is this failure mode more dangerous in engineering work than a #N/A error?
  3. When a lookup table grows to 100,000 rows and twelve conditions, what starts to favor moving the analysis from spreadsheet functions to a script (Python) - and what still favors the spreadsheet?

Course Information

Excel and VBA

Python

MATLAB

MathCAD

Related Courses

Admin