FIRST 2012 Ball Trajectory Spreadsheet
engunneer | January 9, 2012 11:07 pmIt’s the time of year for FIRST again, and this year’s game (Rebound Rumble) is looking like it’s going to be pretty exciting. This year, I’m helping Team 2151, the Monty Pythons.
I made a spreadsheet to help with planning a shooting mechanism, and to help with testing or writing code.
The spreadsheet is broken up into different sections. The first section has the basic parameters to calculate what velocity is needed for a given set of shot parameters. Set the cells that are green to your values, and the first orange boxes will show the velocity needed for each of the three hoop levels. If the angle of the ball entering the hoop is too shallow, the box will turn red. The h0 (robot shooter height) parameter is used for all the sections, so make sure you set this accurately before going down to the other sections.
The next section calculates a the velocity needed for a variety of shooting angles for a given range. This does not check to see if the shot is too shallow. The first section can be used to look at any individual shot to see if it passes the shallowness test. There is an inverted chart off to the right that swaps the X and Y axes, which gives a different view of the data. The left chart can be used to find a desired velocity for a given range and angle, where the right chart can help you choose an angle to go with your known velocity.
The next section is useful for robots with a fixed shooting angle. It calculates the velocity needed to make baskets at a variety of ranges. There is an inverted chart off to the right that swaps the X and Y axes, which gives a different view of the data. The left chart can be used to find a desired velocity, while the right chart can be used to see what range you’d get out of your current velocity.
The last section is only in the macro version of the spreadsheet. It is designed for robots with a fixed firing velocity (such as a pneumatic launcher). It calculates (using an iteration macro) the needed angle of launch for a fixed velocity and a variety of ranges.
The most useful part of this is for estimating a formula to use in the robot code. The math itself is non-trivial to rearrange.
Hopefully this spreadsheet is useful to other teams, so I am posting them here. shot simulation (macro)
Please let me know what you think below
UPDATE (10AM): I added some trendlines to the last graph in the macro version. Now you can specify what range to look at and it will give you a cubic formula for a trendline for each hoop. The trendline will get messed up if you don’t have enough velocity to make the range (blank results in the orange boxes). if you ever get an error message (if the macro fails to find a result), you may have to go to sheet 3 and change cells in the B10 to D22 range to 89 to get it working again. Basically, if the GoalSeek Function can’t find a result, it can make an error pop up. I try to prevent the most common errors, but asking for longer ranges is more likely to have issues.
Be sure to check results back up in the first section to make sure the angles are still valid. The first section can also be used for checking the human player parameters needed to make a shot. set the range to 54 or 56 feet, set the angle high enough to clear the 8 foot alliance wall, and then see what sorts of velocities are needed to make the shot.
UPDATE (10:10AM): I fixed a bug with the entry angle lookup code.
UPDATE (10:40AM): I added the height of the apex of the curve to section 1.
5 Responses to “FIRST 2012 Ball Trajectory Spreadsheet”
[...] into account the nonlinearity of the air drag. Click on the table below to expand it. Here is a link to Branden Gunn 2151 nice Excel trajectory [...]
I just want to say THANKS for this! Found it from Chief Delphi, but really REALLY thanks!!!!!!!
This is awesome. Thanks! I’ve been tinkering with converting this to MathCAD to see explore this PTC product. You can see how it looks here http://communities.ptc.com/thread/36840 (Free Mathcad download available for FIRST teams here http://www.ptc.com/company/community/first/index.htm#)
[...] http://engunneer.com/content/blog/first-2012-ball-trajectory-spreadsheet [...]
Great stuff. I am curious, in the last section (macro file) you outline a angle that will succeed based on a fixed velocity and variety of ranges. As you know there are up to 2 angles that will work. You seem to be solving for the larger of the two angles. Any way you could create a version that solves for the smaller of the two? Many thanks.
Care to comment?