I was asked to explain a bit about how the two new worksheets work, so I will do that here.
Sprint Distance Calculator
First of all, if you do not know what sprint distance is and why it’s important to calculate it, you should read this whitepaper by ex Killer Bees’ student Andrew Palardy. It explains the importance of factoring in acceleration time and voltage sag into your drivetrain calculation, and how the Killer Bees in 2012 sped up their robot by “slowing it down.”
The sprint distance formula that I used was almost exactly copied from the C code of Ether’s Drivetrain Acceleration Model. That code was converted to VBA code, which was used to create a custom Excel function TimeToDist(). This is the reason the new version requires you to enable macros. I made a few simplifications by changing some inputs in Ether’s model into constants, to lower the number of manual inputs needed. These are the constants:
- Kro = 10 (constant internal friction coefficient)
- Krv = 0 (velocity-linear internal friction coefficient)
- Vbat = 12.7 (Unloaded battery voltage)
- Vspec = 12 (Voltage used for the motor specs)
- uk = 0.7 (Kinetic friction coefficient)
- Rcom = 0.013 (Resistance between battery and PDP)
- Rone = 0.002 (Resistance between PDP and one motor)
- dt = 0.001 (simulation timestep)
The Forward formula simply implements this function, using the input cells as inputs to the function. The Reverse formula is more complicated, as one of the human inputs (gear ratio) is not known, and we want to find the value that minimizes the solution (time to go that distance). Solving the function arithmetically for the gear ratio is nigh impossible, so I used the Golden-Section Search method to find the answer numerically. Since this method requires a search interval and I didn’t want to limit the formula to a certain maximum gear ratio, the optimization has two steps. First it steadily increases the gear ratio, evaluating the time for each ratio until it passed the minimum point (i.e. the time begins to grow). It then runs the Golden-Section Search algorithm for a number of iterations using the newly found bounds to find a more accurate estimation of the extremum point. That point, where the time is at a minimum, is the optimal gear ratio for that set of inputs.
Unfortunately, since the actual implementation of the TimeToDist() function is done in VBA, the Excel sheet does not have access to the simulation data, so it cannot draw graphs of different values vs time as some other programs can do. That is one thing I will be working to add to the next release.
Projectile Trajectory Calculator
This calculator was meant to help find a starting point for prototyping, using very basic physics to get an estimate of the initial velocity and angle needed for the projectile to land at a certain point in space with a certain angle of the final velocity. The formula was derived algebraically from five basic physics formulas:
(1) d = vcos(θ) * t
(2) h = dtan(θ) - (gd^2) / (2(vcos(θ))^2)
(3) vfx = vcos(θ)
(4) vfy = vsin(θ) - gt
(5) tan(α) = vfy / vfx
where θ = initial angle, α = final angle, d = x displacement, h = y displacement, v = initial velocity, g = gravity, t = time to finish, vfy = y component of final velocity, and vfx = x component of final velocity
Some algebra and trig boil those down to two final formulas:
(6) v = sec(θ) * sqrt(gd / (tan(θ) - tan(α)))
(7) 2h/d = tan(θ) + tan(α)
Derivation
[spoiler]Substitute vfx and vfy from (3) and (4) into (5):
(8) tan(α) = (vsin(θ) - gt) / v*cos(θ)
Split the right side of (8) into two separate fractions and simplify:
(9) tan(α) = tan(θ) - gd / (vcos(θ))^2
Subtract tan(θ) from both sides of (9):
(10) tan(α) - tan(θ) = - gd / (vcos(θ))^2
Multiply both sides of (10) by -(vcos(θ))^2 / (tan(α) - tan(θ)):
(11) (vcos(θ))^2 = g*d / (tan(θ)-tan(α))
Square root both sides of (11), then multiply both sides by sec(θ) to get (6):
(6) v = sec(θ) * sqrt(g*d / (tan(θ)-tan(α))
Substitute (vcos(θ))^2 from (11) into (2):
(12) h = dtan(θ) - (gd^2)/(2g*d / (tan(θ)-tan(α)))
Simplify (12):
(13) h = d/2 * (tan(θ) + tan(α))
Divide both sides of (13) by d/2 to get (7):
(7) 2*h/d = tan(θ) + tan(α)
[/spoiler]
The Forward formula solves (7) for α, and uses that and (2) to calculate the point and angle that the projectile will end at given the starting conditions. The Reverse formula solves (7) for θ, and uses that and (6) to calculate the required initial conditions for the projectile to end up at a certain point with a certain angle.
The Reverse formula should be useful for designing shooters to fit the game strategy that you choose (i.e. where are you shooting from and what’s the optimal angle for the object to hit at). The Forward formula can then help show what will happen as those values vary (e.g. where does the object land if you move forward/backward from the goal). Again, this formula does not account for air resistance, spin, or a host of other factors, so you must prototype and iterate the shooter to make sure it will work.
