Using Excel in Engineering

Contents

Introduction .......................................................................................................................... 3
Spreadsheet Basics ........................................................................................................ 3
Inserting Text, Values and Formulae .............................................................................. 4
The Syntax of Formulae .................................................................................................... 4
The Precedence of Operators .......................................................................................... 5
Trig Functions and Radians .............................................................................................. 5
Logarithmic Functions........................................................................................................ 6
Annotation, Titles, Formatting Etc.................................................................................... 9
Copying Cell Contents ................................................................................................. 11
Copying Using Relative References.............................................................................. 11
Copying Using Absolute Cell References .................................................................... 15
Copying by Dragging ....................................................................................................... 16
Examples for You to Try ............................................................................................. 17
Simple Circuit .................................................................................................................... 17
Bending Stress.................................................................................................................. 17
Projectile fired from a gun ............................................................................................... 18
Drawing Graphs Using Excel .................................................................................... 21
Looking Up Values Using Excel .............................................................................. 23
An Example for you to try ................................................................................................ 29
Conditions in Excel ........................................................................................................ 30
What if we need to consider more than one condition? ............................................. 31
More Clever Functions................................................................................................. 32
Sum the total of a series of values ................................................................................ 32
Find the average of a series of values .......................................................................... 32
Count the number of occurrences of a particular value or range of values ............ 33
Example for you to do, taken from past examination paper ...................................... 34
Using Excel in design - Goal Seek ......................................................................... 35

2
Introduction

The computer based spreadsheet is a flexible calculation tool that has revolutionised
the way in which much engineering analysis can be done. Prior to the spreadsheet,
calculations were all either laboriously carried out using calculators, or specialist
computer programs had to be written to do the work using languages such as C++ or
Fortran. Spreadsheets greatly simplify the work of programming, and are most
suitable for non IT specialists to use.

They have a wide range of applications, being able to cope with simple home
accounts, complex financial analysis and a great deal of scientific and many types of
engineering analysis.

Most of you will probably have encountered a spreadsheet before, but some might
not have. Whatever the situation we will start by assuming no previous knowledge
but go on to develop your skills to the point where the spreadsheet becomes a very
useful tool indeed.

Spreadsheet Basics

The spreadsheet comprises a matrix of cells, each of which is described by its

coordinate position or cell location, e,g, B1 (column B row 1 within the matrix).

Each cell can contain data, which can be words or numbers, and it may also contain
a formula that is hidden beneath the surface of the spreadsheet.

In this sample spreadsheet the columns are headed A, B and C and the rows 1-10. In
cell A3 is the character “A”, while in cell B9 is the number 10. Looking at the data in
columns A and B we see that the values in B are twice those in column A. Looking
behind the surface in cell B6 we see the formula A6*2, i.e. in order to evaluate the
contents of cell B6 take the value in A6 and multiply by 2.

3
If we look at cell A1 then we see it contains the text “Sample Spreadsheet”

Inserting Text, Values and Formulae

To insert a value or text within a cell, simply use the mouse to move to that cell and
type the value that you want to put into it.

To insert a formula it is only very slightly more difficult. Move to the cell and press =,
you can then insert your formula.

The Syntax of Formulae

The formulae follow standard algebraic expressions quite closely but there are some
differences.

A1+A2 - add the contents of cell A1 to the contents of cell A2 and display the result

4
B5-A7 - deduct the contents of cell A7 from the contents of cell B5 and display the
result

C4/A4 - divide the contents of C4 by the contents of A4 and display the result

D7*B3 – Multiply the contents of D7 by the contents of B3 and display the result.

G6^2 – Raise the contents of G6 to the power 2 and display the result

H4^B3 – Raise the contents of H4 to the power of the contents of B3 and display the
result
B3*(A7+H10) – add the contents of A7 and H10 and multiply the result by the
contents of B3

The Precedence of Operators

The spreadsheet does its arithmetic in the following order: -

1) Contents of brackets

2) Raise to the Power

3) Multiply and Divide

4) Add and subtract

Here’s a few examples for you to evaluate

• 1+3*4 (13)
• 4-6/3 (2)
• 2*(3-2) (2)
• 2*3^2 (18)
• 2*(1+3)^2 (32)

Type them in and verify your answers.

Trig Functions and Radians

All of the normal trigonometric operators are available in the spreadsheet but they all
require the angle to be in radians rather than degrees. To convert from degrees to
radians you need to multiply by π and divide by 180 (as 2 π radians is equal to 360
degrees).

In order to access the value of π (pi) you need to use the pi() function. Behind cell
A2 is the formula pi() and the resulting value is shown on the screen.

5
To use a trigonometric function you must first convert the angle from degrees to
radians, for example sin(30) becomes sin(30*pi()/180), which has value 0.5

The inverse trig functions are also available. In order to calculate the angle whose sin
has the value 0.5 you would need to use arcsin(0.5). The spreadsheet actually uses
the function ASIN and the angle calculated is in, you guessed it, radians.

We therefore have to convert the radians into degrees by multiplying by 180 and
dividing by π. In the spreadsheet this appears as ASIN(0.5)*180/PI()

You can also use cos, acos, tan and atan in this way.

Here are some examples for you to try:

• Determine value of cos60

• What is the angle in degrees whose tan is 9999999

• Evaluate cos2(45)-sin2(45)

Logarithmic Functions

Both the natural logarithm and the logarithm to other bases are available as
functions.

6
To obtain the natural logarithm of a number use LN. For example to find the natural
logarithm of 5 use ln(5)

To obtain the logarithm to any other base use LOG(number, base), for example to
find the log of 8 to base 2 use LOG(8,2).

All this means is that we want to find by what index 2 has to be raised to in order to
obtain 8. The answer is 3 i.e. 23 = 8.

If you omit the base from the expression, the spreadsheet assumes that you want
base 10, for example LOG(1000)

( By what index has base 10 to be raised to in order to obtain 1000? The answer is 3
i.e. 103=1000 )

The reverse of the logarithm is the exponent. The spreadsheet also caters for this. In
the particular case of the natural log LN the inverse is given by the EXP function,
while of course you can use the power ^ operator for all other bases.

7
In the example above I have evaluated ln(1000) in cell A2 while in cell A3 I have
evaluated the exponent of this value, bringing us back to where we started.

Let’s do the same exercise using LOG10

A property of the logarithm is that if you add the logs of two numbers together you in
effect multiply the numbers, while if you subtract the logs you divide. This is the
principle upon which calculations used to be carried out with log tables and slide
rules.

Here’s a case in point. Multiply 8 by 16 using logs to base 2. Well 8 is 2 3 and 16 is

24. Adding the logs (indices) we get 3+4 = 7 so the answer is 2 7. Let’s see this in
action on a spreadsheet.

8
OK folks that’s as hard as it gets!

Annotation, Titles, Formatting Etc

In all calculations it is necessary to provide notes or annotation to describe what you

are doing. This is very easily achieved using a spreadsheet, as you merely have to
type the required information into a cell.

Notice that I have used a title which is in a bold font, and how each parameter is
described and appropriate units also specified. Also note how the simple calculation
uses cell references as well as a number C3*C4/100.

You can format the display of a cell’s contents using the ‘Format Cells’ option in the
context menu, which appears when you right click on the cell you wish to format.
Alternatively you can access ‘Format Cells’ in the ‘Cells’ block on the ‘Home’ tab

9
Perhaps the most useful option is that to format Number. Always choose a sensible
number of decimal places, often 0 or 1 in engineering calculations.

10
Copying Cell Contents

Copying Using Relative References

An essential property of the spreadsheet is that it is possible to easily copy data or

formulae between cells.

A very simple example, that will be useful in the future, is in the creation of a list of
numbers counting up from 1 to n. This is very easy to do.

Start by entering the number 1 in a cell, in the example below in cell A1

Next enter a formula in cell A2, adding 1 to the contents of cell A1, A1+1

11
We now have the ability to copy the formula in cell A2 further down the column to
produce a simple table of numbers.

Place the cursor on cell A2 and use the copy function in the ‘Home’ ribbon
(highlighted below).

Note that this frequently used command has a keyboard short cut which you should
remember, it is Ctrl C (hold down the Control or Ctrl key and simultaneously press C).

Having stored the contents of cell A2 in memory we can then arrange to paste the
formula within another cell or cells. First we must tell the spreadsheet which cell or
cells in which to place the copied formula. In this case we require cells A3 to A10
inclusive. Move the mouse to A3 and press and hold down the left hand mouse
button while you pull the mouse down until you reach cell A10. Release the left
mouse button.

This marks the destination cells for the copied formula. Now use the Paste function
on the Home ribbon

12
Again please note that there is a keyboard shortcut that will paste formulae, it is
CTRL V.

If we examine the formula behind A3 you will see that it contains A2+1. Now the
formula that we copied from cell A2 was A1+1 so the spreadsheet has actually
modified the formula while copying it over.

Looking further down the list we see that a similar modification has occurred.

13
This is normally what we want to happen within a spreadsheet, but that is not always
the case. The formula might be interpreted as “add to the contents of the cell that is
above this one the value 1 and display the result”. The formula is automatically
adjusted so that it is always RELATIVE to the cell above.

We can make further use of copied cells to work out the squares and cubes of the
numbers in our column.

I have modified the spreadsheet to include some headings.

The two new formulae are raising the contents of A4 to the powers 2 and 3.

We can copy these formulae down the list of numbers.

14
Note that cells B4 and C4 are surrounded by a dashed line, indicating that they were
the source of the copied formulae (Ctrl C). The block B5 to C13 was marked and the
formulae pasted in using Ctrl V.

Copying Using Absolute Cell References

In the examples above we have copied formulae such that they are relative to the
current cell location. This is often what is required, but there are occasions when it is
not. In those instances we can avoid the problem if we use ABSOLUTE cell
references.

Going back to our original column of numbers we might want to multiply each of them
by a value that is held in another cell. First let’s do what we have been doing and rely
upon relative addressing This is shown in the spreadsheet overleaf.

The formula in cell B7 is A7*B3 and the result is correct 1x3 = 3. Problems occur in
the next and subsequent rows as the reference to the multiplier cell in B3 has been
incremented to be relative to the previous cell location.

In cell B8 the first part of the formula has been correctly incremented from A7 to A8,
but the next part of the formula has also been incremented from B3 to B4. B4 is an
empty cell so that the result of the calculation 2x0 is 0.

Clearly we want the relative addressing to continue to work for the first part of the
formula but not the second. We need the second cell reference to retain its absolute
value B3. This is very easily done.

15
Go back to the first, correct, implementation of the formula in cell B7. Mark the cell
reference B3 using the mouse. Now press function key 4, i.e. F4, when the cell
reference will become an absolute reference rather than a relative reference. This is
signified by the dollar signs before the B and 3

When we copy this modified formula down we obtain what is required, as the
absolute cell reference B3 does not change during the copying operation.

For example note the formula appearing in cell B9, where the first part of the formula
A9 has changed but the second part B3 has not.

Copying by Dragging

As an alternative to using the CTRL key combinations, or the Copy and Paste
buttons, Excel allows us to copy values to adjacent cells by dragging. Simply select
the cells you wish to copy, hover over the small black square that appears the bottom
of the cell until the cursor turns into a small black cross, and hold down the left
mouse button while you drag your formula to adjacent cells.

16
Note that it is only possible to drag a cell in one direction at a time. You can,
however, select a number of cells, to copy one column to the next for example. The
usual rules regarding relative and absolute addressing apply.

Examples for You to Try

Simple Circuit

An electrical circuit comprises a DC power supply and two parallel branches. In the
first branch there is one resistor R1 while in the second there are two resistors in
series R2 and R3. Write a computer programme that will, for any values of V, R1, R2
and R3, evaluate the total effective resistance and the total current flowing in the
circuit.

You might want to know that the total resistance of two parallel resistors is given by: -

1 1 1
= +
𝑅 R1 𝑅2

Bending Stress

A horizontal cantilever beam of length 𝐿 is subject to an end load 𝐹 acting vertically

down. The beam has a rectangular cross section of depth 𝑑 and breadth 𝑏. Write a
spreadsheet program to calculate the maximum bending stress in the beam, for any
value of 𝐿, 𝐹, 𝑏 and 𝑑.

You need to know that the following formulae: -

17
 M=𝐹𝐿
Max bending moment in the cantilever:

Second moment of area of rectangular section:

𝑏 𝑑3
𝐼=
12

Bending stress:
𝑀𝑦
𝜎=
𝐼

Where:

𝑑
𝑦=
2

Be very careful to get the units right.

Projectile fired from a gun

The physics is quite simple. A projectile is fired from a gun leaving with a muzzle
velocity that is known. Assuming that there is no air resistance (bad assumption, but
it makes life much easier!) calculate the trajectory of the projectile (height and
horizontal distance travelled) until it reaches its maximum vertical height. The gun is
elevated so that the projectile is fired from the muzzle making a known angle with the
horizontal.

Some basic engineering science describes the situation

If the angle of elevation of the gun is θ then the vertical velocity will be Vsin θ and the
initial horizontal velocity Vcos θ. The projectile will decelerate in the vertical direction
at the rate determined by the value of g the gravitational constant. Given that we

have assumed that there is no wind resistance, the horizontal velocity will not change
throughout its flight.

When the projectile has reached the top of its flight it will have a zero vertical velocity
and the kinetic energy that was contained within it (within the vertical direction) will
also be zero, it all having been converted to potential energy. We can therefore easily
calculate the height that the projectile will reach using: -

Initial Kinetic energy = Potential energy plus KE at this point

Given that the horizontal velocity is the same throughout, the KE associated with it
cancels from both sides of the equation, leaving: -

Initial KE due to vertical velocity = Potential energy

0.5 mv2 = mgh

0.5 v2 = gh

Knowing the initial vertical velocity and g we can calculate h, the maximum height
attained.

We can also calculate the time taken to reach this maximum height using

𝑣 = 𝑢 + 𝑎𝑡

In this case the initial velocity u is the initial vertical velocity of the projectile, the final
velocity v is zero and the acceleration is that due to gravity g.

You can use these calculated values to check the operation of your spreadsheet. A
snapshot of my attempt is shown below. The muzzle velocity, elevation, mass of
projectile, time interval and gravitational acceleration are all input parameters, the
spreadsheet calculates everything else.

19
The method used is very common in engineering analysis. We assume that the
action takes place in a series of very small steps during which conditions remain
constant. This greatly simplifies the analysis, but does require the power of a
computer to carry out the many simple steps involved.

In the spreadsheet above the size of the step is set by the time interval, currently set
to 0.1 s, and it is assumed that the velocity will be constant over each 0.1 second
step. Using v=u+at it is possible to calculate a new velocity at the end of each step,
which is in turn assumed to be constant over the next step etc.

When writing your spreadsheet, be careful to use absolute cell references for the
values that must not be changed and relative references for those that refer to the
previous cell.

Work out the maximum height reached and the time taken to reach that height using
the formulae above and use those values to check your spreadsheet results. You will
find that the results are not exactly the same, but any gross differences will mean that
you have got the analysis wrong. Why do you think that the results are not exactly
the same, and what could you do to improve matters? The fact that you have ignored
air resistance is not a factor here as it applies to both methods of calculation.

20
Drawing Graphs Using Excel

• Mark data as a block, use the Control (Ctrl) key to pick separate columns

• Select the Insert ribbon

• Select the first option in the Scatter category which denotes a scatter chart with
no line

21
 25
20
15
10
5
Series1
0
Series2
-5 0 1 2 3 4 5
-10
-15
-20
-25

The chart will appear using default settings as shown above. We can, however, make
a number of changes if necessary:

Chart source data- Excel always uses the left hand most column for the x axis, you
can change this by editing the source data by right clicking on the chart and selecting
“Select data...”. You can also add names for each data series here.

Chart Title, X axis, Y axis. These can be changed by clicking on the chart and using
the menu options in the Layout ribbon. When entering axes titles be sure to include
the units. If you wish to remove the legend or a title you can click on these and press
delete.

Add a secondary axis. This feature can be found by right clicking on one of the data
sets and selecting ‘Format data series’ and selecting the ‘Plot series on Secondary
Axis in the ‘Series Options’ page.

Explore the various options available by right clicking on the chart and in the Layout
ribbon and format your chart as shown below.

22
 Projectile
25 25
20
20 15

(m/s)
15 10

er

ty
al

el

ci
V

V
ti

o
c
5
m
h

g
h
e

t
(

)
i

10 0 Height
-5 Vert Vel
5
-10
0 -15
0 1 2 3 4 5 -20
-5 -25
Time (seconds)

Other options are available by clicking on the graph and using the ‘design’, ‘layout’,
or ‘format’ ribbons (many of the same options are available by right clicking on the
appropriate part of the graph).

• Format Axis - Number- Decimal Places if you want to change format of axis
numbering - no. figs after decimal point
• Format Data Series – Change the markers or the lines which connect them
• Add / Format Trendline – create and format trendlines which summarise the
data series. Explore the options and add a trendline with its associated equation
to your graph.

Looking Up Values Using Excel

There is often a requirement to pick out a particular value from a column of values, or
perhaps a table of values.

For example, you might have a list of the names of the students in a class, along with
their assignment marks, and you need to obtain the marks associated with one
student. Type in the following examples as you work through the text.

23
The user of the spreadsheet will type in the student's name and the spreadsheet will
return the mark. In this case, if the user enters "Morton", the mark 75 should be
returned.

Before starting to explain how to do this, you should be aware that in computer
jargon the word ARRAY is used to describe a table of data. In the example above,
the array could be defined as A2:B6, i.e. all of the cells between A2 and B6

The command used to achieve this is called VLOOKUP (vertical look up). It has the
syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value
can be a value, a reference, or a text string. In this case it is the text string "Morton"

Table_array is the table of information in which data is looked up, in our example it is
A2:B6

Col_index_num is the column number in table_array from which the matching value
must be returned. A col_index_num of 1 returns the value in the first column in
table_array; a col_index_num of 2 returns the value in the second column in
table_array, and so on. In our case the marks are in column 2.

Range_lookup can be left out, provided that your data in the first column is in
ascending order, alphabetic or numeric. If this is not the case you need to set this
variable to false.

The command becomes VLOOKUP(“Morton”,A2:B6,2) and it returns the value 75. In

the small example spreadsheet below I have put the required student's name in cell
C8 and the VLOOKUP formula in cell C9. The formula becomes
VLOOKUP(C8,A2:B6,2). You can see that the spreadsheet has returned the correct
value for the mark, i.e. 75.

This can of course be extended to cover more than two columns.

24
In this case the students have completed 4 assignments, so the size of the array has
been increased to A3:E7. The VLOOKUP command specifies the name of the
student, held in cell C9, and the column in which the marks related to a particular
assignment are stored. Here the user of the spreadsheet has picked assignment No.
3, but the mark returned by the formula is that for assignment 2. Why do you think
that is?

The reason is that the array counts columns starting from column A3, so the marks
for assignment 3 are actually stored in column 4. We can amend our formula
accordingly, by adding a 1 to the value for the Col_index_num. The formula becomes

=VLOOKUP(C9,A3:E7,C10+1)

This returns the correct answer in the spreadsheet. Morton obtained 38% for
assignment 3.

25
There is a clever way to avoid this difficulty. It is possible to use a command that will
automatically locate the particular column that the desired value is stored in. That
command is called MATCH. The syntax for the MATCH command is

MATCH(lookup_value,lookup_array,match_type)

Where the look_up_value is the one to be found, and the lookup_array, refers to the
relevant section of the spreadsheet in which to carry out the search. If you ensure
that the values in that array are in ascending order, you can forget about the 3 rd
parameter, the match_type.

We need to MATCH the required value, in this case 2, within the row of values from
A2 to E2. The MATCH command returns the column number as number 3 in cell

26
C11, which is correct. Our VLOOKUP formula has been amended to take the
contents of this cell as an input.

A more elegant solution takes out the Column Number cell and puts the result from
the MATCH formula directly into the VLOOKUP formula.

You should use the Excel help system to read more about the Commands VLOOKUP
and MATCH, including the variables we have not used in these examples.

27
The help system can be found by pressing ‘F1’ or using the question mark icon on
the top left hand side of the Excel window. You can use the search system to find the
page for VLOOKUP. Having found VLOOKUP, also refer to the “See Also” section of
the help system to explore INDEX, LOOKUP, MATCH and HLOOKUP.

28
An Example for you to try

The following example was taken from the 1998 examination paper. You should
enter the data and then work out what formula to enter in cell G19 to obtain the
correct value of K for the particular values of r/d and angle that the user inputs in
cells D18 and D19.

29
Conditions in Excel

In many situations in engineering and beyond, an outcome is dependent on

conditions. For example

• If the student scores less than 40% he will fail the exam.

• If the person earns more than £32,011 and does not have any allowances,
s/he will pay higher rate tax.

• If the boiler pressure is greater than 15 bar, the signal is green, and the brakes
are off, the steam train will start.

How do we handle these situations in Excel? The answer is to use a form of the IF
formula which uses the syntax: -

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE.

Here are some examples: -

A=B A equals B
A<B A smaller than B
A>B A greater than B
A<=B A smaller than or equal to B
A>=B A greater than or equal to B

30
In this case the formula in cell C13 determines whether the word Fail or Pass should
be written. Note that the words have to be contained within quotation marks. The
logical test carried out is "is the value in cell C12 less than 40" which is written as
C12<40, the value_if_true is "Fail" and the value_if _false is "Pass". Enter the above
spreadsheet and use a suitable look up formula in C12 to obtain the score of a
student named in C11.

Examples

In the following example, if the value in cell A10 is 100, then logical_test is TRUE,
and the total value for the range B5:B15 is calculated. Otherwise, logical_test is
FALSE, and empty text ("") is returned that blanks the cell that contains the IF
function.

IF(A10=100,SUM(B5:B15),"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual
Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the
following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month,
generating text for a message with the following formulas:

IF(B2>C2,"Over Budget","OK") equals "Over Budget"

IF(B3>C3,"Over Budget","OK") equals "OK"

What if we need to consider more than one condition?

Consider an electronic component that is used for switching heavy currents. It has
limiting voltage levels on both the supply rail and bus to be switched. The device will
function if the supply voltage lies in the range 3-5.5V, while the potential over the line
to be switched must not exceed 50V

The answer is to use the AND logical function.

31
This example above takes into account only two of the conditions, that the supply be
equal to or less than 5.5V and that the line to be switched is not higher than 50V. We
can easily add the condition that the supply must not fall below 3.5V.

Notice how we can continue to add further conditions, all of which must be satisfied
and that all of the conditions pertaining to the AND logical function must be contained
within brackets.

Take a look at the Excel Help files for IF, AND, OR and NOT, and about Logical
Functions.

More Clever Functions

There are times when you want to do the following: -

Sum the total of a series of values

=SUM(A5:A20)

Find the average of a series of values

=AVERAGE(B4:B20)

32
Count the number of occurrences of a particular value or range of values

=COUNTIF(B20:B40,<40)

Counts the number of values in the cells B20 to B40 that are less than 40. If you
were to look up the help feature in Excel on this command you would read the
following: -

Syntax

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.
Criteria is the criteria in the form of a number, expression, or text that defines which
cells will be counted. For example, criteria can be expressed as 32, "32", ">32",
"apples".

Examples

Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively:

COUNTIF(A3:A6,"apples") equals 2
Suppose B3:B6 contain 32, 54, 75, 86,
respectively: COUNTIF(B3:B6,">55") equals 2

1) Sum the total of a series of values that meet certain criteria

=SUMIF(A10:A20,<30,B10:B15)

Again the HELP within Excel provides a clear explanation

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which
cells will be added. For example, criteria can be expressed as 32, "32", ">32",
"apples".

Sum_range are the actual cells to sum. The cells in sum_range are summed only if
their corresponding cells in range match the criteria. If sum_range is omitted, the
cells in range are summed.

Note that the you can leave out the Sum_range in which case Excel just looks within
the Range.

Example

33
Suppose A1:A4 contain the following property values for four homes: $100,000,
$200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales
commissions on each of the corresponding property values: $7,000, $14,000,
$21,000, $28,000.

=SUMIF(A1:A4,">160000",B1:B4) equals $63,000

Example for you to do, taken from past examination paper

A spreadsheet has to be set up for the analysis of a student's examination results as

shown below. The purpose of the spreadsheet is to determine the eventual result
from the student, i.e. can the person proceed to the next level, or must they repeat
modules.

The criterion for a pass in any one module is that the student must score at least 4
grade points.

The criterion for the student to PROCEED is that he/she must pass MAT129 and
have failed not more than two other modules.

The criterion for the student to REPEAT is that he/she has failed MAT129 or that
he/she has failed more than one module.

Work out the formulae to put into cells D13 and D15.

34
Using Excel in design - Goal Seek

Spreadsheets can be very useful when doing design calculations; you can easily
explore the effect of varying various parameters. Excel has a very useful feature
called Goal Seek. Goal seek allows you to stipulate the required result of a series of
calculations and Excel will find the value of one piece of input data that gives that
result. E.g. you might have written a spreadsheet to calculate the current flowing in a
circuit. Goal Seek allows you to specify the required current and will work out the
value of an input parameter that is required to obtain that current, e.g. the input
voltage, or the value of a resistor.

Further, spreadsheets are capable of doing simple numerical analysis.

One interpretation of numerical analysis is doing calculations by means of a

computer, rather than using complex conventional analysis. For example it is
sometimes difficult to use calculus to calculate the area of an oddly shaped
component, but it is possible to get a sufficiently good approximation to the area by
dividing it into a number of small equal squares and adding them together. Generally
the answer becomes more accurate the more sections that you use, but if this is
carried to extremes, inaccuracies due to rounding errors start to become significant.

In this next example I demonstrate how the moment of inertia of the rotor of an
electric motor can be estimated using a spreadsheet. There is probably a more
elegant direct mathematical solution, but in engineering the end normally justifies the
means!

First how do we calculate the inertia of an object? Actually for bits that rotate, it is the
polar moment of inertia that we need, and it is defined as: -

I = mr2

i.e. the polar moment of inertia is equal to the sum of all of the various elements of
mass m multiplied by the square of the radius r of that mass about the axis being
considered.

Now our electric motor has four poles, and the rotor consists of a steel former about
which wire is wound. The diagram below shows the shape of the former in way of
one of the poles, the other three poles are identical but not shown.

35
For the purposes of this analysis we will ignore the central cylindrical bit - that is easy
to calculate directly - and the windings, concentrating on the T shaped piece that
remains. I have divided the shape into a series of equally spaced sections and have
calculated the area of the sections using simple trigonometry. You can see that the
innermost section has a minimum radius of 8 mm, a mean radius of 9 mm and a
maximum radius of 10 mm. There are 7 similar sections, while the outermost part has
a larger area.

In the spreadsheet below, the polar moments of inertia of each of the sections is
evaluated and the total I summed.

36
Now suppose I wanted to end up with an inertia value of exactly 15 kgmm^2. We can
use Goal Seek to determine, for example, the minimum mean radius that will
generate that inertia. To use Goal Seek go to the ‘What-if Analysis’ option in the
‘Data Tools’ part of the ‘Data’ ribbon and select ‘Goal Seek’

37
The Goal Seek window appears and invites you to select the cell whose value you
want to set, in this case D17, the total inertia. You then input the value you want
where it says To value. Finally you have to select the cell whose value you are
prepared to change - in this case C8 the minimum mean radius.

38
Note how the spreadsheet has found, within a reasonable degree of accuracy, the
required value of inertia. Also note how all of the mean radius values have been
modified.

Here's an example for you to try: -

The second moment of area of a rectangular section I is given by :-

I = bd 3
12

Where:
b = Breadth of section (mm)
d = Depth of section (mm)
I = Second moment of area about neutral axis (mm4)

The neutral axis is the horizontal line through the centre of the rectangle.

If we want to find the value of the second moment of area about another axis, for
example about the base of the rectangle, we use the parallel axis theorem. The
parallel axis theorem states that : -

39
 Ia = I NN + Aa2

Where Ia is the second moment of area about axis a, which is parallel to the neutral
axis and at distance a from it. A is the area of the section.

Write a spreadsheet to calculate the second moment of area of this beam section
about its neutral axis - the central horizontal centre line. You will see that the central
web has a neutral axis that is coincident with that of the section, so you only have to
apply the parallel axis theorem to the flanges. Note further that they are identical, so
you only need to do the calculation once and multiply the result by two. Configure
your spreadsheet as follows: -

40
Using your spreadsheet and Goal Seek, determine what width of flange would be
required to obtain a total I value of 250,000 mm4.

Answers

I value of section as drawn 198,340 mm4

Width of flange required to generate an I value of 250,000 mm 4 is 38.814 mm

41