Admin
مدير المنتدى
عدد المساهمات : 18745
التقييم : 34763
تاريخ التسجيل : 01/07/2009
الدولة : مصر
العمل : مدير منتدى هندسة الإنتاج والتصميم الميكانيكى
 |  موضوع: كتاب Excel for Scientists and Engineers Numerical Methods الأحد 18 ديسمبر 2022, 7:18 pm | |
| 
أخواني في الله
أحضرت لكم كتاب
Excel for Scientists and Engineers Numerical Methods
E.Joseph Bill
و المحتوى كما يلي :
Summary of Contents
Detailed Table of Contents v11
Preface xv
Acknowledgments . xix
About the Author . xix
Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Introducing Visual Basic for Applications 1
Fundamentals of Programming with VBA 15
Worksheet Functions for Working with Matrices . 57
Number Series . 69
Interpolation 77
Differentiation . 99
Integration . 127
Roots of Equations 147
Numerical Integration of Ordinary Differential Equations
Part I: Initial Conditions 217
Numerical Integration of Ordinary Differential Equations
Part 11: Boundary Conditions . 245
Partial Differential Equations 263
Nonlinear Regression Using the Solver . 313
Random Numbers and the Monte Carlo Method . 341
Systems of Simultaneous Equations 189
Linear Regression and Curve Fitting . 287
APPENDICES
Appendix 2 Shortcut Keys for VBA . 387
389
Appendix 4 Some Equations for Curve Fitting . 409
Appendix 5 Engineering and Other Functions 423
Appendix 6 ASCII Codes 427
Appendix 7 Bibliography 429
Appendix 8 Answers and Comments for End-of-Chapter Problems 431
Appendix 1 Selected VBA Keywords . 365
Appendix 3 Custom Functions Help File
INDEX . 443
Contents
Preface .: xv
Acknowledgments . xix
About the Author . xix
The Visual Basic Editor . 1
Visual Basic Procedures . 4
There Are Two Kinds of Macros . 4
The Structure of a Sub Procedure 4
The Structure of a Function Procedure 5
Using the Recorder to Create a Sub Procedure 5
The Personal Macro Workbook . 7
Running a Sub Procedure 8
Assigning a Shortcut Key to a Sub Procedure . 8
Entering VBA Code . 9
Creating a Simple Custom Function 10
Using a Function Macro 10
A Shortcut to Enter a Function 12
Some FAQs 13
Chapter 2 Fundamentalsof Programming with VBA 15
Components of Visual Basic Statements 15
Operators 16
Variables 16
Objects, Properties, and Methods 17
Objects . 17
Properties . 17
Using Properties . 19
Functions 20
Using Worksheet Functions with VBA . 22
Some Useful Methods 22
Other Keywords . 23
Program Control . 23
Branching . 23
Logical Operators 24
Select Case . 24
Looping 24
For .Next Loop 25
Do While . Loop . 25
Chapter 1 Introducing Visual Basic for Applications 1
vii .
Vlll EXCEL: NUMERICAL METHODS
For Each .Next Loop . 25
Nested Loops . 26
Exiting from a Loop or from a Procedure 26
VBA Data Types 27
The Variant Data Type 28
Subroutines . 28
VBA Code for Command Macros 29
Objects and Collections of Objects 29
"Objects" That Are Really Properties 30
You Can Define Your Own Objects 30
Methods . 31
Some Useful Methods 31
Two Ways to Specify Arguments of Methods . 32
Arguments with or without Parentheses 33
A Reference to the Active Cell or a Selected Range . 33
A Reference to a Cell Other than the Active Cell 34
Scoping a Subroutine . 29
Making a Reference to a Cell or a Range . 33
References Using the Union or Intersect Method 35
Examples of Expressions to Refer to a Cell or Range . 35
Getting Values from a Worksheet . 36
Sending Values to a Worksheet . 37
Interacting with the User 37
MsgBox 37
MsgBox Return Values 39
lnputBox . 39
Visual Basic Arrays 41
Dimensioning an Array 41
Use the Name of the Array Variable to Specify the Whole Array 42
Multidimensional Arrays . 42
Declaring the Variable Type of an Array 42
Returning the Size of an Array 42
Preserving Values in Dynamic Arrays . 43
Passing Values from Worksheet to VBA Module 44
Create an Array Automatically . 45
Create an Array Automatically . 45
An Array of Object Variables 45
Dynamic Arrays . 43
Working with Arrays in Sub Procedures:
A Range Specified in a Sub Procedure Can Be Used as an Array .44
Some Worksheet Functions Used Within VBA
Some Worksheet Functions Used Within VBACONTENTS ix
Working with Arrays in Sub Procedures:
A One-Dimensional Array Assigned to a Worksheet Range
Passing Values from a VBA Module to a Worksheet 45
Can Cause Problems . 46
Custom Functions . 47
Specifying the Data Type Returned by a Function Procedure . 47
Specifying the Data Type of an Argument 47
Returning an Error Value from a Function Procedure . 48
A Custom Function that Takes an Optional Argument . 48
Arrays in Function Procedures . 48
A Range Passed to a Function Procedure Can Be Used as an Array .48
Passing an Indefinite Number of Arguments:
Using the ParamArray Keyword 49
Returning an Array of Values as a Result 49
Creating Add-In Function Macros . 50
How to Create an Add-In Macro . 51
Testing and Debugging 51
Tracing Execution 52
Stepping Through Code . 52
Adding a Breakpoint 52
Examining the Values of Variables During Execution 54
Chapter 3 Worksheet Functions for Working with Matrices 57
Arrays, Matrices and Determinants 57
Some Types of Matrices 57
Excel's Built-in Matrix Functions 60
Some Additional Matrix Functions 63
Problems . 66
Chapter 4 Number Series 69
Evaluating Series Formulas 70
Using Array Constants to Create Series Formulas 70
Using the ROW Worksheet Function to Create Series Formulas 71
Examining the Values of Variables While in Break Mode 53
An Introduction to Matrix Mathematics . 58
The INDIRECT Worksheet Function 71
Using the INDIRECT Worksheet Function
with the ROW Worksheet Function to Create Series Formulas .72
The Taylor Series: An Example . 73
Problems . 75
The Taylor Series . 72X EXCEL: NUMERICAL METHODS
Chapter 5 Interpolation 77
Using Excel's Lookup Functions to Obtain Values from a Table 77
Using the LOOKUP Function to Obtain Values from a Table . 79
Creating a Custom Lookup Formula to Obtain Values from a Table 80
Interpolation . 83
Linear Interpolation in a Table by Means of Worksheet Formulas .83
Linear Interpolation in a Table by Means of a Custom Function 86
Cubic Interpolation in a Table by Using the TREND Worksheet Function .89
Obtaining Values from a Table 77
Using VLOOKUP to Obtain Values from a Table 78
Using Excel's Lookup Functions
to Obtain Values from a Two-way Table 81
Linear Interpolation in a Table by Using the TREND Worksheet Function 85
Cubic Interpolation 87
Linear Interpolation in a Two-way Table
Cubic Interpolation in a Two-way Table
Cubic Interpolation in a Two-way Table
by Means of Worksheet Formulas 90
by Means of Worksheet Formulas 91
Problems . 96
Chapter 6 Differentiation 99
Calculating First and Second Derivatives 100
by Means of a Custom Function . 93
First and Second Derivatives of Data in a Table 99
Using LINEST as a Fitting Function 105
Derivatives of a Worksheet Formula 109
Derivatives of a Worksheet Formula Calculated by Using
a VBA Function Procedure 109
First Derivative of a Worksheet Formula Calculated by Using
the Finite-Difference Method . 110
The Newton Quotient . 110
Derivative of a Worksheet Formula Calculated by Using
the Finite-Difference Method . 111
First Derivative of a Worksheet Formula Calculated by Using
a VBA Sub Procedure Using the Finite-Difference Method 112
First Derivative of a Worksheet Formula Calculated by Using
a VBA Function Procedure Using the Finite-Difference Method 115
Improving the VBA Function Procedure . 118
Second Derivative of a Worksheet Formula 120
Concerning the Choice of Ax for the Finite-Difference Method . 123
Problems . 124CONTENTS xi
Chapter 7 Integration 127
Area under a Curve 127
Calculating the Area under a Curve Defined by a Table of Data Points .129
by Means of a VBA Function Procedure 130
Calculating the Area under a Curve Defined by a Table of Data Points
Calculating the Area under a Curve Defined by a Formula . 131
Area between Two Curves . 132
Integrating a Function 133
Integrating a Function Defined by a Worksheet Formula
Gaussian Quadrature 137
by Means of a VBA Function Procedure 133
Integration with an Upper or Lower Limit of Infinity . 140
Distance Traveled Along a Curved Path 141
Problems . 143
Chapter 8 Roots of Equations 147
A Graphical Method 147
The Interval Method with Linear Interpolation
The Interval-Halving or Bisection Method 149
The Regula Fulsi Method with Correction for Slow Convergence .153
The Newton-Raphson Method . 154
The Secant Method 160
The Newton-Raphson Method Using Circular Reference and Iteration 161
A Newton-Raphson Custom Function . 163
Using Goal Seek .to Find the Point of Intersection of Two Curves .174
(the Regula Fulsi Method) 151
Using Goal Seek 156
Bairstow's Method to Find All Roots of a Regular Polynomial 166
Finding Values Other than Zeroes of a Function 174
Using the Newton-Raphson Method
to Find the Point of Intersection of Two Lines . 176
Using the Newton-Raphson Method to Find Multiple Intersections
of a Straight Line and a Curve 178
A Goal Seek Custom Function 180
Problems . 185
Chapter 9 Systems of SimultaneousEquations 189
Cramer's Rule . 190
Solving Simultaneous Equations by Matrix Inversion 191
Solving Simultaneous Equations by Gaussian Elimination . 191
The Gauss-Jordan Method . 196
Solving Linear Systems by Iteration 200
The Jacobi Method Implemented on a Worksheet 200xii EXCEL: NUMERICAL METHODS
The Gauss-Seidel Method Implemented on a Worksheet 203
The Gauss-Seidel Method Implemented on a Worksheet
Using Circular References 204
A Custom Function Procedure for the Gauss-Seidel Method 205
SolvingNonlinear Systems by Iteration . 207
Newton's Iteration Method 207
Problems 213 .
Chapter 10 Numerical Integration of Ordinary Differential Equations
Part I: Initial Conditions 217
Solving a Single First-Order Differential Equation 218
Euler's Method . 218
The Fourth-Order Runge-Kutta Method . 220
Fourth-Order Runge-Kutta Method Implemented on a Worksheet .220
Runge-Kutta Method Applied to a Differential Equation
Fourth-Order Runge-Kutta Custom Function
Involving Both x and y . 223
for a Single Differential Equation with the Derivative Expression
Coded in the Procedure 224
for a Single Differential Equation with the Derivative Expression
Fourth-Order Runge-Kutta Custom Function
Passed as an Argument . 225
Systems of First-Order Differential Equations . 228
for Systems of Differential Equations 229
Predictor-Corrector Methods., 235
A Simple Predictor-Corrector Method . 235
Higher-Order Differential Equations . 238
Fourth-Order Runge-Kutta Custom Function
A Simple Predictor-Corrector Method
Utilizing an Intentional Circular Reference 236
Problems . 241
Part II:Boundary Conditions 245
Chapter 11 Numerical Integration of Ordinary Differential Equations
The Shooting Method . 245
An Example: Deflection ofa Simply Supported Beam . 246
Solving a Second-Order Ordinary Differential Equation
Solving a Second-Order Ordinary Differential Equation
by the Shooting Method and Euler's Method . 249
by the Shooting Method and the RK Method . 251
Finite-Difference Methods . 254
by the Finite-Difference Method 254
Solving a Second-Order Ordinary Differential EquationCONTENTS X l l l .
Another Example . 258
A Limitation on the Finite-Difference Method 261
Problems . 262
263
Elliptic. Parabolic and Hyperbolic Partial Differential Equations . 263
Elliptic Partial Differential Equations 264
Replacing Derivatives with Finite Differences . 265
An Example: Temperature Distribution in a Heated Metal Plate 267
Parabolic Partial Differential Equations . 269
Solving Parabolic Partial Differential Equations: The Explicit Method 270
An Example: Heat Conduction in a Brass Rod 272
The Crank-Nicholson or Implicit Method 274
An Example: Vapor Diffusion in a Tube . 275
Vapor Diffusion in a Tube Revisited . 277
Vapor Diffusion in a Tube (Again) 279
A Crank-Nicholson Custom Function . 280
Vapor Diffusion in a Tube Solved by Using a Custom Function 282
Hyperbolic Partial Differential Equations 282
Replacing Derivatives with Finite Differences . 282
An Example: Vibration of a String 283
Problems . 286
Chapter 13 Linear Regression and Curve Fitting 287
Linear Regression . 287
Least-Squares Fit to a Straight Line 288
Least-Squares Fit to a Straight Line Using the Worksheet Functions
SLOPE, INTERCEPTand RSQ 289
Least-Squares Fit to a Straight Line Using LINEST 292
Multiple Linear Regression Using LINEST 293
Handling Noncontiguous Ranges of known-x's in LINEST . 297
A LINEST Shortcut 297
LINEST's Regression Statistics 297
Linear Regression Using Trendline . 298
Limitations of Trendline 301
Importing Trendline Coefficients into a Spreadsheet
by Using Worksheet Formulas . 302
Using the Regression Tool in Analysis Tools 303
Limitations of the Regression Tool . 305
Chapter 12 Partial Differential Equations
Solving Elliptic Partial Differential Equations:
Solving Parabolic Partial Differential Equations:
Solving Hyperbolic Partial Differential Equations:
Multiple Linear Regression 291xiv EXCEL: NUMERICAL METHODS
Importingthe Trendline Equation from a Chart into a Worksheet 305
Problems . 309
Chapter 14 Nonlinear Regression Using the Solver 313
Nonlinear Least-Squares Curve Fitting 314
Introducing the Solver . 316
How the Solver Works . 316
Loading the Solver Add-In 317
Why Use the Solver for Nonlinear Regression? 317
Nonlinear Regression Using the Solver: An Example . 318
Some Notes on Using the Solver . 323
Some Notes on the Solver Options Dialog Box . 324
When to Use Manual Scaling 326
Statisticsof Nonlinear Regression . 327
The Solver Statistics Macro . 328
Problems . 332
Chapter 15 Random Numbers and the Monte Cario Method 341
Random Numbers in Excel . 341
How Excel Generates Random Numbers 341
Adding "Noise" to a Signal Generated by a Formula 344
Some Notes on the Solver Parameters Dialog Box . 323
Be Cautious When Using Linearized Forms of Nonlinear Equations .329
Using Random Numbers in Excel . 342
Selecting Items Randomly from a List 345
Random Sampling by Using Analysis Tools . 347
Simulatinga Normal Random Distribution of a Variable . 349
Monte Carlo Simulation . 350
Monte Carlo Integration . 354
The Area of an Irregular Polygon 354
Problems . 362
APPENDICES 363
Appendix 1 Selected VBA Keywords . 365
Appendix 2 Shortcut Keys for VBA . 387
Appendix 3 Custom Functions Help File 389
Appendix 4 Some Equations for Curve Fitting . 409
Engineering and Other Functions 423
Appendix 6 ASCII Codes 427
Appendix 7 Bibliography 429
Appendix 8 Answers and Comments for End-of-Chapter Problems 431
Appendix 5
INDEX 443
Index
A
ABS worksheet function 256,279
active cell, reference to 35
add a breakpoint 55, 56
add a shortcut key 15
Add Trendline... 298,299
Add Watch... 55, 57
Add, Change, Delete (Solver
Add-In function macros 53
Add-In macro, create an 53
Add-Ins 303
AddIns folder 53
addition, matrix 58
additional matrix functions 63
Address property 1 16
alternating series 69
Analysis ToolPak 289,303,343,347,
And keyword 17,25
approximation error 110
area
parameters) 324
425
between two curves 132
of an ellipse 144
of an irregular polygon 354
underacurve 127, 129, 130, 131,
132
argument,
data type of 49
optional 50
two ways to specify 34
with or without parentheses 34
indefinite number of 5 1
naming 11, 17
operators 17
arguments,
Arr (custom function) 65,297
array
constants 71
function 292
Array keyword 53
array
of values as a result 52
dimensioning 43
dynamic 45
multidimensional 44
one-dimensional 48
size of 44
variable type of 44
arrays 43,51,57,64
scaling 64
As keyword 50
ASCII codes 429
assignment statements. 16
Assume Linear Model (Solver options)
Assume Non-Negative (Solver
325
options) 325
B
backward difference 99, 103
Bairstow's method 166
baseball trajectory (problem) 242
bisection method 149
Booleankeyword 29
boundary-value problem 245
branching 24
breakmode 56
breakpoint 55
Buffon's needle (example) 350
buttons parameter of MsgBox 40
By Changing Cell (Goal Seek) 158,
By Changing Cell (Solver) 323
By Changing Cells box 320
181
443444 ~~ EXCEL:NUMERICALMETHODS
C
calculating derivatives 100
cubic fitting function for 105
Calculation tab 205
Calculation tab, in Tools-Options
158, 162, 180
calibration curve (problem) 309
Call keyword 30
cell, reference to 35
Cells keyword 36,37
central difference 99, 103
changing cells (Solver) 316,326
Chartwizard method 35
CheckSpellingmethod 35
chemical kinetics 243
choice of Ax 123
circular reference 161, 212,236,267
Clausius-Clapeyron equation 289
Codewindow 1
code, stepping through 55
coefficient of determination 296
coefficients, linear in the 289
regression 287,289,292
collections of objects 3 1
COLUMN worksheet function 256,
259,278,279
command macros 4
comparison operators 17
computing derivatives, formulas for
constraints, in Solver model 324
Convergence (Solver options) 325
convergence, slow 153
convergent series 69
ConvertFormula method 117, 118
correlation coefficient, R 288
Cramer's rule 169, 190
Crank-Nicholson 274,280
create an Add-In macro 53
critical points 100
cubic
equation 147
104
fitting data to 295
fittingfunction for calculating
derivatives 105
interpolating polynomial 129
interpolation 87
interpolation in a table 89
interpolation in a two-way table 9 1,
93
curve
areaundera 127, 129, 130, 131,
logistic 419, 420
normal error 421
plateau 416
slopeofa 155
133
curved path, distance traveled along a
curves, area between two 132
curves, intersection of 176
custom function 11,49, 389
141
dydx 119,120,123
d2ydx2 120, 121, 123
Integrate 134
Integrates 136
JntegrateT 136
custom lookup formula 80
CVErr keyword 50
D
Data Analysis... 303, 347
debug toolbar (VBA) 57
Debug... 55
debugging 54
deck of cards (problem) 362
decrease, exponential 412
definite integral 127
derivative
calculating first and second 99, 104
cubic fitting function for calculating
first 99, 100
formulas for computing 104
105INDEX 445
of a function 109
of a worksheet formula 110, 1 1 1,
partial 168
partial 287
second 99, 100, 120
determinant 57, 58,60, 190
degrees of freedom 297
diagonal elements, in SolvStat macro
diagonal matrix 58
diatomic molecule (example) 183
difference formulas 103
difference, backward 99, 103
112
328
central 99
forward 103
first-order 2 18
higher-order 238
ordinary 217
partial 217
second-order 245,259
systems of first-order 228
systems of simultaneous 229
digits, frequency of occurrence of
Dim keyword 43,44
dimensioning an array 43
discontinuous functions (Solver) 323
distance traveled along a curved path
Do While...loop 27
Doublekeyword 29
double exponential 413
double reciprocal plot 4 17
dynamic array 45
differential equation 2 17
differential equations, systems of 229
(problem) 362
141
E
ellipse, area of 144
elliptic partial differential equation
263,264,267
empirical fitting function 294
Engineering functions 343,425
entering VBA code 9
equation, exponential 4 11
equation, Michaelis-Menten 416
equations, simultaneous 65
error surface 3 15
error value, returning 50
error, approximation 111
error-square sum 314
estimation of n 353,354,362
Euler's method 218,219,222, 247,
Evaluatemethod 116, 117, 134,225
evaluating series formulas 70
event-handler procedures 3
examining the values of variables 56
Exit keyword 28
exiting from a loop 28
from a procedure 28
explicit method 270
exponential
250,258
curve 289
decrease 412
double 413
equation 411
growth 412
external references (Solver) 323
F
F9 (function key) 71
Fick's second law 264
finite-difference method 254, 258
first and second derivatives 99
first derivative 99, 104, 155
of a specific worksheet formula
of a worksheet formula 1 11, 115
systems of 228
110
first-order differential equations 2 I8
fitting function, empirical 294
fitting functions, Trendline 302446 EXCEL:NUMERICALMETHODS
For Each...Next loop 27
For...Next loop 26
Formulaproperty 116, 133,225
formulas for computing derivatives
forward difference 99, 103
fourth-order polynomial, Lagrange
fourth-order Runge-Kutta, see Rungefrequency of occurrence of digits
Frontline Systems Inc 316
F-statistic 298
Function Arguments dialog box 13
function linear in the coefficients 287
function macro 11
Functionprocedure, structure of a 5
function 21
104
87
Kutta
(problem) 362
Add-In 53
custom 11,49, 389
derivative of a 109
engineering 425
logistic 418
naming 11
partial derivative of 287
shortcutto enter a 13
trigonometric 422
G
GaussElimcustom function 194, 196
Gaussian
curve 421
elimination 191,192
quadrature 137, 138
Gauss-Jacobimethod 200,205
GaussJordan custom function 197,
Gauss-Jordan method 196
Gauss-Seidel custom function 205
Gauss-Seidelmethod 200,203
198
Generalized Reduced Gradient
getting Trendline coefficients into a
global minimum (Solver) 323,324
Goal Seek... 156, 159, 174, 175,
178,251
GoalSeek custom function 180, 182,
183
graphical method 147
growth, exponential 412
Guess (Solver parameters) 324
(Solver) 316
spreadsheet 302
H
heat conduction in a brass rod
(example) 272
hierarchy of objects 31
higher-order differential equations
Hill slope 419
HLOOKUPworksheet function 77
hyperbolic partial differential equation
238
use of, in Solver models 323
263,282
I
identity matrix 63
IF worksheet function 278,279
If...Then statement 25
If...Then... Elself statement 25
If...Then...Else statement 25
implicit intersection 107
implicit method (PDE) 274
indefinite integral 127
indefinite number of arguments 5 I
INDEX worksheet function 80, 8 1,
84, 106,346,349
INDIRECTworksheet function 72,
107,256,259,279
inflection point 100, 101
information functions, VBA 23
initial conditions 2 18INDEX 447
initial estimates for Solver 323
InputBox function 41
InputBox method, syntax of the 42
insert a module sheet 2
Insert Function dialog box 12
Integerkeyword 29
integral, definite 127
Integratecustom function 134
Integratescustom function 136
IntegrateTcustom function 136
integrating a function 133
integration, lower and upper limits of
indefinite 127
134, 140, 142
symbolic 127
INTERCEPT worksheet function 289,
intercept, least-squares 288
InterpCcustom function 88, 92
InterpC2custom function 93,95
InterpLcustom function 86, 90
interpolation 77, 83
cubic 87
linear 83, 85, 86
Intersectmethod 37
intersectionof two lines 174, 178
interval method with linear
interpolation 151
interval-halving method 149
intrinsically nonlinear 313
inverse matrix, in SolvStat macro 328
inverse of a matrix 60
ISERROR worksheet function 360
Iteration box 200, 205,237
291
J
Jacobi method 200
K
Keep Solver Solution 322
keywords, VBA 365
L
Lagrange fourth-order polynomial 87
Laplace's equation 264,266
LBoundfunction 44
least squares
curve fitting 3 16
fit to a straight line 288,289,292,
294,316
intercept 288
slope 288
Legendre polynomials 137
limitations of the Regression tool 305
limits of integration, lower and upper
linear
of Trendline 301
134, 140, 143
equations, systems of 190
in the coefficients 287,289
interpolation 83, 85, 86
interpolation in a two-way table 90
least squares curve fitting 3 16
regression 287,289
using Trendline 298
linearized forms of nonlinear
equations 329
line-continuation character 10
LINEST shortcut 297
LINEST worksheet function 65, 105,
LINEST's regression statistics 297
Lineweaver-Burke 330,417
liquid flow (problem) 243
local minimum 323
logarithmic 415
logical operators 17,25
logistic curve 418,419,420
lookup functions 77
LOOKUP worksheet function 79
loop, exiting from 28
looping 26
loops, nested 28
292, 293,294, 296,297448 EXCEL:NUMERICAL METHODS
lower and upper limits of integration
134, 140, 141
M
Macro Name list box 15
macro, function 11
macros, Add-In 53
command 4
two kinds of 4
main diagonal 57
male children (problem) 362
manual scaling (Solver) 326
Marquardt-Levenberg algorithm 316
MATCHworksheet function 80, 84,
mathematical functions, VBA 21
matrices 57
matrix
106,107,346
addition 58
elements 57
functions, additional 63
inversion 60,62, 19 1,276
mathematics 58
multiplication 59
subtraction 58
transposition 60
diagonal 58
identity 63
in SolvStat macro 328
square 57
symmetric 58
transpose of a 62
tridiagonal 5 8
unit 58
Max Time and Iterations (Solver
MAX worksheet function 80,279
Maximum Change parameter 159,
Maximum Change (Solver options)
options) 324
162,205,237
325
MDETERMworksheet function 60,
megaformula 107,347
methane hydrate 289
method of steepest descent 3 16
methods, VBA 18,23,33
Michaelis-Menten 330, 416
MIDENTworksheet function 63
MIN worksheet function 279
MINDEXworksheet function 64
MINVERSEworksheet function 60,
MMULT worksheet function 62, 191,
Module from the Insert menu 11
module sheet, rename a 14
Monte Carlo method 342,350, 354
MSCALEworksheet function 64
MsgBox
63, 190, 191
191,257,261,276
257,261,276
inserta 2
function 39
return values 41
buttons parameter of 40
multidimensional array 44
multiple linear regression 289, 293,
multiplication, matrix 59
410
scalar 59
N
named formulas 107
naming functions and arguments 11
nested loops 28
Newton quotient 110
Newton-Raphson
variables or arguments 17
custom function 163
method 154, 155, 161, 176, 178
Newton's iteration method 207
nodes, in Gaussian quadrature 137
noise 103,342,344
non-contiguous ranges 297nonlinear equations
linearized form of 329
systems of 207
nonlinear least squares curve fitting
using the Solver 314,316,317
nonlinear regression, statistics of 327
nonlinear, intrinsically 313
normal error curve 421
normal random distribution,
simulating 349, 421
NORMINVworksheet function 349
Not keyword 17,25
number series 69
NumberFormatproperty 19
numerical differentiation 155
0
object browser 32
objective function (Solver) 316, 326
object-oriented programming language
objects 18,31
18
collections of 31
hierarchy of 31
obtaining values from a table 77
occurrence of digits, frequency of
OFFSETworksheet function 106
On Error GoTo statement 119
one-dimensional array 48
operators 17
(problem) 362
arithmetic 17
comparison 17
logical 17, 25
optimization 316
Option Base 1 44,47, 51
Option Explicit 10
optional argument 50
Optionalkeyword 50
Or keyword 17,25
ordinary differential equation 217
Orvis, William J. 88
P
n,estimation of 353,354,362
panel 127, 140
parabolic partial differential equation
263,269,274
ParamArraykeyword 5 1,52,66
parentheses, arguments with or
partial derivative 168, 265, 287, 328
partial differential equation 217,263
without 34
in SolvStat macro 328
elliptic 263,264,267
hyperbolic 263,282
parabolic 263,269,274
passing values 46
Pearson product moment correlation
pendulum motion (problem) 242
Personal Macro Workbook 8
perturbation factor (Solver) 316
pH titration curve (example) 100
phase diagram 289
pit-mapping 315
plateau curve 416
polygon, area of 354
polynomial
coefficient 289
cubic interpolating 129
Lagrange fourth-order 87
Legendre 137, 138
regression 410
roots of a regular 166
position of a value in an array 64
power series 69
Precision and Tolerance (Solver
options) 324
predictor-corrector methods 235
Preservekeyword 45
principal diagonal 57
Private 31
procedure, exiting from 28
running a Sub 8450 EXCEL:NUMEFUCALMETHODS
structureof a Function 5
structure of a Sub 5
Visual Basic 4
program control 24
Project Explorer window 1,2
properties 18, 19
Properties window 1,4, 14
Public 31
Range object 20
Q
quadratic interpolating polynomial
quadrature 127
Quick Watch... 57
128
R
R2 288,296,298
RANDworksheet function 342,343,
345
RANDBETWEENworksheet function
343
random
number generator 341
sampling 345, 347
Rangekeyword 37
range, reference to 35
read-only 19
Record Macro dialog box 7
Record New Macro... 6
Recorder, using the 6
ReDim keyword 45
reference
to a cell 35
to a range 35
to the active cell 35
circular 212
refractive index of benzene (problem)
97
regression
analysis 287,288
coefficients 287,289,292
linear 287,289
multiple 410
multiple linear 289
parameters, standard deviation of
polynomial 4 10
statistics of nonlinear 327
regression statistics
from LINEST 297
mathematical relationships 297
limitations of 305
using 303
Regula Falsi method 151,153
regular polynomial, roots of 166
rename a macro 14
a module sheet 14
Reset All (Solver parameters) 324
result, array of values as 52
return statement 6
return values, MsgBox 41
returning an error value 50
RMSD (root-mean-square deviation)
roots of a regular polynomial 166
ROUNDworksheet function 343
roundoff error 111
ROW worksheet function 71, 107,
256,259,279,347
RSQ worksheet function 289,29 1
rules for naming variables or
arguments 17
Runge-Kutta custom functions 224,
225,229,234
Runge-Kutta method 218,220,222,
223,225,235,237,251,258
running a Sub procedure 8
327
Regression tool 289,304
296,299
S
Sampling tool 348
sampling, random 345,347
scalar multiplication 59INDEX 45 1
scale factor, in Solver model 326
scaling arrays 64
scoping a subroutine 30
secant method 160
second derivative 99, 102, 103, 106
of a worksheet formula 121
second-order differential equation
245,258,263,282
Select Case statement 25
series
alternating 69
convergent 69
formulas, evaluating 70
power 69
sumofa 69
Set Cell box (Goal Seek) 158, 181
Set keyword 32,43,47
Set Target Cell box 320
Sheets 32
shooting method 245
shortcut key, add 15
shortcut keys for VBA 15,387
shortcut to enter a function 13
Show Iteration Results (Solver
simply supported beam 246
Simpson's 1/3 rule 128
Simpson's 3/8 rule 129
Simpson's method 127, 128, 133,
simulating a normal random
simultaneous differential equations,
simultaneous equations 65
SimultEqNL custom function 208
size of an array 44
slope 99
ofacurve 155
least-squares 288
assigning 9
options) 325
134, 136
distribution 349
systems of 229
SLOPE worksheet function 289, 291
slow convergence 153
SMALL worksheet function 346
Solver Add-In 3 17
Solver Estimates, Derivatives and
Search 326
Solver Options 321,324
Solver Parameters dialog box 320,
Solver perturbation factor 3 16
Solver Results dialog box 321,322
Solver Statistics macro 115, 328
Solver
323
Add, Change, Delete 324
Assume Linear Model 325
Assume Non-Negative 325
By Changing Cell 323
changing cells 316, 326
constraints 324
Convergence 325
discontinuous functions 323
external references 323
global minimum 324
Guess 324
initial estimates for 323
manual scaling 326
Max Time and Iterations 324
Maximum Change 325
objective 316,326
Precision and Tolerance 324
Reset All 324
Save Model... and Load Model...
scale factor 326
Show Iteration Results 325
target cell 3 16
Unable to Find a Solution 323
Use Automatic Scaling 325
use of HLOOKUPin models 323
use of VLOOKUP in models 323
326
Sort... 345
square matrix 57452 EXCEL:NUMERICALMETHODS
standard deviation of the regression
standard error of the y estimate 296,
statements, VBA 16
statisticsof nonlinear regression 327
StatusBar 325
steepest descent, method of 3 16
stencil 266, 271, 283
Step mode 55,58
steppingthrough code 55
Stop keyword 55,56
Stop Recording toolbar 6,7
straight line, least-squares fit to a
String keyword 29
structure of a Functionprocedure 5
of a Sub procedure 5
Sub procedure, running 8
Sub procedure, structure of 5
subroutines 30 ,
SUBSTITUTEworksheet function
116, 134,225
subtraction, matrix 58
sum of a series 69
sum of the squares of deviations 288
surface, error 3 15
symbolic integration 127,218
symmetric matrix 58
syntax of the InputBoxmethod 42
systems
of differential equations 229
of first-order differential equations
of linear equations 190
of nonlinear equations 207
of simultaneous differential
parameter 327
298
288,289
228
equations 229
T
table, obtaining values from a 77
target cell (Solver) 3 16
Taylor series 73, 103,208
temperature distribution 267
tenth-order Legendre polynomial 138
testing 54
text functions, VBA 22
thermal diffusion equation 264
To Value box (Goal Seek) 158, 181
Toggle Breakpoint 55
traffic model (problem) 362
trajectory (problem) 24 1
transpose of a matrix 62
TRANSPOSE worksheet function 49
transposition, matrix 60
trapezoid method for integration 127,
traveling salesman (problem) 362
TREND worksheet function 85, 86,
Trendline 289,298
fitting functions 302
limitations of 301
linear regression using 298
Trendline.. .Tocell utility 305
trial-and-error 3 14
tridiagonal matrix 58
trigonometric functions 422
two kinds of macros 4
two ways to specify arguments of
two-way table 81
134
90
methods 34
cubic interpolation in 91,94
linear interpolation in
U
UBoundfunction 44,5 1
Unable to find a solution (Solver)
Unionmethod 37
unit matrix 58, 196
Use Automatic Scaling (Solver
options) 325
user-defined functions 4
323INDEX ~ 45-3
using the Recorder 6
D
Value property 38, 116
vapor diffusion in a tube (example)
variable type of an array 44
variables 17
examining the values of 56
naming 17
275,277,279,282
Variant data type 29
VBA Add Watch dialog box 58
VBA data types 28,29
VBA information functions 23
VBA keywords 365
Address 116
And 17,25
As 50
Boolean 29
Call 30
Cells 36, 37
Chartwizard 35
CheckSpelling 35
Convertformula 117, 118
CVErr 50
Dim 43,44
Double 29
Evaluate 116, 117, 134,225
Exit 28
Formula 116, 133,225
If...Then 25
If...Then...Elself 25
If...Then...Else 25
InputBox 41
Integer 29
Intersect 37
LBound 44
MsgBox 39
Not 17,25
NumberFormat 19
On Error GoTo 119
Option Base 1 44,47,5 1
Option Explicit 10
Optional 50
Or 17,25
ParamArray 5 1,52,65
Preserve 45
Private 31
Public 31
Range 37
ReDim 45
Select Case 25
Set 32,43,47
Sheets 32
Stop 55,56
String 29
UBound 44,51
Union 37
Value 38, 116
text functions 22
VBA mathematical functions 21
VBA, shortcut keys for 387
VBA Watches Pane 58
vector 57
vibration of a string 282,283
Visual Basic arrays 43
Visual Basic Editor 1,2, 7
Visual Basic procedures 4
Visual Basic statements 16
VLOOKUP worksheet function 77,
VLOOKUP, use of, in Solver models
79, 80, 81
323
W
wave equation 264
weights, in Gaussian quadrature 137
window, properties 1, 5
work-around for the row-column
worksheet formula, derivatives of a
problem 49
110,111,112
first derivative of a 111, 115454 EXCELNJMERICAL METHODS
second derivative of 121
ABS 256,279
COLUMN 256,259,278,279
HLOOKUP 77
IF 278,279
INDEX 80,81,84, 106,346,349
INDIRECT 72, 107,279,256,259
INTERCEPT 289,291
ISERROR 360
LINEST 65, 105,292,293,294,296,
LOOKUP 79
MATCH 80,84, 106, 107,346
MAX 80,279
MDETERM 60,63, 190, 191
MIDENT 63
MIN 279
Mlndex 64
MINVERSE 60, 191,257,261,276
MMULT 62, 191,257,261,276
MSCALE 64
NORMINV 349
OFFSET 106
RAND 342,343,345
RANDBETWEEN 343
ROUND 343
ROW 71, 107,256,259,279,347
RSQ 289,291
SLOPE 289,291
SMALL 346
SUBSTITUTE 116,134,225
TRANSPOSE 49
TREND 85,86,90
VLOOKUP 77,79,80,81
worksheet functions:
297
worksheet functions with VBA 23
كلمة سر فك الضغط : books-world.net
The Unzip Password : books-world.net
أتمنى أن تستفيدوا من محتوى الموضوع وأن ينال إعجابكم
رابط من موقع عالم الكتب لتنزيل كتاب Excel for Scientists and Engineers Numerical Methods
رابط مباشر لتنزيل كتاب Excel for Scientists and Engineers Numerical Methods 
|
|
