1 / 39

Numerical Methods in Excel

Numerical Methods in Excel. what Excel is really good at. representation of the results. a column of values. calculations based on those values. THRUST. DRAG. In flight, the thrust produced by an aircraft’s engine is used to overcome the drag of flying through thick air.

alyson
Download Presentation

Numerical Methods in Excel

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. Numerical Methods in Excel

  2. what Excel is really good at representation of the results a column of values calculations based on those values

  3. THRUST DRAG

  4. In flight, thethrustproduced by an aircraft’s engine is used to overcome the drag of flying through thick air. • Thrust and Drag are FORCES, measured in Newtons, but since the plane is flying through "liquid" air ... • Power = Force x VELOCITY is relevant to keeping the plane aloft, measured in Watts. • There are two types of Power: • Power required = Drag * Velocity • Power available = Thrust * Velocity

  5. The faster the plane goes, the more power is required to keep it aloft, like pushing through a liquid Equation 1: Pr = Drag * V • The faster the plane goes, the more power is availableEquation 2: Pa = Thrust * V • When Pr exceeds Pa, the plane falls. When Pa exceeds Pr, the excess power can be used to overcome WEIGHT to cause the aircraft to climb, called Vertical Climb Capability: • Equation 3: Vc = (Pa - Pr) / Weight • ...must be POSITIVE

  6. note Pa and Pr calculations, then, have to be made for a RANGE of velocities. (i.e. "RANGE" = Excel).

  7. Calculating DRAG: how does a plane "lift"?

  8. Aspect Ratio Wing Surface Area

  9. Things we know about a Boeing 747 Our Constants: V = velocity = a range = 30 to 350 m/s W = weight = 3,440,000 Newtons T = maximum thrust = 567,000 Newtons ρ = air density - a constant = 1at the earth's surface S = wing (bottom) surface area - a constant = ~ 104 sq. m. A = aspect ratio = wing effective front / bottom = ~ 1/4 C = drag coefficient = how much of the wing's "lift" is lost due to the plane's size and weight = .0011268

  10. Drag Calculation - a function of almost everything on the plane and in the air V = velocity, speed, flight speed W = weight = 3,440,000 Newtons T = maximum thrust = 567,000 Newtons ρ = air density = 1 S = wing (bottom) surface area = 104 A = "aspect ratio" = ¼ C = drag coefficient = .0011268

  11. a and b a= (ρ * S * C) / 2 = (1 * 104 * .001268 ) /2 = 5.634 b = (2 * W2) / (π * A * ρ * S) = 2.46 x 109

  12. constants will be accessed by formulas using ABSOLUTE addressing

  13. 1: Power Required = V * Drag a = 5.634 b =2.46 x 109 Pr = V * Drag = V( a*V2 + b/V2 ) = a*V3+ b/V = (5.634)V3 + (2.46x109)/V V = velocity, speed, flight speed W = weight = 3,440,000 Newtons T = maximum thrust = 567,000 Newtons ρ = air density = 1 S = wing (bottom) surface area = 104 A = "aspect ratio" = 1/4 C = drag coefficient = .0011268

  14. Thrust Calculation • This is easy, it's just engine power T = 567,000 Newtons 2: Power Available Pa = V * Thrust = V * 567,000 in Watts

  15. 3: Vertical Climb Capability Vc = (Pa - Pr) / W = (Pa - Pr) / 3,440,000

  16. let's do it in Excel • Need constants, to be used with absolute addressing: a, b, T, W • Need a column of velocities: 30-350, by 20s • Need a column to calculate Pr = (a*V3 + b/V) / 106 for each velocity (in Millions of Watts) • Need a column to calculate Pa =( V * T) / 106 for each velocity • Need a column to calculate vertical rate of climb = 106* (Pa-Pr) / W to determine at what velocities flight occurs • Chart Pa and Pr on one Scatter Plot using the velocity column as the X-axis • Chart (Pa-Pr) / W on a second scatter plot to show vertical rate of climb

  17. double click on the worksheet tab and rename the worksheet to ‘calculations’ • create the table of calculation constants • create the table for calculation results with the formatting shown

  18. enter the formula to calculate the Power Required • = ($B$1 * A10^3 + $B$2/A10) / 10^6 • note that the calculated power is divided by 106so that the results are shown in MillionsOfWatts • format the calculated results as shown

  19. enter the formula to calculate the Power Available • = $B$3 * A10 / 10^6 • note that the calculated power is divided by 106 so that the results are shown in MW • format the calculated results as shown

  20. enter the formula to calculate the Climb Velocity or Rate of Climb • = (C10 - B10) *10^6 / $B$4 • note that the excess power is multiplied by 106to convert to N*m or Watts. • format the calculated results as shown

  21. Plot the Power Required and Power Available versus Flight Speed - use SCATTER rather than LINE plot (will pick leftmost column as the independent variable)

  22. format the plot as shown

  23. This chart illustrates the variation of power required and power available with respect to aircraft speed for a Boeing 747. • At speeds where the power available is greater than the power required, the excess power can be used to accelerate the aircraft. • The two speeds at which the power available is equal to the power required are the minimum and maximum level flight speed for the aircraft.

  24. plot the Rate of Climb

  25. There is a particular speed at which the excess power ( and therefore, the rate of climb) are a maximum. • Here, the maximum flight speed is indicated by the speed at which the slope of the curve is zero, i.e., when the excess power is zero. • The two speeds at which the power available is equal to the power required are the minimum and maximum level flight speed for the aircraft.

  26. some new calculations

  27. note that the speed at which the Rate of Climb is zero is also the speed at which the Excess Power is zero • this is the Maximum Level Flight Speed • we will locate the maximum speed by finding the speed at which the Rate of Climb is zero

  28. copy the table headings and first row of calculations to create a new table as shown • label the table ‘Maximum Speed’

  29. in the Data Tools group on the Data tab, select Goal Seek

  30. the Goal Seek tool will search for values of Flight Speed (cell $G$10) such that the Climb Speed (cell J10) is zero • we are using Goal Seek as a ‘root finding’ tool, i.e., we want to find the value of V such that Vc(V) = 0

  31. the result is that the Climb Speed is zero when the flight speed is 67.40206 m/s • is this right?

  32. the speed that Goal Seek found is the minimum speed rather than the maximum speed • this is a result of starting cell $G$10 with a value of 30 m/s • the closest solution was the minimum speed

  33. set the initial value of the flight speed to 200 m/s • restart the Goal Seek tool

  34. now we see that the maximum level flight speed is 310.0789 m/s

  35. here, we can verify that we have found the correct root to Vc(V) = 0 • now we would like to find the maximum rate of climb and the speed at which it occurs • note that the rate of climb is a maximum when the slope of Vc(V) is zero i.e. dVc/dV = 0

  36. the slope of the Vc(V) curve can be written as • we can approximate the slope at a speed of 90 m/s with • recall that the slope is equivalent to the derivative of the function which is defined as • we are using a ‘central difference’ approximation to the derivative at 90 m/s • implement this approximation as a cell formula and replicate it for all but the first and last values of velocity

  37. plot the derivative or slope versus the flight speed • move the plot to a worksheet titled ‘dVcdV’

  38. recall that we are looking for the speed for maximum rate of climb which will be the speed at which the slope is equal to zero • from the plot, the speed for maximum rate of climb is approximately 190 m/s

  39. verify that these results appear to be correct

More Related