newtonian mechanics - Calculation of Distance from measured Acceleration vs Time

I have an Accelerometer connected to a device that feeds the instant values of the acceleration in the 3 directions. I've tried to calculate the distance for a vertical movement using these values with excel (applying two times an integration), but somehow it doesn't seem to work properly.

How would be a good way to calculate the traveled distance from the measured acceleration using excel tables?

Answer

Starting at ${position}_z$ = $z$ = 0 and $v(z) = 0$ and by tracking multiple acceleration values either with a time interval or at fixed intervals, $t$, then you can get the position.... somewhat. It will drift over time. Also, your device cannot rotate whatsoever, or else you need a gyroscope to track that and then use trigonometry to properly orient the x y and z values from the accelerometer. Assuming it's always oriented such that the $a(z)$ is always perfect vertical acceleration (if you're in a vehicle that's always flat, in which case z doesn't matter, or you're on a vertical guide rail),

$$p(z) = \int_0^t v(z) ~dt = \iint_0^t a(z) ~dt $$

Also, from here:

Short answer: Forget about it.

Longer answer: Unless you're on a perfectly straight rail, you will not achieve what you want to do without (a) a set of gyros; and (b) Far more accurate sensors than what you have.

Accelerometers measure acceleration in the body fixed reference frame, whereas you need some displacement in an earth-fixed frame.

Therefore, you need not only to integrate the accelerometers, but rotate them into the earth-fixed frame before doing the integration.

This is assuming perfect sensors. MEMS sensors are far from perfect - I have written up a post on some of the errors here.

Consider two errors: 1. A bias on the accelerometer. 2. An initial attitude (tilt) error.

In addition to whatever acceleration signal there is, integrate a bias and you get a ramp error with time. Integrate the ramp and you get a quadratically increasing error with time. This will add up really, really quickly.

Consider a tilt error. You'll now be measuring some of the gravity vector in the forward (or whatever) direction. Integrate this error twice and you'll have the same problem as the bias.

So, my advice again is DON'T! Find another method.

Also, check this book out for more detailed designs, or use whatever sensors and algorithm these guys are on:

http://www.youtube.com/watch?v=6ijArKE8vKU

If you still want to give this a shot, use the Trapezoidal method in Excel, it's pretty easy. There's an explanation page here with a sample, but here's a more complete way:

1  Time [A]  Acceleration [B]   Velocity [C]                Distance [D]
2  0         a(z)               0                           0
3  1         a(z)               =C2+(A3-A2)*(B2+B3)/2       =D2+(B3-B2)*(C2+C3)/2
4  2         a(z)               =C3+(A4-A3)*(B3+B4)/2       =D3+(B4-B3)*(C3+C4)/2
5  3         a(z)               =C4+(A5-A4)*(B4+B5)/2       =D4+(B5-B4)*(C4+C5)/2
6  4         a(z)               =C5+(A6-A5)*(B5+B6)/2       =D5+(B6-B5)*(C5+C6)/2
7  5         a(z)               =C6+(A7-A6)*(B6+B7)/2       =D6+(B7-B6)*(C6+C7)/2
8  6         a(z)               =C7+(A8-A7)*(B7+B8)/2       =D7+(B8-B7)*(C7+C8)/2

9  7         a(z)               =C8+(A9-A8)*(B8+B9)/2       =D8+(B9-B8)*(C8+C9)/2
10 8         a(z)               =C9+(A10-A9)*(B9+B10)/2     =D9+(B10-B9)*(C9+C10)/2
11 9         a(z)               =C10+(A11-A10)*(B10+B11)/2  =D10+(B11-B10)*(C10+C11)/2
12 10        a(z)               =C11+(A12-A11)*(B11+B12)/2  =D11+(B12-B11)*(C11+C12)/2
   ...       ...                ...                         ...