1 / 27

259 Lecture 10 Spring 2013

259 Lecture 10 Spring 2013. Advanced Excel Topics – More User Defined Functions; Conditional Statements. Outline. More User Defined Functions! Perp_Bisector_Slope () Perp_Bisector_Intercept () Quadratic_1() Quadratic_2() Pseudo Code IF…THEN…ELSE Statements Modified Quadratic Function

emil
Télécharger la présentation

259 Lecture 10 Spring 2013

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 259 Lecture 10 Spring 2013 Advanced Excel Topics – More User Defined Functions; Conditional Statements

  2. Outline • More User Defined Functions! • Perp_Bisector_Slope() • Perp_Bisector_Intercept() • Quadratic_1() • Quadratic_2() • Pseudo Code • IF…THEN…ELSE Statements • Modified Quadratic Function • A Piecewise Defined Function

  3. Perp_Bisector_Slope() • Example 1: Create a user defined function Perp_Bisector_Slope() that will return the slope of the line that is the “perpendicular bisector” of the line segment determined by the points (x1,y1) and (x2,y2).

  4. Perp_Bisector_Slope() • Given: • Points (x1,y1) and (x2,y2). • Line L is the perpendicular bisector of the line segment determined by the points (x1,y1) and (x2,y2). • Find: • The slope of line L.

  5. Perp_Bisector_Slope() • Pseudo Code: (1) Find the slope of the line segment determined by the points (x1,y1) and (x2,y2), and then store the result in the variable m1. (2) Find the negative reciprocal of m1 and then store the result in the variable m2 . (3) Return the value stored in the variable m2 as the result of the user defined function.

  6. Perp_Bisector_Slope() Function Perp_Bisector_Slope(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double 'Returns the slope of the line that is the '"perpendicular bisector" of the line segment 'determined by points (x1,y1) and (x2,y2). 'The function does NOT work for vertical lines Dim m1 As Double, m2 As Double m1 = (y2 - y1) / (x2 - x1) 'negative reciprocal of m1 m2 = -1 / m1 Perp_Bisector_Slope = m2 End Function

  7. Perp_Bisector_Intercept() • Example 2: Create a user defined function Perp_Bisector_Intercept() that will return the y-intercept of the line that is the “perpendicular bisector” of the line determined by the points (x1,y1) and (x2,y2).

  8. Perp_Bisector_Intercept() • Given: • Points (x1,y1) and (x2,y2). • Line L is the perpendicular bisector of the line segment determined by the points (x1,y1) and (x2,y2). • Find: • The y-intercept of line L.

  9. Perp_Bisector_Intercept() • Pseudo Code: (1) Find the slope of the line by using the Perp_Bisector_Slope(x1, y1, x2, y2) function previously defined, and then store the result in the variable m. (2) Find the x-component of the midpoint of the line segment, and then store the result in the variable Midpoint_X. (3) Find the y-component of the midpoint of the line segment, and then store the result in the variable Midpoint_Y. (4) Return the y-intercept of line L, by substituting the coordinates of the midpoint into the expression y -m*x.

  10. Perp_Bisector_Intercept() Function Perp_Bisector_Intercept(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double 'Returns the y-intercept of the line that is the 'perpendicular bisector" of the line segment 'determined by points (x1,y1) and (x2,y2). 'The function does NOT work for vertical lines Dim m As Double Dim Midpoint_X As Double, Midpoint_Y As Double m = Perp_Bisector_Slope(x1, y1, x2, y2) Midpoint_X = (x1 + x2) / 2 Midpoint_Y = (y1 + y2) / 2 'b = y - m*x 'put in a known point (Midpoint_X, Midpoint_Y) on the line Perp_Bisector_Intercept = Midpoint_Y - m * Midpoint_X End Function

  11. Quadratic_1() • Example 3: Create a user defined function Quadratic_1() that will return the first real root of a quadratic equation Ax2 + Bx + C = 0.

  12. Quadratic_1() • Given: • A, B, and C • Roots of Ax2 + Bx + C = 0 are and • Find: • The first real root of a quadratic equation Ax2 + Bx + C = 0.

  13. Quadratic_1() • Pseudo Code: (1) Substitute the A, B, and C values into the formula and return the result of this expression.

  14. Quadratic_1() Function Quadratic_1(A As Double, B As Double, C As Double) As Double 'Returns the first real root of a quadratic equation 'Ax^2+Bx+C=0 'Does NOT return a complex root Quadratic_1 = (-B + Sqr(B ^ 2 - 4 * A * C)) / (2 * A) End Function

  15. Quadratic_2() • Example 4: Create a user defined function Quadratic_2() that will return the second real root of a quadratic equation Ax2 + Bx + C = 0.

  16. Quadratic_2() • Given: • A, B, and C • Roots of Ax2 + Bx + C = 0 are and • Find: • The second real root of a quadratic equation Ax2 + Bx + C = 0.

  17. Quadratic_2() • Pseudo Code: (1) Substitute the A, B, and C values into the formula and return the result of this expression.

  18. Quadratic_2() Function Quadratic_2(A As Double, B As Double, C As Double) As Double 'Returns the first real root of a quadratic equation 'Ax^2+Bx+C=0 'Does NOT return a complex root Quadratic_1 = (-B - Sqr(B ^ 2 - 4 * A * C)) / (2 * A) End Function

  19. IF…THEN…ELSE Statements • Just like in Excel, we can create conditional statements within VBA! • One of the most useful conditional statements in VBA is the IF…THEN…ELSE statement!

  20. IF…THEN…ELSE Syntax • Syntax: If condition_1 Then result_1 ElseIf condition_2 Then result_2 ... ElseIfcondition_n Then result_n Else result_else End If • How the command works: • condition_1 to condition_n are evaluated in the order listed. • Once a condition is found to be true, the IF…THEN…ELSE statement will execute the corresponding code and not evaluate the conditions any further. • result_1 to result_n is the code that is executed once a condition is found to be true. • If no condition is met, then the Else portion of the IF…THEN…ELSE statement will be executed. • Note that the ElseIf and Else portions are optional.

  21. Quadratic_1_Improved() • Example 5: Modify the Quadratic_1() user defined function to return the first root (real or complex). • For example x2+4x+8=0 should return (2+2i) and x2+5x+6=0 should return -2. • Then modify the VBA code for this new function to make a user defined function to find the other root!

  22. Quadratic_1_Improved() • Given: A, B, and C Roots of Ax2 + Bx + C = 0 are: • Find: The first root of a quadratic equation Ax2 + Bx + C = 0 .

  23. Quadratic_1_Improved() • Pseudo Code: (1) If B^2 – 4AC  0, then substitute the A, B, and C values into the formula and return the value of this expression. (2) Otherwise, put the A, B, and C values into the formula and store the result in the variable Real_Part. (3) Substitute the A, B, and C values into the formula and store the result in the variable Imaginary_Part. (4) Return the string value “{Real_Part} + {Imaginary_Part} i “ by substituting the actual numeric values for Real_Part and Imaginary_Part into {Real_Part} and {Imaginary_Part} .

  24. Quadratic_1_Improved() Function Quadratic_1_Improved(A As Double, B As Double, C As Double) As Variant 'Returns the first root of a quadratic equation Ax^2+Bx+C=0 'Returns both real and complex roots Dim Real_Part As Double, Imaginary_Part As String If B ^ 2 - 4 * A * C >= 0 Then Quadratic_1_Improved = (-B + Sqr(B ^ 2 - 4 * A * C)) / (2 * A) Else Real_Part = -B / (2 * A) Imaginary_Part = Sqr(Abs(B ^ 2 - 4 * A * C)) / (2 * A) Quadratic_1_Improved = Str(Real_Part) + "+ " + Str(Imaginary_Part) + "i" End If End Function

  25. A piecewise “user defined function” f(x) • Example 6: Use VBA IF statement(s) to create the following piecewise “user defined function” f(x): • Plot a graph of this function!

  26. A piecewise “user defined function” f(x) Function f(x As Double) As Double 'Returns x^3, if x<0 'Returns x^2, if 0<=x<=3 'Returns Sqr(x-3), if x>3 If x<0 Then f = x^3 End If If (x >= 0) And (x <= 3) Then f = x^2 End If If (x>3) Then f = Sqr(x-3) End If End Function

  27. References • User Defined Functions Notes – John Albers • IF…THEN…ELSE Statements (p. 20 of this lecture) - http://www.techonthenet.com/excel/formulas/if_then.php

More Related