Best use of Microsoft Excel for Numerical Analysis of Classical Mechanics?

[Farina]

I'm need to use Microsoft Excel as a numerical
analysis tool for classical mechanics physics problems.

Yes, I know there are dozens or hundreds of other
tools that would be more powerful, but I (and my
students) are required to see what they can do
with Excel.

The Class: Analytical Mechanics

Level: Junior/Senior/First-year Graduate

The Question: what kind of problems/solution types
would serve as the best examples to show what can
be done with Excel?

Thank you.

Farina

 

[Integral]

I cannot think of any numeric methods which Excel cannot do. 3d things would be hard to display the results, but with a bit of thought you could do the calculations.

I have found that Excel is designed for business applications, it does not have very good graphing tools for scientific needs. If you want 1d time varying solutions Excel can easily do the job, you can plot the results adequately. essentially creating a "movie" of the time changing solution.

It is not clear to me where you need help?
If your problem is with how to use Excel, this question should be in the software forum.

OR?

Should this be in Math, for help with the Numerical Methods needed to solve the DEs from the Physics?

Please clarify.

 

[NeutronStar]

Rocket Science

I just took a course in Analytical Mechanics and we used spreadsheets quite often to solve differential equations numerically.

I still have one of those spreadsheets in a folder. I can send it to you if you like. It was a problem were a rocket had only 120 seconds of fuel to burn which wasn't enough to reach escape velocity. The spreadsheet could be used to calculate how high the rocket would reach before running out of fuel, and how long it would take to hit the ground from the time it had been launched. The problem takes into consideration air resistance. (But I think that the air density was considered to be constant which wouldn’t be realistic)

We were basically given the following formulae in lecture:

$$F(y,v,t)=F_o-c_Dv|v|-G\frac{M_RM_E}{\left(R_E+y\right)^2}; 0\leq t \leq 120 s$$

$$F(y,v,t)=0-c_Dv|v|-G\frac{M_RM_E}{\left(R_E+y\right)^2}; t > 120 s$$

$$\Delta v=\frac{F(y,v,t)}{M_R}\Delta t$$

$$\Delta y=v\Delta t+\frac{1}{2}\frac{F(y,v,t)}{M_R}\Delta t^2$$

Then we had to create the spreadsheet to solve these conditions.

Like I say, I have the raw spreadsheet if you want a copy I can email you one. Once you set up the first row of dependent equations and choose values for the deltas then you just fill the columns down and Excel does the rest. The smaller deltas you chose the more you have to fill down, but the more accurate your answer will be.

By the way, we didn't bother to graph any of this stuff, we were just concerned with getting the answers to specific questions. But I suppose that we could have graphed some stuff too. Like the maxium height and maximum flight time in this example.

 

[Farina]

Very interesting!

Yes, I would appreciate a copy. My email address is:

cjwood99@hotmail.com

Thanks again.

I just took a course in Analytical Mechanics and we used spreadsheets quite often to solve differential equations numerically.

I still have one of those spreadsheets in a folder. I can send it to you if you like. It was a problem were a rocket had only 120 seconds of fuel to burn which wasn't enough to reach escape velocity. The spreadsheet could be used to calculate how high the rocket would reach before running out of fuel, and how long it would take to hit the ground from the time it had been launched. The problem takes into consideration air resistance. (But I think that the air density was considered to be constant which wouldn’t be realistic)

We were basically given the following formulae in lecture:

$$F(y,v,t)=F_o-c_Dv|v|-G\frac{M_RM_E}{\left(R_E+y\right)^2}; 0\leq t \leq 120 s$$

$$F(y,v,t)=0-c_Dv|v|-G\frac{M_RM_E}{\left(R_E+y\right)^2}; t > 120 s$$

$$\Delta v=\frac{F(y,v,t)}{M_R}\Delta t$$

$$\Delta y=v\Delta t\frac{1}{2}\frac{F(y,v,t)}{M_R}\Delta t^2$$

Then we had to create the spreadsheet to solve these conditions.

Like I say, I have the raw spreadsheet if you want a copy I can email you one. Once you set up the first row of dependent equations and choose values for the deltas then you just fill the columns down and Excel does the rest. The smaller deltas you chose the more you have to fill down, but the more accurate your answer will be.

By the way, we didn't bother to graph any of this stuff, we were just concerned with getting the answers to specific questions. But I suppose that we could have graphed some stuff too. Like the maxium height and maximum flight time in this example.

 

[Farina]

Right -- I guess I would ideally find classical mechanics
problems that ONLY had an analytical solution, then
actually show how Excel could be used to crank-out
a solution.

I'm proficient with Excel; not so proficient with
Numerical Methods. For now, examples that fit
this description, or advice on what kind of classical
mechanics problem types lend themselves to this
purpose would be great.

Another tact: identify a classical mechanics problem
that has an analytical solution, then modify the
same problem so it only has a numerical solution.

Thanks.

Farina

I cannot think of any numeric methods which Excel cannot do. 3d things would be hard to display the results, but with a bit of thought you could do the calculations.

I have found that Excel is designed for business applications, it does not have very good graphing tools for scientific needs. If you want 1d time varying solutions Excel can easily do the job, you can plot the results adequately. essentially creating a "movie" of the time changing solution.

It is not clear to me where you need help?
If your problem is with how to use Excel, this question should be in the software forum.

OR?

Should this be in Math, for help with the Numerical Methods needed to solve the DEs from the Physics?

Please clarify.

 

[rayjohn01]

Well what can I say -- you can't make a silk purse out of a sow's ear.
Excell to my mind is the worst form to solve such problems -- but then I know a lot of people who like it --- but why teach this as a method compared to better I do not understand -- you say you know there are many other solutions -- well WHY NOT THEM -- I mean what is the point.
The very concept of putting something into a pigeon hole is a total business concept -- to apply it to mathematical analysis and graphing is laughable ---
a square peg in a round hole --- each to their own but each solution to the problem.
ray.