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 BIBRANew 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. ### HuiExcel NinjaStaff Member

Messages:
11,726
You may want to illustrate the problem with some pictures?

Messages:
18

File size:
60.7 KB
Views:
11
4. ### JAMINDER BIBRANew 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. ### HuiExcel NinjaStaff Member

Messages:
11,726
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 BIBRANew Member

Messages:
18
how do you deal with charts which are derivatives of first and second order
are there any tutorials available please
7. ### HuiExcel NinjaStaff Member

Messages:
11,726
Send some data and I’ll show you
8. ### JAMINDER BIBRANew 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

File size:
36.4 KB
Views:
4
9. ### HuiExcel NinjaStaff Member

Messages:
11,726
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

You can repeat the Y Chart for the Z data

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

File size:
6 KB
Views:
6
10. ### JAMINDER BIBRANew Member

Messages:
18
can we proceed step by step

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 BIBRANew 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. ### HuiExcel NinjaStaff Member

Messages:
11,726

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

#### Attached Files:

File size:
14.4 KB
Views:
4
Dillina likes this.
13. ### HuiExcel NinjaStaff Member

Messages:
11,726

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

Full chart
Dillina likes this.
14. ### JAMINDER BIBRANew 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. ### HuiExcel NinjaStaff Member

Messages:
11,726
Jaminder

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:

• ###### stabiliser accuraacy.xlsx
File size:
67 KB
Views:
4
Dillina likes this.

Messages:
18
17. ### HuiExcel NinjaStaff Member

Messages:
11,726
I only associate with family and direct friends on Facebook

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

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 BIBRANew 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

thank you
19. ### HuiExcel NinjaStaff Member

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

Second point is personal preference
20. ### JAMINDER BIBRANew 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. ### HuiExcel NinjaStaff Member

Messages:
11,726
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 BIBRANew 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