1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Homework Help: Modeling oscilations in Excel

  1. Feb 9, 2013 #1
    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
    (damping frequency) w_d= 5.184 radians
    (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
    http://ese.wustl.edu/ContentFiles/Research/UndergraduateResearch/CompletedProjects/WebPages/2006/as12/identify%20single_files/image017.gif [Broken]
    Last edited by a moderator: May 6, 2017
  2. jcsd
  3. Feb 10, 2013 #2


    User Avatar
    2017 Award

    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.
  4. Feb 10, 2013 #3


    User Avatar
    Staff Emeritus
    Science Advisor

    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
  5. Feb 13, 2013 #4

    rude man

    User Avatar
    Homework Helper
    Gold Member

    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.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook