1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

stabilization accuracy

Discussion in 'Discuss Data Visualizations and Charting' started by JAMINDER BIBRA, Feb 15, 2018.

  1. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    simple graph sine curve with x,y axis
    asymmetrical
    averaging of the highs and lows
    the results give a new chart
    again average the highs and lows
    the result again gives a new chart
    now count the points and feed it into a formula to convert stabilization into mils

    can it be done in excel
    does the process describe a second order equation
    can it be done in another way
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    You may want to illustrate the problem with some pictures?
  3. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    please see attd file

    Attached Files:

  4. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    the high and low points H1, H2..... are averaged to give rise to a new graph B1, B2.................
    the B1, B2 points are again averaged once more to give rise to a new graph C1, C2....................
    can the averaging be done in excel graphs for B and C graphs
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Wouldn't the second chart be the derivative of the first chart?
    Wouldn't the third chart be the derivative of the second chart?

    Do you have a set of data ?
  6. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    absolutely nailed on the head
    how do you deal with charts which are derivatives of first and second order
    are there any tutorials available please
  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Send some data and I’ll show you
  8. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    posted the file
    plotted t versus y
    t versus y'
    t versus y''

    the idea is to find that the judder in y axis is not more than 2 mils

    though the book says to first plot y versus t
    then find mean axis for the plot of y versus t
    then find the mean axis a second time for the plot of y versus t
    then find the amplitude of all peaks from the mean axis
    then find the mean amplitude on each side of the axis
    the mean amplitude in mils should not exceed 2 mils

    similar exercise for z axis
    mean amplitude not to exceed 0.8 mils

    may i request you to kindly advise whether the same can be done by y, y', y''
    or by finding the mean axis

    Attached Files:

  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Your main plot is y, y' and y'' versus t
    t is the x Axis
    y, y' and y'' are all plotted on the Y Axis
    upload_2018-2-17_10-47-57.png

    You can repeat the Y Chart for the Z data
    upload_2018-2-17_10-52-54.png

    For the mean I would add a Linear trend line to the data or simply average the y' and y'' values

    I would clarify with your lecturer/boss what the requirements are

    Attached Files:

  10. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    can we proceed step by step
    thanx for the prompt reply
    upload_2018-2-17_23-12-28.png
    this is the original graph
    now how do i overlay another line on this graph which connects the average of the highs and lows of the original graph
    for example the first high is point 7 =17.24
    which then dips to point 9 = -10.64
    so the average is 17.24+10.64/2 = 14.14
    the next average would be 10.64+4.28/2 = 12.78
    and so on
    the average trend line needs to be generated on the original graph
    how to do it?
  11. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    let me just say what is prescribed in the procedure

    draw the original graph(consists of around 1000 points)
    draw a graph connecting the mean (/2) points
    draw a second mean graph
    now calculate the mils as follows

    from each peak of the original graph drop a vertical intercept onto the second mean line and note the displacement(A)
    then the mils is given as under

    =(A1+A2+A3.......An)/(n*65) =should not be greater than 0.8 mils

    n=number of peaks
    65=constant based on the focal length of camera, magnification etc

    My staff is doing it manually
    It takes them 4 months to do it for one set of 1000 points
    And we have another 4 sets to do(4*1000)

    Moreover this is only in azimuth axis
    After this judder has to be calculated for transverse axis and should not be more than 2 mils
    That means another 4*1000 points

    I am sure Excel can help

    the procedure sheet with the drawing is uploaded

    https://www.dropbox.com/s/hj7xsvitfs...edure.bmp?dl=0

    https://www.dropbox.com/s/00idfse06n...lines.png?dl=0
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    I broadly understand what you are asking except that it isn't supported by your data or comments above

    upload_2018-2-21_8-19-14.png

    Look at the above points
    pt t=2 it is a Low
    pt t=3 it is a Hi
    pt t=4 it is a Low
    pt t=6 it is a Hi, not t=7

    comments please

    Attached Files:

    Dillina likes this.
  13. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    upload_2018-2-21_9-11-29.png

    The Yellow Dots are the average between each Hi/Lo pair, both in the t and y directions

    Please explain next step!

    Full chart
    upload_2018-2-21_9-14-6.png
    Dillina likes this.
  14. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    GENIUS!!!
    wish i could hug you!!
    just what is required

    the following part is now completed::

    • draw the original graph(done in blue with red crosses)
    • draw a graph connecting the mean (/2) points (done with the yellow graph)

    the following part is to be completed::

    • draw a second mean graph(taking the highs and lows of the yellow curve)
    • now calculate the mils as follows
    • from each peak of the original blue graph drop a vertical intercept onto the second mean line and note the displacement(A)
    • then the mils is given as under
    • =(A1+A2+A3.......An)/(n*65) =should not be greater than 0.8 mils
    • n=number of peaks
    • 65=constant based on the focal length of camera, magnification etc
  15. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Jaminder

    upload_2018-2-22_9-1-58.png

    upload_2018-2-22_9-3-47.png

    Please see attached file

    You will see errors in Rows 101:104 these are due to lack of data past the last Y" point
    So I haven't included those in the calculation of A or mils

    There are some seriously complex formulas in here,
    I'd suggest not trying to move them, but to paste other data into the source columns J:L

    The formulas are only setup to go to Row 105, you will have to extend them if there are more than 100 data points

    You may wish to visit here
    .

    Attached Files:

    Dillina likes this.
  16. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
  17. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    I only associate with family and direct friends on Facebook
    I don't use Twitter

    I do Excel consulting, but only on big projects, Financial / Budget models, payroll systems, Inventory systems, Cost Estimation systems etc

    I am on Linkedin

    Donations are entirely up to you

    Please check the math of my solution manually for a few points to ensure you are satisfied.

    .
  18. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    i did get stuck at one place
    why add the row number to the lookup value
    +row()/100000
    how does it help

    also instead of dividing arrays by 1 to convert into (1,0......) instead of (true, false.......)
    i multiplied (--(array)
    could not find any adverse effect
    kindly advise


    thank you
  19. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    +row()/10000 is used to avoid duplicates in the column

    Second point is personal preference
  20. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    where can i read up more on these type of solutions and there applications

    would like to increase my knowledge

    could we apply single differential and double differential (velocity and acceleration) to such problems
  21. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Jaminder,

    It isn't strictly velocity/acceleration,because it is changes between Min's and Max's not between adjacent points, so it is sort of a smoothed velocity/acceleration

    I'm not sure of exactly what this math is called ?

    Where did you get the original idea/concept? Ask them
  22. JAMINDER BIBRA

    JAMINDER BIBRA New Member

    Messages:
    18
    problem pertains to stabilisation process where damping is required to reduce the amplitude judder below 2 mils

    i meant the formulas that you have applied
    which other problems have such solutions
    would like to study more in this direction

Share This Page