How our class can help you.
Our 1-day class shows the essential Excel
skills you need to assemble a wide variety of hi-tech model and analysis tools.
From laying out model and analysis problems; to
building high-powered formulas, lookups, and logic; to implementing
numerical techniques and design optimization; our class covers a wide
range of must know Excel topics.
Join us and our class will show you the design strategies,
assembly methods, and Excel features that you can use to rapidly solve
your problems.
Key Excel topics covered in class.
- Complete formula, debugging, cell reference, and cell naming review
- How to layout model / analysis problems,
build input / output ranges, and link team data in workbooks
- Creating Excel and worksheet tables to
store and filter data
- Using Excel table slicers to rapidly
filter table data
- How to use key Excel worksheet functions in engineering
and science problems
- Performing table lookups and
interpolation with the VLOOKUP, XLOOKUP, INDEX... functions
- How to toggle between lookup tables in a
formula
- Creating formula logic that toggles
equations, detects blank cells and errors, performs If...ElseIf
logic...
- Using Data Validation to create cell
drop down lists and control worksheet cell entry in your UIs
- Using ActiveX controls, shapes, and
Conditional Formatting to build UIs and dynamic technical
illustrations
- Performing integration and
differentiation, solving ODEs, solving linear / nonlinear equations,
using Solver... on the worksheet
- How to format and create charts, limit
lines, secondary axis, carpet plots...
Click to enlarge, scroll < > if hidden.
Detailed class syllabus.
Available:
Public >,
Virtual >, Onsite >
How we run the class:
We focus our Excel class on what our customers need. When class
begins, we analyze those needs and shift our Excel training outline appropriately. We will stress topics or add topics that our customers want. No two
classes are ever the same with EMAGENIT.
Problem Formulation on the
Worksheet
- How to organize problem input and output ranges on the worksheet
- Rapid formula construction and debugging
methods for engineers and scientists
- Using various relative, mixed, and
absolute cell references effectively in formulas
- Dragging and filling formulas over
1-d and 2-d range problems
- How to manage and use cell / range names in your formulas to track model info and data
- How to organize and transmit large amounts of
data between problem / team worksheets and workbooks
- Using string concatenation to
assemble text "on-the-fly" in your formulas; used in lookups,
logic conditions...
Creating Excel and Worksheet
Tables to Store and Filter Data
- Designing worksheet and Excel tables
for easy lookups and data storage
- How to create an Excel table and
control its formats, filters, rows, columns...
- Using Excel table structured references to
build worksheet functions that adapt to changing data structure
- Using Excel's Sort, Filter, Copy,
and PasteSpecial features to rapidly create data reports
- Using an Excel table's quick
analysis tools and Slicers to quickly calculate and filter report data
Using Key Excel Worksheet Functions
in Engineering / Science Problems
- How to create and use basic functions like
SUM, COUNTA, AVERAGE, MAX, MIN, SUBTOTAL... in your formulas
- How to nest worksheet functions in
your formulas to perform advanced engineering / science
calculations
- Using functions like SUMIFS, COUNTIFS,
MAXIFS... in your formulas to analyze data based on logic
- Performing trig and math
operations in your formulas using functions like RADIANS, COS,
ATAN2, ROUND, SQRT, LN, LOG...
- Using the IMAGINARY, COMPLEX... complex number functions in your formulas
- How to use statistical functions like STDEV.S, SKEW, FREQ, RANK... in your
engineering / science formulas
- Designing formulas and using functions like MINVERSE, MMULT, TRANSPOSE...
to perform array operations
Performing Table Lookups and
Interpolation with Functions
- Using VLOOKUP, XLOOKUP, and HLOOKUP to perform basic
engineering / science table lookups
- Using XMATCH and INDEX to perform
complex table lookups as well as return entire rows and columns
- Using functions to perform interpolation on a worksheet table
- Using the INDIRECT function and
string concatenation to toggle worksheet tables
in formulas
Creating Logic Formulas for
Model / Analysis Problems
- Logic review for engineers and
scientists including how to figure out logic triggers in problems
- How to use the relational operators: <, >, <=, >=, < >, =
and IF, AND, OR, NOT... to build logic formulas
- Using simple logic to activate and
deactivate a worksheet formula
- Using the IF and IS functions to determine if cells are blank, contain numbers, contain text, have errors...
- Creating formulas that appear and disappear when needed using the IF and IS functions
- Creating IF...ELSEIF logic in formulas using nested IF's or the IFS function
- Using the IF functions to toggle
between different engineering and science formulas in a cell
Building Worksheet UIs and
Dynamic Displays for Your Problems
- Using Data Validation to create
cell drop down lists and control number, date, and text entry in
your UIs
- How to create self-adjusting cell drop
down lists using
dynamic names and names piggy backed on Excel tables
- Creating and integrating ActiveX
and Form controls in worksheet UIs
- Using Conditional Formatting to flag (i.e., format) cells
in your problems based on logic
triggers
- Dynamically displaying cell values
in shapes and creating linked pictures that update in your UIs
Performing Integration and
Differentiation on the Worksheet
- Performing numerical integration in Excel using the Trapezoid, Simpson's 1/3, and Simpsons 3/8 rule
- Performing differentiation in Excel using the Forward Difference, Central Difference, and Backward Difference expressions
Solving Linear and Non-Linear
Equations on the Worksheet
- Performing Newton's method on the
worksheet to solve a non-linear equation
- Using Goal Seek to solve a non-linear
equation on the worksheet
- Performing the Gauss Seidel method
on the worksheet to solve a system of linear equations
- Using the Inverse Matrix
method on the worksheet to solve a system of linear equations
Performing Simulations and
Using Solver on the Worksheet
- Performing the 4th Order Runge Kutta method
on the worksheet to solve single and coupled ODE's
- Using the Equilibrium method to
solve a boundary value problem on the worksheet
- Using Goal Seek to solve a Shooting method boundary value problem
on the worksheet
- What is Solver and how can it be used in
engineering and science to perform design optimization?
- Design optimization problem
/ constraint formulation on the worksheet
- How to load and run Solver on
single and multiple worksheets
Building and Formatting
Engineering / Science Charts
- How to create XY Scatter, Log,
Column, Line, Pie... charts on a worksheet or chart sheet
- Formatting chart data series, data labels,
gridlines, axis limits...
- Creating limit lines and a secondary axis
for your charts
- How to display multiple tables in
a chart, great for trade studies, differing x axis increments...
- How to combine two or more chart types in one chart (i.e., combo chart)
Additional Classes:
If you need to contact us about
our class.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
You can email us at info@emagenit.com >
Contact us by form.
_EMAGENIT_2019.png)
