Paper: AMB Design Spreadsheet

Thread created automatically to discuss a document in CD-Media.

AMB Design Spreadsheet v2
by: AriMB

A set of spreadsheet calculators useful for designing FRC robots.

This is the second version of my design calculator: a set of spreadsheet calculators I have designed/compiled for my team to use designing our robot.

Changes since v1.2:

  • Added Sprint Distance Calculator, which can calculate an optimized drivetrain gear ratio for moving a certain distance. Also runs the forward calculation where you know the distance and gear ratio and want to find the time to cover that distance.
  • Added Projectile Motion Calculator, which can calculate the required launch velocity and angle needed to shoot a projectile a given height and distance, landing at a given angle. Also runs the forward calculation where you know the launch velocity and angle and want to find the final values.
  • Added Stall Voltage as a calculation option for the Mechanism Ratio sheet. This allows you to see what voltage needs to be applied to exactly balance out the load (e.g. to hold an elevator/arm in place). You can use this to make sure your motors don’t burn up when holding position.
  • Removed pneumatic actuation time calculation because it was unreliable and required too many estimate values.
  • Fixed a few minor bugs in formulas
  • Minor UI updates


  • Drivetrain Calculator
  • Sprint Distance Calculator
  • Mechanism Gear Ratio Calculator
  • Projectile Trajectory Calclulator
  • Chain/Belt C-C Calculator
  • Gear Size Calculators
  • Pneumatics Calculator
  • Useful Values

Note: You must enable macros in Excel for the Sprint Distance Calculator to work.

AMB Design Spreadsheet v2.xlsm (107.4 KB)
AMB Design Spreadsheet v2_1.xlsm (134.8 KB)
AMB Design Spreadsheet v2_2.xlsm (132.6 KB)

This version includes some cool new stuff, including an sprint distance drivetrain gear ratio optimizer and reverse projectile motion calculator. Check it out!

I live and die by these spreadsheets, so I find the trajectory very interesting. I played around with linear velocity (speed of the wheel at the edge) but my ratios are about 8:1 for a 775 for a 2017ish shooter by just using free speed and wheel radius. What is a ratio that some teams used or am I way off? Thanks for the amazing spreadsheet.

Most people went faster than that, most people seemed to go with something closer to 2:1 as I could see. That being said, I can’t comment since i really Son the have enough information - wheel diameter and compression on the balls is also unknown. As far as those two go- it seems a lot of people did 4” or lower in diameter for the flywheel and kept the compression pretty low (less than 1/4”). What some teams did is they made their hood elliptical or other fun shapes instead of circular so that the compression start pretty high and then gradually lower until there was essentially no compression by the time the ball left the shooter.

Digression over - I generally recommend that you just overspec the speed and make up for the lower torque by adding more motors (if your weight budget allows) - every shooter I’ve designed or worked on has magically had less range than you wanted at the beginning.

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 = d
tan(θ) - (gd^2) / (2(vcos(θ))^2)
(3) vfx = v
(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) 2
h/d = tan(θ) + tan(α)

[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) (v
cos(θ))^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 = d
tan(θ) - (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(α)
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.

The reverse trajectory formula gives the velocity that the ball should leave the shooter. Since with a flywheel the wheel is generally slipping on the ball, the tangential speed of the wheel will not necessarily equal the speed that the ball leaves the shooter. That is one of the many reasons why calculating the required wheel speed (i.e. reduction) for a flywheel is pretty-much impossible without real-world testing.

I figured this was the case as most design calculators don’t have a flywheel shooter calculator and why my calculations were way off… thanks for the resource.

I just uploaded version 2.1. Here’s what’s new:

• Supports forward sprint distance calculation for shifting drivetrains. The formula assumes the drivetrain shifts instantly, and at the perfect time. I’m pretty sure the formula is working correctly, but sometimes I am a bit doubtful; I’d appreciate if people want to play around with it to test it out.
• Now you can get a position, velocity, and acceleration graph for the sprint distance calculation. This allows you to better visualize how the drivetrain accelerates as it travels.
• Metric boxes now only change the outputs, and leave the inputs in imperial units. This on request of my students, who natively use metric, and said most of the inputs start off in imperial units, so it’s easier for them for the calculator to only convert the outputs to metric.
• Projectile calculator graph now has axis labels.
• A few other random bug fixes

Let me know if you have any questions, comments, or suggestions :smiley:

Version 2.2 has been uploaded, including:

• Fixed the formula for sprint distance calculations*
• Small GUI updates
• A few other miscellaneous things

*I thought that I had copied the code from Ether’s Drivetrain Acceleration Model correctly and verified it, but apparently I made some mistakes. This time, I literally copy-pasted the code and switched syntax from C to VBA. This time, it seems to work better