410 likes | 534 Views
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.
E N D
what Excel is really good at representation of the results a column of values calculations based on those values
THRUST DRAG
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
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
note Pa and Pr calculations, then, have to be made for a RANGE of velocities. (i.e. "RANGE" = Excel).
Aspect Ratio Wing Surface Area
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
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
a and b a= (ρ * S * C) / 2 = (1 * 104 * .001268 ) /2 = 5.634 b = (2 * W2) / (π * A * ρ * S) = 2.46 x 109
constants will be accessed by formulas using ABSOLUTE addressing
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
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
3: Vertical Climb Capability Vc = (Pa - Pr) / W = (Pa - Pr) / 3,440,000
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
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
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
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
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
Plot the Power Required and Power Available versus Flight Speed - use SCATTER rather than LINE plot (will pick leftmost column as the independent variable)
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.
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.
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
copy the table headings and first row of calculations to create a new table as shown • label the table ‘Maximum Speed’
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
the result is that the Climb Speed is zero when the flight speed is 67.40206 m/s • is this right?
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
set the initial value of the flight speed to 200 m/s • restart the Goal Seek tool
now we see that the maximum level flight speed is 310.0789 m/s
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
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
plot the derivative or slope versus the flight speed • move the plot to a worksheet titled ‘dVcdV’
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