# Modeling oscilations in Excel

1. Feb 9, 2013

### _Bd_

1. The problem statement, all variables and given/known data

You have a car going over a speed bump, you need to model the oscillation of the car using excel only (no matlab or other software)

Simple Schematics:

|Mass of car|
| |
Spring Damper
| |
\ /
| Wheel | _____________/ Speed bump \________________

Car speed: 88 inches/s
Car mass: 700 lb (need to divide over G=386 in/s^2
Bump Max height (Hb) = 5
Bump lenght (Lb) =36
Bump Equation (y_wheel)= (Hb/2)*(1-COS(2*PI*X/Lb))
This is the vertical displacement of the wheel as it goes over the bump, the derivative (velocity) is then:
y'_wheel= (Hb/2)*(2PI*V'/Lb)SIN(2*PI*X/Lb)
damping frequency (w_d) = We can assume and play with this
damping ratio (zeta) = We can assume and play with this, values between 0 and .9

2. Relevant equations

Using force analysis ( on each element) and some solving here and there

Using Laplace transforms and my notes I came up with some equations, I wont list those processes since I verified with the professor that they are correct, but here are the results:

Assuming a zeta of .5 and a frequency of .825 Hz
(natural frequency) w_n=w_d/(1-zeta^2)^.5
(spring constant) Ks= w_n^2*m
(ave. damping constant) Kd= 2*zeta*(K_s*m)^.5
NOTE: Kd will be divided into K_up and K_down which average to K_d and generally Kup < Kdown

3. The attempt at a solution
I've made my excel table, my problem is that I keep getting a weird wave:

Fs = force of spring
Fd= force of damper
y_w = vertical displacement of wheel
y_c = vertical displacement of car (y' is velocity and y'' is acceleration)

Time | Displacement | Y_wheel | Fs | Fd | K(up or down?) |Y''_c | Y'_c | Y_c

Fs=Ks(y_w - y_c)
Fd=Kd(y'_w - y'_c)
Y''_c= Fs+Fd/m
Y'_ci= Y'_c(i-1)+Y''*dt
Y_ci= Y_c(i-1)+Y'*dt
http://imageshack.us/a/img850/4871/tablegf.png [Broken]

I get a really funky graph that initially looks good but then doesnt seem to "converge" towards the center like all the graphs in the internet.
I've searched all over and many webpages provide excel codes to do this, (so you can just input your variables and let it solve it by itself) but I want to code it myself (learn how to)

http://imageshack.us/a/img838/6452/graphlj.png [Broken]

If I extend more time it keeps going down and down and down

and it should look similar to this

Last edited by a moderator: May 6, 2017
2. Feb 10, 2013

### Staff: Mentor

Imperial units :/

What happens if you set the damping to 0?
I think the signs of the damping are wrong in some way - it is dampening in the upwards direction, but increasing the energy in the downwards direction.

3. Feb 10, 2013

### Astronuc

Staff Emeritus
One appears to have an equation of a form: ( b - ax ) sin (ωx) rather than a damped sine wave.

The cited image shows the behavior obtained from the convolution of a step function and function formed by the sum of a constant and damped sine wave.

I think though one wants a damped sine wave where the amplitude corresponds to the displacement from its equilibrium.

Last edited by a moderator: May 6, 2017
4. Feb 13, 2013

### rude man

I assume that when you wrote Y''_c= Fs+Fd/m you actually implemented
Y''_c= (Fs+Fd)/m.

The equations look right. Of course I can't comment on how you implemented y_w(t) in Laplace.

I don't understand the distinction between ks_up and ks_down. Seems to me the force on the car is always ks(y_w - y_c) where ks is the one and only spring constant. That assumes y = y_c - y_w = 0 before hitting the bump. The fact that the spring is compressed at that point does not change the equation.

Finally, if all else looks right, have you tried different values for dt? Like 10dt or dt/10? Finite-difference equations are strange critters!

You also know I'm sure that there are many levels of transforming a differential equation into a finite-difference one. Some of those turnkey simulations you mention may well have used a higher-order Runge-Kutta or other equation.