stabiliser accuracy

[bibra]

i have a dataset which can be converted into a graph
how do i get an overlapping graph showing the average of the highs and lows

1 17.02
2 16.42
3 16.54
4 16.11
5 17.08
6 17.3
7 17.24
8 -8.86
9 -10.64
10 -8.34
11 -4.56
12 -0.86
13 0.02
14 2.16
15 3.78
16 4.28
17 1.3
18 1.3
19 0.9
20 4.3
21 4.7
22 10.5
23 5.86
24 3.96
25 5.4
26 6.44
27 7.32
28 8.78
29 9.48
30 10.98
31 20.32
32 21.86
33 22.42
34 23.34
35 24.74
36 24.96
37 21.9
38 19.44
39 19
40 19.24
41 16.86
42 14.62
43 15.64
44 13.64
45 11.3
46 0.44
47 8.52
48 3.64
49 2.32
50 1.18
51 3.24
52 6.2
53 2.54
54 4.22
55 2.94
56 5.96
57 7.28
58 9.75
59 9.22
60 9.78
61 41.7
62 15.4
63 16.78
64 18.62
65 18.6
66 15.72
67 16.28
68 18.44
69 13.4
70 10.56
71 12.58
72 12.68
73 15.32
74 18.9
75 19.48
76 21.66
77 25.25
78 24.06
79 17.8
80 18.22
81 18.76
82 16.32
83 16.44
84 20.48
85 22.1
86 19.64
87 23.14
88 25.42
89 21.58
90 22.94
91 20.96
92 17.12
93 13.74
94 10.5
95 11.44
96 10.08
97 11.1
98 8.84

 

[thisoldman]

i have a dataset which can be converted into a graph
how do i get an overlapping graph showing the average of the highs and lows

How do you define "average of the highs and lows"? How is this average calculated?

 

[bibra]

How do you define "average of the highs and lows"? How is this average calculated?

average is where the peaks go from max--h1 to min--h2
h1+h2/2=b1

now b1 b2 b3.................... would form the average and would be plotted over the original chart

 

[thisoldman]

If I have this:

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl65, align: center"]A
[/TD]
[TD="class: xl65, width: 64, align: center"]B[/TD]

[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]17.02[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]16.11[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64, align: right"]17.24[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64, align: right"]-8.86[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]-10.64[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64, align: right"]-0.86[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64, align: right"]0.02[/TD]
[TD="class: xl64"][/TD]

</tbody>

What values should appear in column B?

 

[bibra]

If I have this:

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl65, align: center"]A
[/TD]
[TD="class: xl65, width: 64, align: center"]B[/TD]

[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]17.02[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]16.11[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64, align: right"]17.24[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64, align: right"]-8.86[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]-10.64[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64, align: right"]-0.86[/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64, align: right"]0.02[/TD]
[TD="class: xl64"][/TD]

</tbody>

What values should appear in column B?

thanx for the quick response
this is important to me

column B should be able to select the max in A =17.24 and then the low in A =-10.64
then the mean =17.24+10.64/2 =14.14 would be the point to be plotted
the next point would be the average of 10.64+.86/2 =? would be the next plot
next plot .86+4.28/2
and so on
since there are a 1000 points to be averaged manual would be very tedious

the idea being to draw a mean line of all these points and overlay it on the original graph

 

[thisoldman]

column B should be able to select the max in A =17.24 and then the low in A =-10.64
then the mean =17.24+10.64/2 =14.14 would be the point to be plotted
the next point would be the average of 10.64+.86/2 =? would be the next plot
next plot .86+4.28/2
and so on

the idea being to draw a mean line of all these points and overlay it on the original graph

What is it that you are really trying to show with your chart? Do you want to compare the data you collected to some standard measure?

I am still confused by your definition of the "average of the highs and lows":
=17.24 + 10.64 / 2 equals 22.95.
=(17.24 + 10.64) / 2 equals 13.94

But the average of the highest and lowest values is:
=(17.24 + (-10.64)) / 2 equals 3.3

Do you mean the absolute values of the largest and smallest data points?

For your second point you take the average of the absolute values of the smallest point and the second smallest point! And I have no idea where the 4.28 value originated for the third point's calculation.

 

[bibra]

What is it that you are really trying to show with your chart? Do you want to compare the data you collected to some standard measure?

I am still confused by your definition of the "average of the highs and lows":
=17.24 + 10.64 / 2 equals 22.95.
=(17.24 + 10.64) / 2 equals 13.94

But the average of the highest and lowest values is:
=(17.24 + (-10.64)) / 2 equals 3.3

Do you mean the absolute values of the largest and smallest data points?

For your second point you take the average of the absolute values of the smallest point and the second smallest point! And I have no idea where the 4.28 value originated for the third point's calculation.

the idea is to measure the judder in a stabiliser system with the given dataset
the judder from the axis is to be measured in mils and should not exceed 0.8 mils
any idea is welcome
thanks

 

[thisoldman]

Perhaps this combination stacked area and xy-chart?

The blue line is the data from your first post. I centered the colored band on the mean of the data values. The band height shown here is 3.2 mils. You can change either of these settings by changing the two yellow-filled cells.

The chart was constructed using the method given by Jon Peltier: https://peltiertech.com/excel-charts-with-horizontal-bands/

My workbook can be downloaded from https://www.dropbox.com/s/3hipuysizmhirmp/jitter_stabilization_chart.xlsx?dl=0

 

[bibra]

Thanks for the response
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/hj7xsvitfsc0g34/procedure.bmp?dl=0

https://www.dropbox.com/s/00idfse06nuyx3z/hand drawn chart with 2 mean lines.png?dl=0

 

[bibra]

Thanks for the response
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/hj7xsvitfsc0g34/procedure.bmp?dl=0

https://www.dropbox.com/s/00idfse06nuyx3z/hand drawn chart with 2 mean lines.png?dl=0

anything