Microsoft Excel for Scientists and Engineers

Create an xy scatter graph in Excel to compare measured temperature (blue) with predicted temperature (orange) over time, and add axis and chart titles.

Format an XY scatter graph in Excel by adding primary major vertical grid lines, customizing both data series with markers and lines, and adjusting axis bounds.

Learn to create an xy chart with two y-axes in Excel by plotting temperature and light data with separate scales, adding axis titles, legend, and formatting for print clarity.

Create a scatter plot with vertical error bars using custom positive and negative values from temperature and voltage data, then format axes and add caps for clarity.

Create a combination chart by plotting temperature as a column and time data as a line on a secondary axis (lumens per square meter), with labeled temperature and time axes.

Compute sums, averages, counts, maximums, and minimums in Excel by typing =sum, =average, =count, =max, and =min, selecting ranges, and using the AutoSum tool.

Compute the weighted average voltage from measured data in Excel by summing V times N and dividing by total observations, then round to two decimal places and autofill.

Explore trigonometric functions in Excel, calculating sine, cosine, and inverse cosine with radians and degrees, using PI() and the radians and degrees functions, while noting rounding errors and formatting zeros.

Explore exponential functions in Excel, including e^x, natural logarithms, and the log function with a base, such as log base two of eight equals three.

Learn how to convert units in Excel with the convert function by supplying a value, the original unit (inches), and the destination unit (millimeters), including unit lists and prefixes.

Learn to use logical expressions in Excel to test conditions with comparison operators (=, >, >=, <, <=, <>), and understand the syntax value1 operator value2 and boolean outcomes.

Explore boolean functions in Excel, including and, or, not, and xor, with syntax, input arguments, and practical true/false test examples to apply logic in data analysis.

Learn a practical excel workflow to evaluate items against p and q thresholds, name cells, apply if tests returning 1/0, and compute pass percentages for each test and both tests.

Master the IF function in Excel by using the equal sign, function name, and three arguments: logical test, true value, and false value; nest tests for multiple outcomes.

Create an Excel quadratic equation solver that inputs coefficients a, b, c, computes the discriminant, and displays real roots, two, one, or none, using appropriate formulas.

Explore the VLOOKUP function to search the first column of a vertical table and return the matching value from a specified column, using exact or approximate matches and absolute references.

Fit a straight line to experimental data using regression analysis, determine the intercept and slope to minimize squared errors, and show R-squared as one minus SSE over TSS.

Calculate temperatures at specific times via interpolation in-range and extrapolation out-of-range, using a straight-line model and Excel's forecast linear function with absolute references to lock the slope and intercept.

Learn to specify a fixed intercept in linear trends with Excel’s trendline and the LINEST function. The lecture shows forcing intercepts to zero or seven and extracting slope and intercept.

Learn multilinear regression by fitting y = a0 + a1 x1 + a2 x2 with log P and 1/T, using LINEST to estimate coefficients and compare calculated versus experimental pressures.

fit a cubic polynomial to temperature versus time data in excel using a trendline of order three to obtain the equation and r-squared.

Explore residuals as the difference between experimental data and predicted data from a fit, assess linear and polynomial fits using residuals plots and r-squared to judge goodness of fit.

Compute the slope of a polynomial at a point with derivative and forward, backward, and central difference schemes, then plot and tailor the tangent line on a chart using Excel.

Use the Analysis ToolPak in Excel to run regression analysis without formulas by importing data, selecting data analysis, and interpreting coefficients and r-squared values.

Use excel's goal seek to find roots of the quadratic 2x^2+3x-1 by plotting the function and setting f(x) to zero. The method reveals x ≈ 0.28 and x ≈ -1.78.

Use Excel solver to find roots of x^2+3x-1=0 by setting the objective to zero, selecting non-linear, adjusting precision, and revealing positive and negative roots.

Explore finding multiple roots of a cubic with Excel's solver. Use initial guesses, compute F(X) and sum of squares, then verify roots -2, 1, and 3, unconstrained then with constraints.

Use the solver to maximize the volume of a 50 by 20 cm cardboard box formed by cutting corners, with x ≤ 10, yielding x ≈ 4.4 cm.

Define x1 and x2 as operation days for two refineries, minimize total cost, and meet minimum high, medium, and low grade oil quotas using a solver with integer-day constraints.

Perform nonlinear regression in Excel using Solver to fit a decaying sine wave y = e^{-x} sin(Bx) by minimizing the sum of squared errors and assessing fit with R^2.

Learn to add two 3x2 matrices in Excel: element-wise addition with autofill, or using named ranges A and B with an array formula.

Learn to multiply a matrix by a scalar in Excel using autofill with absolute references and named ranges to produce an array of results.

Multiply a 3 by 2 matrix by a 2 by 4 matrix in excel using the mmult function, yielding a 3 by 4 result with matching inner dimensions.

Demonstrate how to transpose a 3x2 matrix in Excel using the clipboard transpose option, producing a 2x3 result, then introduce the dynamic transpose function for automatic updates.

Identify square non-singular matrices, compute determinants and inverses, and explore why a matrix is singular due to zero columns, identical rows, or a row being a linear combination of others.

Solve a system of linear equations using matrix operations, defining the coefficient matrix, right-hand side vector, and unknown vector, then compute the inverse to obtain X1, X2, and X3.

Use the Visual Basic Editor to create user defined functions and macros in Excel, understand the difference between functions and subroutines, and apply radians conversion.

Master the if-else structure in Excel formulas and VBA, using condition tests to determine triangle validity and types: equilateral, isosceles, or scalene.

Master the select case structure in Visual Basic, evaluating test expressions and executing the first true case, with examples from simple values to ranges and quadratic roots.

Master the for next loop in Excel to compute pi over four via a counter-driven series, using positive or negative step size and exit for in the Visual Basic Editor.

Explore the Excel object model, where workbooks, worksheets, ranges, and cells form objects and collections; access them via dot notation, indices, or names, and distinguish methods from properties.

learn how to use the for each structure to loop through each cell in a range or each worksheet in a workbook and compute the equivalent resistance in parallel.

Discover the do while and do until dual loops in Excel VBA, and build a user defined function that sums positive numbers using a do while loop.

Learn how VBA lets you use variables without declaring them, and how option explicit enforces declarations to prevent errors. Explore data types like single, double, integer, long, boolean, and string.

Learn to build an Excel VBA array function that computes the resultant force vector by calculating the x and y components from F1 and F2, then returns magnitude and angle.

Record a macro in Excel to automate repetitive tasks, name and describe it, assign a shortcut, choose scope, then review and edit the generated VBA code.

Learn to implement a bisection method macro in Excel VBA that finds a root of a cubic function by iteratively updating interval A and B, computing midpoints, and validating convergence.

Learn to build a macro using arrays to collect numbers greater than five, store them in an array, and display results from the active cell in Visual Basic Editor.

Learn two ways to run a macro in Excel: via the developer tab and by using a form control button. Create and customize the button to run a macro.

Build an Excel user form in the Visual Basic Editor to calculate ideal gas pressure using p = nrt/v, with inputs for moles, volume, and temperature and a calculate button.

Apply the rectangle rule to approximate integrals, compare right- and left-sided methods for cosine x from 0 to pi/2, and improve accuracy by averaging the results.

Apply the trapezoid rule to approximate the integral of cosine x from zero to pi, yielding about 0.9991 with 0.09% error and higher accuracy than the rectangle method.

Apply Simpson's rule to approximate integral of cos x from 0 to pi/2 with parabolic segments and odd number of points, using step size and y0+4y1+y2, summing areas toward 1.

Develop a VBA user defined function to evaluate the cos(x) integral from 0 to pi/2 using Simpson's rule, testing step sizes pi/30 and pi/60 to improve accuracy toward 1.