INTERMIDIATE VALUE OF A CURVE (PRECISION 1 MICRON)

  • Thread starter HARSHAWARDHAN. S .SHASTRI
  • Start date
H

HARSHAWARDHAN. S .SHASTRI

I AM WORKING ON CAM DATA.WHAT I WANT IS TO FIND INTERMIDIATE VALUE OF A
CURVE.
FOLLOWING IS MY DATA ,WHAT I NEED IS TO FINF THE VALUE SAY AT 11.3 DEG.

DEG LIFT
0.0 9.0000
1.0 9.0000
2.0 9.0000
3.0 9.0000
4.0 9.0000
5.0 9.0000
6.0 9.0000
7.0 9.0000
8.0 8.9874
9.0 8.9539
10.0 8.8972
11.0 8.8154
12.0 8.7089
13.0 8.5770
14.0 8.4185
15.0 8.2318
16.0 8.0151
17.0 7.7655
18.0 7.4795
19.0 7.1500
20.0 6.7883
21.0 6.4133
22.0 6.0454
23.0 5.6961
24.0 5.3647
25.0 5.0498
26.0 4.7507
27.0 4.4664
28.0 4.196
29.0 3.9388
30.0 3.6941
31.0 3.4613
32.0 3.2396
33.0 3.0286
34.0 2.8279
35.0 2.6368
36.0 2.4549
37.0 2.2819
38.0 2.1173
39.0 1.9609
40.0 1.8122
41.0 1.671
42.0 1.5366
43.0 1.4099
44.0 1.2895
45.0 1.1755
46.0 1.0678
47.0 0.9661
48.0 0.8704
49.0 0.7802
50.0 0.6957
51.0 0.6165
52.0 0.5426
53.0 0.4738
54.0 0.4099
55.0 0.3511
56.0 0.2971
57.0 0.2477
58.0 0.2032
59.0 0.163
60.0 0.1274
61.0 0.0963
62.0 0.0697
63.0 0.0473
64.0 0.0294
65.0 0.0156
66.0 0.0063
67.0 0.0011
 
B

Bernard Liengme

Try using the FORECAST function
With you data in A1:B69, I used
=FORECAST(11.3,B12:B14,A12:A14)
giving me a result of 8.78
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"HARSHAWARDHAN. S .SHASTRI"
 
M

Mike H

Hi,

It much depends on what happens between 11 & 12 degrees this assumes the
change is linear

=((LOOKUP(C2,A2:B69)-LOOKUP(CEILING(C2,1),A2:B69))/10*MOD(C2,1)*10)+LOOKUP(C2,A2:B69)

with 11.3 in C2 and your table in columns A&B

Mike
 
H

HARSHAWARDHAN. S .SHASTRI

Thanks Mike and Bernard for quick response.

iIhave tried both formula's on my data but i am getting diff up to 110
microns.

Any other logic / formula. ?

H S Shastri

=========================================================
 
M

Mike H

Hi,

Now I've correct my (long winded) formula I get the same answer as Bernard,
but next time I'll use Forecast :)

8.78345

=(LOOKUP(C2,A2:B69))-(((LOOKUP(C2,A2:B69))-LOOKUP(CEILING(C2,1),A2:B69))/10)*(MOD(C2,1)*10)

Mike
 
M

Mike H

This is now bugging me. The interval between (11) 8.8154 & (12) 8.7089 =
-0.01065 and can be shown thus:-

11.00 8.81540
11.10 8.80475
11.20 8.79410
11.30 8.78345
11.40 8.77280
11.50 8.76215
11.60 8.75150
11.70 8.74085
11.80 8.73020
11.90 8.71955
12.00 8.70890

So why doesn't the correct answer for 11.3 = 8.78345 or does the curve
demonstrate some strange profile between these 2 values that we haven't been
made aware of.

Mike
 
R

Rick Rothstein

I think part of the problem is that you are assuming a linear relationship
between points when the curve for the posted data looks sort of like the
right half of a "bell curve" (shape-wise, sort of an elongated backward
"S"). Since the OP wants 1 micron "precision" (6th decimal place for data to
5 decimal places?), I'm guessing the curving part of the curve is not close
enough to a straight line to give the precision the OP is looking for.
 
M

Mike H

Rick,

I think your probably correct but I did give the OP the opportunity to
clarify that in my first post
It much depends on what happens between 11 & 12 degrees this assumes the
change is linear
Click to expand...

and got no such clarification/correction

Mike
 
H

Herbert Seidenberg

The data fits a Gompertz curve with this formula fairly well:
=ka*EXP(-EXP(kb-kc*xdata))+kd
where
ka -9.21E+00
kb 2.17E+00
kc 9.25E-02
kd 9.11E+00
The sum of the squared absolute error = .764
which is not good enough for the precision required.
If we consider only the 7 data points around 11 and
apply a second order polynomial, the results might be good enough:
=TREND(ydata,xdata^{1,2},11.3^{1,2})
=8.78598
 
M

MartinW

Hi Harshawardhan,

I did a trial with 2 polynomial trends, one from 7 Deg to 21 Deg,
the other from 21 Deg to 67 Deg.

I had to go right up to a 6th order polynomial to improve the accuracy
of the curve fit. I don't think it is quite as tight as you are looking for
but
it is very close, plug some different values into the 'new X' cell and you
will
see what I mean.

Actually, I thought that I should have gotten a better fit so maybe someone
else can take a look and see if I have got a small error in there somewhere.

You can download a copy here
http://www.savefile.com/files/1833930
It's in XL2000 format.

HTH
Martin

"HARSHAWARDHAN. S .SHASTRI"
 
M

MartinW

Hi again,

I just used Autocad to calculate the true value.

At 11.3 Deg your Y value will be 8.785991
My spreadsheet calcs this value to be 8.786205
A difference of 0.000214

Can you live with that variation?

I am confident that the Autocad derived result is correct,
however it is not a straightforward procedure that can be
used as a generic solution, it is just a one off type of thing.

HTH
Martin
 
H

HARSHAWARDHAN. S .SHASTRI

Thank u Mike ,

You have taken many efforts and really that works.The formula which you
have modified working very well and gives me precision up to 6 microns which
is acceptable to be.
Thanks Once again
H S Shastri

===========================================================
 
M

MartinW

Hi,

I seem to be talking to myself down here but I will continue as
I think it is important.

As your data is to 4 decimal places and you are talking microns
I am assuming that your lift column is in centimetres making
0.0001 equal to 1 micron.

I have proved to myself, with the use of Autocad, that the true
Y value at X=11.3 is 8.7860 (rounded to 4 decimals)
My Spreadsheet returns 8.7862 (out by 2 microns)
The straight line value of 8.7835 that you say is only out by 6 microns
is, in fact, out by 25 microns.

Of course, I may be stumbling with some false assumptions here, as
you haven't provided a great deal of detail, but it appears to
me that your calculations may be flawed.

HTH
Martin
 
Top