Department of Mechanical Engineering

JSS Academy of Technical Education

Bengaluru-560060

Lab Manual

Spread Sheets for

Engineers
21ME481

IV Semester Mechanical Engineering-BE

 PROGRAM OUTCOMES

1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering

fundamentals, and an engineering specialization to the solution of complex engineering
problems.

2. Problem analysis: Identify, formulate, research literature, and analyze complex engineering
problems reaching substantiated conclusions using first principles of mathematics, natural
sciences, and engineering sciences.

3. Design/development of solutions: Design solutions for complex engineering problems and

design system components or processes that meet the specified needs with appropriate
consideration for the public health and safety, and the cultural, societal, and environmental
considerations.

4. Conduct investigations of complex problems: Use research-based knowledge and research

methods including design of experiments, analysis and interpretation of data, and synthesis
of the information to provide valid conclusions.

5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern
engineering and IT tools including prediction and modelling to complex engineering
activities with an understanding of the limitations.

6. The engineer and society: Apply reasoning informed by the contextual knowledge to assess
societal, health, safety, legal and cultural issues and the consequent responsibilities relevant
to the professional engineering practice.

7. Environment and sustainability: Understand the impact of the professional engineering

solutions in societal and environmental contexts, and demonstrate the knowledge of, and
need for sustainable development.

8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practice.

9. Individual and team work: Function effectively as an individual, and as a member or leader
in diverse teams, and in multidisciplinary settings.

10. Communication: Communicate effectively on complex engineering activities with the

engineering community and with society at large, such as, being able to comprehend and
write effective reports and design documentation, make effective presentations, and give
and receive clear instructions.

11. Project management and finance: Demonstrate knowledge and understanding of the
engineering and management principles and apply these to one’s own work, as a member
and leader in a team, to manage projects and in multidisciplinary environments.

12. Life-long learning: Recognize the need for, and have the preparation and ability to engage
in independent and life-long learning in the broadest context of technological change.
 JSS MAHAVIDYAPEETHA
JSS ACADEMY OF TECHNICAL EDUCATION, BENGALURU
Affiliated to Visvesvaraya Technological University, Belagavi, Karnataka, INDIA
Approved by All India Council for Technical Education, New Delhi
UG programs accredited by NBA: ECE, CSE, ISE, CIVIL, E & IE and MECHANICAL

Department of Mechanical Engineering

Academic Term: 2022-23 (Even)

Course: Spread Sheet for Engineers Course Code: 21MT481 Sem/Sec: 4th
Name of the Faculty: Dr. BV Raghavendra+ Dr. J S Srikantamurthy

At the end of this course, students will be able to:

CO Course Outcome BLL

Select different plots and charts and apply different functions, conditional functions,
CO1 L3
regression analysis and matrix operations.
Develop iterative solutions for roots, multiple roots, optimization and non-linear
CO2 L3
regression analysis
Construct Visual Basic Analysis (VBA), User Defined Functions (UDF), VBA
CO3 L3
subroutines and Macros
CO4 Explain numerical integration and solving differential equations using different methods L2

Cos and POs Mapping

CO-PO PO1 PO2 PO3 PO4 PO5 PO6 PO7 P08 PO9 PO10 PO11 PO12

CO1 3 - - - - - - - - - - 2

CO2 3 - - - 3 - - - - - - 2
CO3 3 - - - 3 - - - - - - 2
CO4 - - - - 3 - - - - - - 2
Average 3 - - - 3 - - - - - - 2
 38

Semester 04 Ability Enhancement Course IV

SPREAD SHEETS FOR ENGINEERS

Course Code 21MT481 CIE Marks 50
Teaching Hours/Week (L:T:P: S) 0:0:2:0 SEE Marks 50
Credits 1 Exam Hours 01
Course objectives:
 To create different plots and charts
 To compute different functions, conditional functions and make regression analysis
 To carryout iterative solutions for roots, multiple roots, optimization and non-linear regression analysis
 To carryout matrix operations
 To Understand VBA and UDF
 To understand VBA subroutines and Macros
 To carryout numerical integration and solving differential equations using different methods
Sl.NO Experiments
1 Charting: Create an XY scatter graph, XY chart with two Y-Axes, add error bars to your plot, create a
combination chart
2 Functions: Computing Sum, Average, Count, Max and Min, Computing Weighted Average, Trigonometric
Functions, Exponential Functions, Using The CONVERT Function to Convert Units
3 Conditional Functions: Logical Expressions, Boolean Functions, IF Function, Creating a Quadratic Equation
Solver, Table VLOOKUP Function, AND, OR and XOR functions.
4 Regression Analysis: Trendline, Slope and Intercept, Interpolation and Forecast, The LINEST Function,
Multilinear Regression, Polynomial Fit Functions, Residuals Plot, Slope and Tangent, Analysis ToolPack.
5 Iterative Solutions Using Excel: Using Goal Seek in Excel, Using The Solver To Find Roots, Finding Multiple
Roots, Optimization Using The Solver, Minimization Analysis, NonLinear Regression Analysis.
6 Matrix Operations Using Excel: Adding Two Matrices, Multiplying a Matrix by a Scalar, Multiplying Two
Matrices, Transposing a Matrix, Inverting a Matrix and Solving System of Linear Equations.
7 VBA User-Defined Functions (UDF): The Visual Basic Editor (VBE), The IF Structure, The Select Case Structure,
The For Next Structure, The Do Loop Structure, Declaring Variables and Data Types, An Array Function The Excel
Object Model, For Each Next Structure.
8 VBA Subroutines or Macros: Recording a Macro, Coding a Macro Finding Roots by Bisection, Using Arrays,
Adding a Control and Creating User Forms.
Demonstration Exercises
9
Numerical Integration Using Excel: The Rectangle Rule, The Trapezoid Rule, The Simpson's Rule, Creating a
User-Defined Function Using the Simpson's Rule.
10
.

11
Differential Equations: Euler's Method, Modified Euler's Method, The Runge Kutta Method, Solving a Second
12 Order Differential Equation

Course outcomes (Course Skill Set):

At the end of the course the student will be able to:
 To create different plots and charts
 To compute different functions, conditional functions and make regression analysis
 To carryout iterative solutions for roots, multiple roots, optimization and non-linear regression analysis
 To carryout matrix operations
 To Understand VBA and UDF
 To understand VBA subroutines and Macros
 To carryout numerical integration and solving differential equations using different methods

38
 39

Assessment Details (both CIE and SEE)

The weightage of Continuous Internal Evaluation (CIE) is 50% and for Semester End Exam (SEE) is 50%. The minimum
passing mark for the CIE is 40% of the maximum marks (20 marks). A student shall be deemed to have satisfied the
academic requirements and earned the credits allotted to each course. The student has to secure not less than 35%
(18 Marks out of 50) in the semester-end examination (SEE).
Continuous Internal Evaluation (CIE):
CIE marks for the practical course is 50 Marks.
The split-up of CIE marks for record/ journal and test are in the ratio 60:40.
 Each experiment to be evaluated for conduction with observation sheet and record write-up. Rubrics for the
evaluation of the journal/write-up for hardware/software experiments designed by the faculty who is handling the
laboratory session and is made known to students at the beginning of the practical session.
 Record should contain all the specified experiments in the syllabus and each experiment write-up will be
evaluated for 10 marks.
 Total marks scored by the students are scaled downed to 30 marks (60% of maximum marks).
 Weightage to be given for neatness and submission of record/write-up on time.
 Department shall conduct 02 tests for 100 marks, the first test shall be conducted after the 8th week of the
semester and the second test shall be conducted after the 14th week of the semester.
 In each test, test write-up, conduction of experiment, acceptable result, and procedural knowledge will carry a
weightage of 60% and the rest 40% for viva-voce.
 The suitable rubrics can be designed to evaluate each student’s performance and learning ability. Rubrics
suggested in Annexure-II of Regulation book
 The average of 02 tests is scaled down to 20 marks (40% of the maximum marks).
The Sum of scaled-down marks scored in the report write-up/journal and average marks of two tests is the total CIE
marks scored by the student.

Semester End Evaluation (SEE):

SEE marks for the practical course is 50 Marks.
SEE shall be conducted jointly by the two examiners of the same institute, examiners are appointed by the University
All laboratory experiments are to be included for practical examination.(Rubrics) Breakup of marks and the instructions
printed on the cover page of the answer script to be strictly adheredto by the examiners. OR based on the course
requirement evaluation rubrics shall be decided jointly by examiners. Students can pick one question (experiment)
from the questions lot prepared by the internal /external examiners jointly. Evaluation of test write-up/ conduction
procedure and result/viva will be conducted jointly by examiners.
General rubrics suggested for SEE are mentioned here, writeup-20%, Conduction procedure and result in -60%, Viva-
voce 20% of maximum marks. SEE for practical shall be evaluated for 100 marks and scored marks shall be scaled
down to 50 marks (however, based on course type, rubrics shall be decided by the examiners)
Change of experiment is allowed only once and 15% Marks allotted to the procedure part to be made zero.
The duration of SEE is 03 hours
Rubrics suggested in Annexure-II of Regulation book

Suggested Learning Resources:

McFedries PaulMicrosoft Excel 2019 Formulas And Functions Microsoft Press, U.S, 2019 Edition

39
 Spread Sheets for Engineers

SPREAD SHEET FOR ENGINEERS PROGRAMMING LAB MANUAL

1. Charting: Create an XY scatter graph, XY chart with two Y-Axes, add error bars to your plot,
and create a combination chart

Student Subject-1 Subject-2

Scatter Graph:
Select the table Rama 84 68
>Insert>Scatter>Select type of graph Krishna 56 82
>Layout>Axis Title, Axis Horizontal, Axis Vertical Eshwara 63 56
>Format Axis, Change Maximum and Minimum Geetha 44 66
Pooja 72 48
Two Y Axes Graph:
Select the table Vasanth 59 64
>Insert>line>Select type of graph
>Layout>Axis Title, Axis Horizontal, Axis Vertical Subject-1 Subject-2
>Format Axis, Change Maximum and Minimum Average 63.00 64.00
Maximum 84 82
Error Graph:
Select Average data Minimum 44 48
>Insert>Column>Select type of graph
>Layout>Axis Title, Axis Horizontal, Axis Vertical Max. difference 21.00 18.00
>Format Axis, Change Maximum and Minimum Min. difference 19.00 16.00

Std. Deviation 13.77 11.52

>Layout>Error bars>More error bars options> Custom>Specify Value>Positive Error value (Select both)>
Negative Error value (Select both)>Enter

100 100
90
Scatter Graph 90
Two Y Graph
Subject-1
80 80
Subject-1 Subject-2
Marks

70
Marks

70
60
Subject-2
60
50
40 50
30 40
0 5 10
Students
Student Name

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 1

 Spread Sheets for Engineers

Error Graph
100.00 Average
80.00
60.00
40.00
20.00
0.00
Subject-1 Subject-2

Combination Graph:
Student Subject-1 Subject-2 Percentage
https://www.youtube.com/watch?v=RWwpkwvXRN4 Rama 84 68 76
Select below table data
>Insert>Column>Select type of graph Krishna 56 82 69
>Layout>Axis Title, Axis Horizontal, Axis Vertical Eshwara 63 56 59.5
Primary,
Axis Vertical Secondary Geetha 44 66 55
>Format Axis, Change Maximum and Minimum Pooja 72 48 60
>Layout>Chart Area>Percentage
Vasanth 59 64 61.5
> Format Selection>Secondary Axis

100 100
Combination Graph 95
90
Subject-1 90
80 Subject-2 85
Percentage

70 Percentage 80
Marks

75
60 70
50 65
60
40
55
30 50
Rama Krishna Eshwara Geetha Pooja Vasanth
Students

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 2

 Spread Sheets for Engineers

2. Functions: Computing Sum, Average, Count, Max and Min, Computing Weighted Average,
Trigonometric Functions, Exponential Functions, Using the CONVERT Function to Convert
Units.

Functions for: Sl. No. Name Marks

1 Abhiskek 80
Sum=SUM (range) 2 Ajay 64
Average=AVERAGE (range) 3 Krishna 78
Count=COUNT (range)
4 Ravindra 55
Max.=MAX(range)
Min=MIN (range) 5 Anupama 58
6 Aravind 72
7 Yuvaraj 54
8 Nachiketh 65

Sum 526 =SUM(range)

Average 65.75 =AVERAGE(range)
Count 8 =COUNT(range)
Max 80 =MAX(range)
Min 54 =MIN(range)

Computing Weighted Average:

Sl. No. Product Qty Weight
Function: Weighted Average=SUMPRODUCT (range, range) 1 Apple 30 0.4
2 Orange 42 0.2
3 Banana 23 0.1
4 Grapes 22 0.3
Weighted Avg. 29.3

Trigonometric Functions:
Radian=Cell*PI()/180
Sin=SIN(Cell Num)
Cos=COS(Cell Num)
Tan=TAN(Cell Num)
Cot=TANH(Cell Num)
Sec=COSH(Cell Num)
Co-Sec=SINH(Cell Num)

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 3

 Spread Sheets for Engineers

Angle in Angle in
Sin Cos Tan Cot Sec CoSec
Degree Radian
=Cell*PI()/18 =SIN(Cell =COS(Cell =TAN(Cell =TANH(Cell =COSH(Cell =SINH(Cell
0 Num) Num) Num) Num) Num) Num)
0 0 0 1 0 0 1 1
45 0.785398 0.707107 0.707107 1 0.6557942 1.324609 1.324609
90 1.570796 1 6.13E-17 1.63E+16 0.9171523 2.509178 2.509178
180 3.141593 1.23E-16 -1 -1.2E-16 0.9962721 11.59195 11.59195
225 3.926991 -0.70711 -0.707107 1 0.9992239 25.38686 25.38686
270 4.712389 -1 -1.84E-16 5.44E+15 0.9998386 55.66338 55.66338
315 5.497787 -0.70711 0.707107 -1 0.9999664 122.0776 122.0776
360 6.283185 -2.5E-16 1 -2.5E-16 0.999993 267.7468 267.7468

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 4

 Spread Sheets for Engineers

Exponential Functions:

Function=EXP (Value)
e^2 7.389056

Using the CONVERT Function to Convert Units:

Function=COVERNT (Value, “from unit”, “to unit”)

Convert Function
mtr inch
1 39.37008
2 78.74016
Sec Hour
60 1

Table Abbreviations:

Weight and mass From_unit or to_unit Distance From_unit or to_unit

Gram "g" Meter "m"
Slug "sg" Statute mile "mi"
Pound mass "lbm" Nautical mile "Nmi"
(avoirdupois)
Inch "in"
U (atomic mass unit) "u"
Foot "ft"
Ounce mass "ozm"
(avoirdupois) Yard "yd"
Angstrom "ang"
Pica (1/72 in.) "Pica"

From_unit or From_unit From_unit or

Temperature to_unit Time or to_unit Pressure to_unit
Degree Celsius "C" (or "cel") Year "yr" Pascal "Pa" (or "p")
Degree "F" (or "fah") Day "day" Atmosphere "atm" (or "at")
Fahrenheit Hour "hr" mm of "mmHg"
Kelvin "K" (or "kel") Mercury
Minute "mn"
Second "sec"

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 5

 Spread Sheets for Engineers

Liquid From_unit or From_unit or From_unit or

measure to_unit Energy to_unit Force to_unit
Teaspoon "tsp" Joule "J" Newton "N"
Tablespoon "tbs" Erg "e" Dyne "dyn" (or "dy")
Fluid ounce "oz" Thermodynamic "c" Pound force "lbf"
calorie
Cup "cup" IT calorie "cal" Horsepower "HP" (or "h")
U.S. pint "pt" (or "us_pt") Electron volt "eV" (or "ev") Watt "W" (or "w")
U.K. pint "uk_pt" Horsepower- "HPh" (or "hh") Tesla "T"
hour
Quart "qt" Watt-hour "Wh" (or "wh") Gauss "ga"
Gallon "gal" Foot-pound "flb"
Magnetism
Liter "l" (or "lt") BTU "BTU" (or "btu")

The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit:

Prefix Multiplier Abbreviation

exa 1.00E+18 "E"
peta 1.00E+15 "P"
tera 1.00E+12 "T"
giga 1.00E+09 "G"
mega 1.00E+06 "M"
kilo 1.00E+03 "k"
hecto 1.00E+02 "h"
dekao 1.00E+01 "e"
deci 1.00E-01 "d"
centi 1.00E-02 "c"
milli 1.00E-03 "m"
micro 1.00E-06 "u"
nano 1.00E-09 "n"
pico 1.00E-12 "p"
femto 1.00E-15 "f"
atto 1.00E-18 "a"

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 6

 Spread Sheets for Engineers

3. Conditional Function: Logical Expressions, Boolean Functions, IF Function, Crating a Quadratic

Equation Solver, Table VLOOKUP Function, AND, OR and XOR functions.

Logical Expressions:

Logical functions in Excel: AND, OR, XOR and NOT

Function Description Formula Example Formula Description

Returns TRUE if all The formula returns TRUE if a value in cell
=AND(A2>=10,
AND of the arguments A2 is greater than or equal to 10, and a value
B2<5)
evaluate to TRUE. in B2 is less than 5, FALSE otherwise.

The formula returns TRUE if A2 is greater

Returns TRUE if any
than or equal to 10 or B2 is less than 5, or both
OR argument evaluates to =OR(A2>=10, B2<5)
conditions are met. If neither of the conditions
TRUE.
it met, the formula returns FALSE.
The formula returns TRUE if either A2 is
Returns a logical greater than or equal to 10 or B2 is less than 5.
=XOR(A2>=10,
XOR Exclusive Or of all If neither of the conditions is met or both
B2<5)
arguments. conditions are met, the formula returns
FALSE.
Returns the reversed
logical value of its
argument. I.e. If the The formula returns FALSE if a value in cell
NOT argument is FALSE, =NOT(A2>=10) A1 is greater than or equal to 10; TRUE
then TRUE is otherwise.
returned and vice
versa.

One of the most common uses of the Excel AND function is found in the logical test argument of the IF
function to test several conditions instead of just one. For example, can nest any of the AND functions above
inside the IF function and get a result.

=IF(AND(A2="Bananas", B2>C2), "Good", "Bad")

Crating a Quadratic Equation Solver:

Y= aX^2+bX+c
Y= 2X^2-4X+4
a 2
b -4
c 1
Function= X=-b+/- SQRT(b^2-4ac)/2a
X1 1.707107 X1=-b+SQRT(b^2-4ac)/2a
X2 0.292893 X2=-b- SQRT(b^2-4ac)/2a

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 7

 Spread Sheets for Engineers

VLOOKUP Function:

=VLOOKUP(M34,L28:M32,2,FALSE)

=VLOOKUP(Value in the cell to be looked, range, column no to look value, False)

Create the table

Use function VLOOKUP and enter the cell numbers in the arguments

1 Harian 20
2 Karn 40
3 Tamil 10
4 Andra 70
5 Delhi 50

Name Andra
Prince 70

4. Regression Analysis: Trend line, Slope and Intercept, Interpolation and Forecast, The LINEST
Function, Multilinear Regression, Polynomial Fit Functions, Residuals Plot, Slope and Tangent,
Analysis Tool Pack.

Regression Analysis: Trend line, Slope and Intercept, Interpolation and Forecast, The LINEST Function

X Y
1 9
2 13
3 17
4 21
5 25

Using functions:
Slope 4 =SLOPE(Yrange, X range)
Intercept 5 =INTERCEPT(Y range, X range)
Trend X=0 5 =TREND(Y range, X range, New X,Const)
Trend X=10 45

Linset Function=Linset(Y range, X range, ,TRUE)

Slope 4 5 Y Intercept
Uncertainty Slope 3.24317E-16 1.07564E-15 Uncertainty Y Intercept
R^2 1 1.02558E-15 se(y)
F 1.52118E+32 3 df
SS reg 160 3.15544E-30 ss resid

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 8

 Spread Sheets for Engineers

Ctr+Shift+Enter

30 y = 4x + 5
25
20
15
10 Series1

5
0
1 2 3 4 5

Multilinear Regression, Polynomial Fit Functions, Residuals Plot, Slope and Tangent, Analysis Tool Pack

Regression:
Data>Data Analytics>Descriptive Statistics>Input cell>output cell>Summary statistics

Column1

Mean 17
Standard Error 2.828427
Median 17
Mode #N/A
Standard Deviation 6.324555
Sample Variance 40
Kurtosis -1.2
Skewness 0
Range 16
Minimum 9
Maximum 25
Sum 85
Count 5

Multilinker Regression:

SUMMARY OUTPUT
Regression Statistics
Multiple R 1
R Square 1
Adjusted R
Square 1
Standard Error 1.03E-15
Observations 5

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 9

 Spread Sheets for Engineers

ANOVA
Significance
df SS MS F F
Regression 1 160 160 1.52E+32 1.1754E-48
Residual 3 3.16E-30 1.05E-30
Total 4 160

Standard Upper Lower Upper

Coefficients Error t Stat P-value Lower 95% 95% 95.0% 95.0%
Intercept 5 1.08E-15 4.65E+15 2.2E-47 5 5 5 5
X Variable 1 4 3.24E-16 1.23E+16 1.18E-48 4 4 4 4

RESIDUAL OUTPUT PROBABILITY OUTPUT

Observation Predicted Y Residuals Standard Residuals Percentile Y

1 9 -1.77636E-15 -2 10 9
2 13 0 0 30 13
3 17 0 0 50 17
4 21 0 0 70 21
5 25 0 0 90 25

X Variable 1 Residual Plot X Variable 1 Line Fit Plot

0 30
0 2 4 6 20
Residuals

-1E-15 10 Y
0 Predicted Y
0 2 4 6
-2E-15
X Variable 1 X Variable 1

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 10

 Spread Sheets for Engineers

5. Iterative Solutions Using Excel: Using Goal Seek in Excel, Using the Solver to find roots, Finding
Multiple Roots, Optimization Using the Solver, Minimization Analysis, Nonlinear Regression Analysis.

Using Goal Seek in Excel:

Create a table Goal Seek

>Data>What-IF Analysis>Goal Seek

>Set=Select Cell (Row-Grand Total, Column-Total Amount),
Value=15000,
by changing cell=Select cell (Row-Sl. N0 3, Column- Unit cell)
>Enter

Goal Seek

Total Amount
S.No Product Price Unit (Rs.)
1 Product A 195 30 5850
2 Product B 35 20 700
3 Product C 50 0
Grand Total 50 6550
Target 15000

Total Amount
S.No Product Price Unit (Rs.)
1 Product A 195 30 5850
2 Product B 35 20 700
3 Product C 50 169 8450
Grand Total 219 15000
Target 15000
Using the Solver to find roots:

Function f(X)=2X3+5X2-X-1 f(x)=2X3+5X2-6X-1

Create function in excel using formula in a cell
>Data>Solver
Set target cell=Select cell having formula, X 1
Select value=0, f(x) -1.2E-07
By changing=Cell having value of X
Enter

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 11

 Spread Sheets for Engineers

Finding Multiple Roots:

X 0.279557 X1= -0.866951256
Y 0 X2= -0.412605573
Function= Y=-X3-X2+0.1 X3= 0.279556889
Create function in excel using formula in a cell
>Data>Solver Y 0
Set target cell=Select cell having Diff value, Y=-X3-X2+0.1 5.3E-10
Select Min, Diff 2.81E-19
By changing=Cell having value of X
Enter
Repeat the same for three different values of X which make Y close to Zero

Optimization Using the Solver:

Maximize P=3X1+2X2-X3
Constraints
X1+3X2+X3<=9
2X1+3X2-X3>=2
3X1-2X2+X3>=5
X1>=0, X2>=0, X3>=0

Variables Solution
X1 0 Variables
X2 0 X1 9
X3 0 X2 0
X3 0
P 0
P 27
Constraints Inequalities
1 0 <= 9 Constraints Inequalities
2 0 >= 2 1 9 <= 9
3 0 >= 5 2 18 >= 2
4 0 >= 0 3 27 >= 5
5 0 >= 0 4 9 >= 0
6 0 >= 0 5 0 >= 0
6 0 >= 0

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 12

 Spread Sheets for Engineers

Nonlinear Parameter Estimation

Error= minimize sum(((y_Measured - y_Estimated)/y_Measured)^2)
y = 1.4016ln(x) + 0.9798
Set Constant 0.99 Estimated
y
x Measured y Measured Estimated Error x Plot y Plot
18.34 5.07 5.0628 3.46198E-06 5 3.2408
79.87 7.16 7.1245 2.29155E-05 10 4.2124
85.10 7.26 7.2135 4.60281E-05 15 4.7807
10.52 4.25 4.2836 4.63915E-05 20 5.1839
44.46 6.28 6.3034 1.17679E-05 25 5.4966
69.57 6.91 6.9311 7.77304E-06 30 5.7522
8.96 4.04 4.0584 1.30558E-05 35 5.9682
86.20 7.26 7.2315 1.49307E-05 40 6.1554
66.86 6.90 6.8754 1.08653E-05 45 6.3205
16.87 4.87 4.9457 0.000214083 50 6.4681
52.27 6.52 6.5304 3.63587E-06 55 6.6017
93.92 7.34 7.3517 1.27127E-06 60 6.7237
24.35 5.43 5.4595 2.63304E-05 65 6.8359
5.12 3.38 3.2735 0.001078343 70 6.9397
25.13 5.46 5.5037 5.21647E-05 75 7.0364
34.04 5.90 5.9291 2.36708E-05 80 7.1269
61.44 6.80 6.7570 4.74326E-05 85 7.2119
42.70 6.19 6.2471 7.54548E-05 90 7.2920
39.53 6.07 6.1389 0.000127183 95 7.3678
29.99 5.74 5.7516 6.69533E-06 100 7.4397

Sum= 0.001833455

8.00
y

Nonlinear Regression
7.00

6.00

5.00
y = 1.4016ln(x) + 0.9798
R² = 0.9985
4.00
y Measured
3.00

2.00 x
0.00 20.00 40.00 60.00 80.00 100.00

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 13

 Spread Sheets for Engineers

Nonlinear Parameter Estimation

y = a + b / x + c ln(x)
minimize sum=(((y_Measured - y_Estimated)/y_Measured)^2)
Actual Estimated
a 0.50 1.00
b 2.00 1.00
c 1.50 1.00
Estimated
x Measured y Measured y Estimated x Plot y Plot
18.34 5.07 5
79.87 7.16 10
85.10 7.26 15
10.52 4.25 20
44.46 6.28 25
69.57 6.91 30
8.96 4.04 35
86.20 7.26 40
66.86 6.90 45
16.87 4.87 50
52.27 6.52 55
93.92 7.34 60
24.35 5.43 65
5.12 3.38 70
25.13 5.46 75
34.04 5.90 80
61.44 6.80 85
42.70 6.19 90
39.53 6.07 95
29.99 5.74 100

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 14

 Spread Sheets for Engineers

Solution:
Actual Estimated
a 0.50 0.54
b 2.00 1.98
c 1.50 1.50
Estimated
Normalized
x Measured y Measured y Estimated Error^2 x Plot y Plot
18.34 5.07 5.02 0.00 5.00 3.35
79.87 7.16 7.14 0.00 10.00 4.20
85.10 7.26 7.24 0.00 15.00 4.74
10.52 4.25 4.26 0.00 20.00 5.14
44.46 6.28 6.28 0.00 25.00 5.45
69.57 6.91 6.94 0.00 30.00 5.72
8.96 4.04 4.06 0.00 35.00 5.94
86.20 7.26 7.26 0.00 40.00 6.13
66.86 6.90 6.88 0.00 45.00 6.30
16.87 4.87 4.90 0.00 50.00 6.46
52.27 6.52 6.52 0.00 55.00 6.60
93.92 7.34 7.38 0.00 60.00 6.72
24.35 5.43 5.42 0.00 65.00 6.84
5.12 3.38 3.38 0.00 70.00 6.95
25.13 5.46 5.46 0.00 75.00 7.05
34.04 5.90 5.90 0.00 80.00 7.15
61.44 6.80 6.76 0.00 85.00 7.24
42.70 6.19 6.23 0.00 90.00 7.32
39.53 6.07 6.11 0.00 95.00 7.40
29.99 5.74 5.71 0.00 100.00 7.48

Sum 0.000375851
8.00
y

Nonlinear Regression in Excel

y = a + b/x + c ln(x)
6.00
y Measured
y Estimated
4.00

2.00 x
0.00 20.00 40.00 60.00 80.00 100.00
Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 15
 Spread Sheets for Engineers

6. Matrix Operations Using Excel: Adding Two Matrices, Multiplying a Matrix by a Scalar, Multiplying
Two Matrices, Transposing a Matrix, Inverting a Matrix and Solving System of Linear Equations

Adding Two Matrices: Matrix A+B

Matrix A Matrix B
2 3 1 5 3 6 7 6 7
>Create matrix A and B
Sum of Matrix A+B= 3 4 2 6 4 2 9 8 4
Select Cells equal to size of Matrix in a new cell 4 7 5 3 4 8 7 11 13
>= Select matrix A+ Select matrix B
>Ctrl+Shift and Enter

Multiplying a Matrix by a Scalar:

Matrix A Matrix 3*A
>Create matrix A 2 3 1 6 9 3
Matrix 3*A=
3 4 2 9 12 6
Select Cells equal to size of Matrix in a new cell
>= 3*Select matrix A 4 7 5 12 21 15
>Ctrl+Shift and Enter

Matrix A*B
Multiplying Two Matrices: Matrix A Matrix B
2 3 1 5 3 6 31 22 26
>Create matrix A and B 3 4 2 6 4 2 45 33 42
Matrix AXB= 4 7 5 3 4 8 77 60 78
Select Cells equal to size of Matrix in a new cell
>=MMULT(Select matrix A, Select matrix B)
>Ctrl+Shift and Enter

Transposing a Matrix:

>Create matrix A Matrix A Transpose of A

Transpose Matrix A= 2 3 1 2 3 4
Select Cells equal to size of Matrix in a new cell 3 4 2 3 4 7
>=TRANSPOSE(Select matrix A) 4 7 5 1 2 5
>Ctrl+Shift and Enter

Inverse of a Matrix:
Matrix A Inverse of A
>Create matrix A 2 3 1 -2 2 -0.5
Inverse of Matrix A= 3 4 2 1.8 -2 0.25
Select Cells equal to size of Matrix in a new cell
>=MINVERSE(Select matrix A) 4 7 5 -1 0.5 0.25
>Ctrl+Shift and Enter

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 16

 Spread Sheets for Engineers

Solving System of Linear Equations:

Equation in Matrix form
3X+4Y+2Z=5 Matrix A Variables Constraint
4X+7Y+3Z=15 3 4 2 X = 5
5X+3Y+4Z=3
4 7 3 Y = 15
>Create matrix A 5 3 4 Z = 3
Inverse of Matrix A=
Select Cells equal to size of Matrix in a new cell Value of
>=MINVERSE(Select matrix A) Inverse of A Variables
>Ctrl+Shift and Enter 3 -1.4 -0 X = -8.71
Values of Variables= -0 0.3 -0 Y = 3.14
>=MMULT(Select Inverse of A, Select matrix B)
-3 1.6 0.7 Z = 9.29
>Ctrl+Shift and Enter

7. VBA User-Defined Functions (UDF): The Visual Basic Editor (VBE), The IF Structure, The Select Case
Structure, The For Next Structure, The Do Loop Structure, Declaring Variables and Data Types, An Array Function The
Excel Object Model, For Each Next Structure.
https://youtu.be/BTtqrXYliMs
https://www.youtube.com/watch?v=NpvvwrdDcQk
https://www.techonthenet.com/excel/formulas/if_then.php
https://www.youtube.com/watch?v=I2auhdHDyXk (Object Tutorials)

If-Else Statement:

Private Sub Run_If_Structure_Click()

Dim LRegion As String
Dim LRegionName As String

LRegion = Range("A2").Value

If LRegion = "N" Then

LRegionName = "North"

ElseIf LRegion = "S" Then

LRegionName = "South"

ElseIf LRegion = "E" Then

LRegionName = "East"

ElseIf LRegion = "W" Then

LRegionName = "West"

Else
LRegionName = "Not Assigned"
End If

Range("B2").Value = LRegionName
End Sub

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 17

 Spread Sheets for Engineers

Select Case Statement:

Private Sub Select_Case_Click()
Dim UserInput As Integer
UserInput = InputBox("Please enter a number between 1 and 5")
Select Case UserInput
Case 1
MsgBox "You entered 1"

Case 2
MsgBox "You entered 2"

Case 3
MsgBox "You entered 3"

Case 4
MsgBox "You entered 4"

Case 5
MsgBox "You entered 5"

End Select
End Sub
For Next Statement:
Private Sub Forloop_Click()
Dim x As Integer
Dim i As Integer
x = InputBox("Enter Input Number to multiple by 1 to 5")
For i = 1 To 5
Cells(15 + i, 3) = i
Cells(15 + i, 4) = x
Cells(15 + i, 5) = x * i
Next
End Sub
Do Loop Statement:

Private Sub Do_While_2_Click()

counter = 0
myNum = 9
Do
myNum = myNum - 1
counter = counter + 1
Cells(55 + counter, 12) = myNum
Loop While myNum > 10
MsgBox "The loop made " & counter & " repetitions."
End Sub

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 18

 Spread Sheets for Engineers

_______________________________________

Private Sub Do_While_Click()

counter = 0
myNum = 20
Do While myNum > 10
myNum = myNum - 1
counter = counter + 1
Cells(42 + counter, 10) = myNum
Loop
MsgBox "The loop made " & counter & " repetitions."
End Sub

8. VBA Subroutines or Macros: recording a Macro, Coding a Macro Finding Roots by Bisection, Using
Arrays, Adding a Control and Crating User Forms.

Bisection Method in VBA User Form on Vimeo

https://atozmath.com/example/CONM/Bisection.aspx?q=bi&q1=1 Understating Bisection method

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 19

 Spread Sheets for Engineers

Option Explicit
__________________________________________________

Function f(h)
Dim r As Double, V As Double, Pi As Double
r = Radius '3.048 'Range("Radius")
V = Volume '1.89 ' Range("Volume")
Pi = 4 * Atn(1)
f = h ^ 3 - 3 * r * h ^ 2 + 3 * V / Pi

End Function

________________________________________

Private Sub Calclulate_Click()

Dim n As Integer, i As Integer, xmid As Double

Dim x1 As Double, x2 As Double
x1 = 0
x2 = 2 * Radius
n = 20
For i = 1 To n
xmid = (x1 + x2) / 2
If f(x1) * f(xmid) > 0 Then
x1 = xmid
Else
x2 = xmid
End If

Cells(16 + i, 10) = i
Cells(16 + i, 11) = x1
Cells(16 + i, 12) = x2
Cells(16 + i, 13) = xmid

Next i
'Bisect1 = xmid
Depth = FormatNumber(xmid, 2)

End Sub
____________________________________________________
Private Sub Quit_Click()
UserForm_Bisect.Hide
End Sub
______________________________________________________
Private Sub Reset_Click()
Unload UserForm_Bisect
UserForm_Bisect.Show

End Sub

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 20

 Spread Sheets for Engineers

1 0 5 5
2 0 2.5 2.5
3 0 1.25 1.25
4 0.625 1.25 0.625
5 0.625 0.9375 0.9375
6 0.625 0.78125 0.78125
7 0.703125 0.78125 0.703125
8 0.742188 0.78125 0.742188
9 0.761719 0.78125 0.761719
10 0.771484 0.78125 0.771484
11 0.776367 0.78125 0.776367
12 0.776367 0.778809 0.778809
13 0.776367 0.777588 0.777588
14 0.776978 0.777588 0.776978
15 0.777283 0.777588 0.777283
16 0.777283 0.777435 0.777435
17 0.777283 0.777359 0.777359
18 0.777321 0.777359 0.777321
19 0.77734 0.777359 0.77734
20 0.777349 0.777359 0.777349

Demo 9 and 10

https://www.youtube.com/watch?v=DatyXtowK70
https://www.mclennan.edu/scholar-
day/docs/spring2021/John%20Patterson%20Simpsons%20Rule%20with%20Excel.pdf Simson Rule

Department of Mechanical Engineering-JSS ATE, Bengaluru Page No. 21