159 Lecture 5

1
159 Lecture 5

• Mathematical Functions in Excel

2
Mathematical Functions

• Excel has many built-in mathematical functions!
• The complete list can be found online here
  http//office.microsoft.com/en-us/excel/CH10064536
  1033.aspx
• Here are some familiar mathematical functions

3
Common Mathematical Functions

• SQRT
• ABS
• EXP
• LN
• LOG10
• POWER
• Raises a number to a specified power.
• ROUND
• Rounds a number to a specified number of decimal
  places.

• SIN
• COS
• TAN
• CSC
• SEC
• COT
• PI
• RADIANS

4
Trigonometric Functions

• In Excel, mathematical functions work as one
  would expect!
• For example, the syntax for the sine function is
  SIN(number), where number is the angle in
  radians for which you want the sine.
• Note that if an argument is in degrees, you can
  use the functions PI or RADIANS to convert the
  number to radians!

5
Example 1

• Make a table for f(x) sin x, for x in the
  x-interval 0, 2?, in increments of ?/8.
• Plot the graph of y sin x on the interval 0,
  2?.
• How can the graph be refined to look more like
  what we are used to seeing (on paper or on a
  graphing calculator)?

6
Example 1 (cont.)
7
Example 2

• Create the function g(x) tan x, using the sine
  and cosine functions in Excel.
• Compare your created tangent function g(x) to the
  actual built-in tangent function!
• Make a table of tangent function values and plot
  this function, as we did in Example 1.

8
Example 2 (cont.)
9
Best-Fit Lines Revisited!

• Recall that for data points (x1,y1), (x2,y2), ,
  (xn,yn), the best-fit line is defined by y
  axb, with
• Using the SUMPRODUCT function, we can compute
  best-fit lines more efficiently!

10
The SUMPRODUCT Function

• Syntax SUMPRODUCT(array1,array2,array3, ...)
  where array1, array2, array3, ...   are 2 to 255
  arrays whose components you want to multiply and
  then add.
• Multiplies corresponding components in the given
  arrays, and returns the sum of those products.
• The array arguments must have the same
  dimensions. If they do not, SUMPRODUCT returns
  the VALUE! error value.
• SUMPRODUCT treats array entries that are not
  numeric as if they were zeros.

11
Example 3

• Construct a best-fit line for the toad data,
  using the SUMPRODUCT function.

12
Example 3 (cont.)
13
Example 4

• Another way to find a best-fit line for some data
  is with the SLOPE and INTERCEPT functions!
• Repeat Example 3 with these functions.
• To do so, we need to know what these functions do!

14
The SLOPE Function

• Syntax SLOPE(known_y's,known_x's)
• known_y's is an array or cell range of numeric
  dependent data points.
• known_x's is the set of independent data points.
• Returns the slope of the linear regression line
  through data points in known_y's and known_x's.
• The arguments must be either numbers or names,
  arrays, or references that contain numbers.
• If an array or reference argument contains text,
  logical values, or empty cells, those values are
  ignored however, cells with the value zero are
  included.
• If known_y's and known_x's are empty or have a
  different number of data points, SLOPE returns
  the N/A error value.

15
The INTERCEPT Function

• Syntax INTERCEPT(known_y's,known_x's)
• known_y's is the dependent set of observations or
  data.
• known_x's is the independent set of observations
  or data.
• Calculates the y-intercept of the best-fit
  regression line plotted through the known
  x-values and known y-values.
• The arguments should be either numbers or names,
  arrays, or references that contain numbers.
• If an array or reference argument contains text,
  logical values, or empty cells, those values are
  ignored however, cells with the value zero are
  included.
• If known_y's and known_x's contain a different
  number of data points or contain no data points,
  INTERCEPT returns the N/A error value.

16
Example 4 (cont.)
17
A Better Trendline for the Toads Data

• Using Excels Trendline feature, we can find a
  function that fits the data better than a linear
  function!
• It turns out that an exponential function does a
  much better job!

18
Example 5

• Using the exponential trendline found by Excel,
  along with the POWER and EXP function, compare
  the actual toad data to that found with the
  exponential trendline y 910-62e0.0779x.
• Note that Excel 2007 may give y
  910-62e0.077x.

19
Example 5 (cont.)

• A way to fix the missing digits in the
  trendline equation can be found here
  http//support.microsoft.com/kb/282135
• Unfortunately, this may introduce a new problem!

20
Rates of Change

• Excel is useful for creating function tables to
  investigate rates of change!
• Recall that for a function y f(x), the average
  rate of change between points (x1,f(x1)) and
  (x2,f(x2)) is given by
• The instantaneous rate of change at the point
  (x1,f(x1)) is found by taking the limit
• provided this limit exists.

21
Rates of Change (cont.)

• If we let x1 a and x2 a h, then our
  definitions become
• Average rate of change of y f(x) between points
  (a,f(a)) and (ah,f(ah))
• Instantaneous rate of change of y f(x) at the
  point (a,f(a))
• provided this limit exists.
• An idea related to rates of change is that of
  tangent line.

22
The Tangent Line

• The line tangent to the graph of the function y
  f(x), at the point (a,f(a)) is the line through
  the point (a,f(a)), with slope mtan given by
• provided this limit exists.
• Notice that mtan is the instantaneous rate of
  change of y f(x) at the point (a,f(a))!

23
The Derivative

• Corresponding to x a in the domain of f(x), for
  which the graph of y f(x) has a tangent line at
  (a,f(a)), is exactly one slope.
• Thus, we can define a function that specifies the
  slope of the tangent line to y f(x) when x a.
• The derivative of the function y f(x) at x a
  is the number f(a), given by
• provided this limit exists.
• The derivative f(a) gives the instantaneous rate
  of change of f with respect to x when x a.

24
Example 6

• If a cylindrical tank holds 100,000 gallons of
  water, which can be drained from the bottom of
  the tank in an hour, then Torricelis Law gives
  the volume V of the water remaining in the tank
  after t minutes as
• Find the average rate at which the water is
  draining out of the tank between times
• t 10 min and t 20 min
• t 10 min and t 15 min
• t 10 min and t 11 min
• t 10 min and t 10.1 min
• t 10 min and t 10.01 min
• t 10 min and t 10.001 min
• t 10 min and t 10.0001 min
• t 10 min and t 10.00001 min
• Estimate the instantaneous rate at which water is
  flowing out of the tank at t 10 min.
• (If time) Graph y V(t) from Example 6, along
  with the tangent line y L(t) at t 10 on the
  same xy-coordinate axes.

25
Example 6 (cont.)
26
Engineering Functions

• In addition to the standard mathematical and
  trigonometric functions, Excel has several
  built-in functions that are useful in applied
  mathematics areas, including engineering!
• These functions may need to be added in to the
  set of available functions.

27
Engineering Functions (cont.)

• To see if the Engineering Functions are included,
  look in the Function Library group in the
  Formulas tab.
• You may have to click on the More Tools drop-down
  menu.
• If Engineering Functions is not listed, you will
  have to add them in, via Add-Ins.

28
Loading Excel Add-Ins

• Click the Microsoft Office Button , and then
  click Excel Options.
• Click the Add-Ins category.
• In the Manage box, click Excel Add-ins, and then
  click Go.
• To load an Excel add-in, do the following
• In the Add-Ins available box, select the check
  box next to the add-in that you want to load, and
  then click OK. Tip  If the add-in that you want
  to use is not listed in the Add-Ins available
  box, click Browse, and then locate the add-in.
  Add-ins that are not available on your computer
  can be downloaded from Downloads on Office
  Online.
• If the add-in is not currently installed on your
  computer, click Yes to install it. Tip  Follow
  the setup instructions as needed.
• To unload an Excel add-in, do the following
• In the Add-Ins available box, clear the check box
  next to the add-in that you want to unload, and
  then click OK.
• To remove the add-in from the Ribbon, restart
  Excel.

29
Engineering Functions (cont.)

• Examples of the functions available include
• BIN2DEC, which converts a binary number (base 2)
  to a decimal number.
• HEX2DEC, which converts a hexadecimal number
  (base 16) to a decimal number.
• CONVERT, which converts a number in one
  measurement system to another.
• COMPLEX, which turns a pair of real numbers into
  a complex number.
• IMPRODUCT, which multiplies complex numbers.

30
Example 7

• Try each of the following commands
• BIN2DEC(111000101)
• HEX2DEC(FF) - HEX2DEC(F8)
• CONVERT(50, mi, km)
• COMPLEX(2,3)
• IMPRODUCT(23i, 1-i)

31
References

• James Stewart, Calculus (Early Transcendentals),
  5th edition
• Microsoft online help http//office.microsoft.com
  /en-us/excel/CH100645361033.aspx