Ideal Ball Trajectory Spreadsheet

I wanted to share a spreadsheet I made with CD. It helps with calculations for the ideal projectile in a variety of circumstances. Most importantly, it helps with the case of a fixed-velocity varying-angle shooter, as well as other cases. Please see my blog post about it: link. The file is about 12KB too large to post directly here.

It doesn’t take the drag into account, but will give an order of magnitude for different situations. It also tells you how much variation in your velocity or angle there would be between each of the hoops, so there is an indication of how much tolerance there is on the velocity or angle.

The formula for predicting the angle of a shot given a range and an initial velocity is non-trivial when you add in the height of the shooter and the height of the basket, so I have a macro doing iterative loop to calculate those values.

Wow, this spreadsheet looks very nice!

Just one question, how do you feel we can implement drag into the formula?

Great work, and thank you for this tool!

It looks nice, but entry angle prediction doesnt seem to work properly for long-distance shots.

I noticed that, and fixed it in the update 10 min later. I’m updating another version now that adds the height of the apex of the curve. It should be up in a few minutes. (EDIT: It’s up now)

My physics-fu is not that strong. If there is a good reference I can follow (and if someone else measures things like the reynolds number, etc.), I can try to add it. It depends on how bad the formulas are.

To find the drag you really need to know the surface roughness of the game balls. The reynolds number is VD/v where V is the velocity, D is the diameter of our game balls and v is the kinematic velocity of air at room temperature.

v = 1.12*10^-4 ft^2/s

I estimate the Reynolds number to be 7.4 * 10^5 at 40 ft/s with the 25 inch diameter. It won’t change too much from this number no matter the speed.

NASA has some nice curves that could be helpful for estimates, but this may be the best that will be found unless someone does tests or runs simulations in CFD software.

The “Rough” curve is obviously what would be used.

Oh and this sheet is really well put together.

I was working on a Matlab code, but will probably just use this instead.

(I haven’t had the time to work on it. If only my real job could be designing this robot.)

Sorry for kinda spamming CD with this post, but I calculated the drag here:

I’ve just been pointing everyone there who has a question about drag. It turns out, it’s pretty non-negligible as well as fairly difficult to model with a program. Your best bet is going to be to use a look-up table.

Of course, finding the drag and implementing it into trajectory calculations are entirely different beasts. I’ve been working on it on-and-off for a couple days, but really understanding it requires calculus I’m not learning until later this semester (Multivariable). I’m just trying to get as far as I can. If you’re interested in pursuing it, there’s academic papers that discuss this sort of problem, I’m sure.

Did I hear someone say something about this problem being at the University level?

I think 2D kinematics will be sufficient, but hey, maybe that’s just trying to build for an ideal world. I’ll post this link in the other thread to make sure that people see it.

Mathematacally this is hard to compute - but you don’t need to.

For the purposes of this game the key issue is repeatability of the shot. So you can calibrate the system to hit the “kill zone” and as long as it is repeatable - who cares about the math (again for the purpose of this game).
However you wil need to place your robot at the same place and orientation every time. Or a set of predefined positions.

Repeatability is tricky though, because you need to place minimization of errors as a key design rule for all you do. We hear for example that the weight of the balls is within a 10% range. When faced with an item that has such an inbuilt error, one needs to design mechanisms that reduce the significance of that error on the outcome.That is indeed what design engeneers do all the time, and there are multiple ways of doing it.

Good discussion,