How to find the inital formula behind engineering test data ? 2

[FreddyMusic]

Recently, my job seems to work on a mathematic problem.

We did many efforts on engineer test and get a perfect graph curve by discrete data (100 points on EXCEL).
But my customer interest also the inital Formula behind those data. Perhaps it’s a function or calculus.....not clear.

Engineers and physicists distinguish between continuous data and discrete data.

Anyone leads us first step of this cross?
Books, software, method?

 

[IRstuff]

SOMEONE must know what you were measuring. Data does not randomly create itself.

The physical process that you measure should have some sort of model behavior. That is the equation. You can then run least squares or other regression of the data against that equation to show correlation to the model.

TTFN

 

[electricpete]

I'm not sure I completely understand the question, but perhaps it amounts to communicating to the customer the logic behind the spreadsheet. By the nature of spreadsheets, that can often be difficult. It is helped if the spreadsheet it well documented.

Some tools for documenting spreadsheets and making them easier to use:
Label the frequently-used variables with a descriptive name instead of using cell reference. Often it is handy to put the name of the variabel immediately adjacent to it and create name using insert/name function/create / top-row or left-column.

If you don't have a lot of named-variables, then if you do a printout of the spreadsheet you absolutely have to set it up so it prints the row and column headers so readers can understand it.

One thing that can be very helpful in documenting results which won't take a lot of time is the getformula function described earlier in this forum. It will allow you to display the formula contents of a cell in a separate cell for documntation purposes.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

If all you have is a bunch of values (no calculations), then my previous comments won't help.

A clear description of where the data came from is best as suggested by IRStuff

Also excel has a number of curve-fitting functions which may or may not be relevant to satisfying the customer's request.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[FreddyMusic]

Thanks for comments at the moment.

I try to make the problem clear, but I believe that many engineers have the same problem as I had.
It exists on all engineering to physical world.

I assume, physical model is Load to Stress, or Speed to Torque, or Current to Voltage...whatever
( My customer will not pay me, if detail and physical model is open from my side.)

Below is the procedure.

1. We did many jobs by our test rig.
2. Then we have reliable test data.
3. We analyzed those data, with as much as knowledge, we have at the moment.
4. At this stage, we filter some parameters and relation by physical and logical think.
5. Now we have a curve, Let’s call it coefficient curve on EXCEL spreadsheet.
This curve composed by 100 points on X-Y coordinates.
6. This curve can be trust and smoothly, But it is discrete data. Not precise enough for 0.000 digital.
7. My customer needs a precise continuous data or a continuous curve or a reliable formulate so that he can make programme to any points he likes. Perhaps some new relation or theory behind the curve…. I don’t know.
8. What to do next step…..

This is same thing for Newton on hundreds years before. G=9.8 X M. ( F=a X m)
He saw, apples drop from tree. Then he did many tests, and find this links to apple mass.
So he divide G/ M and found it is a constant G/ M = 9.8
Modern people laugh the simple equation. But it’s great development on history.

My problem is no more a simple constant. It is a smoothly curve.
I try on EXECL. Not powerful. Perhaps I will try other math software.
One of my mathematic friends told me, it could be thousand function or equations match with only one curve.
I don’t expect Nobel’s price.
I only need to convert discrete data to continuous curve, so my customer can programmed later base on it.
A function formulate with some reasonable tolerance will be perfect.
Or some math company knows?

 

[IRstuff]

Then, you've asked for two different things.

1> If you are simply collecting data for a client, then the model is the province of the client. Who else whould know about the mathematical abstraction of his physical system?

2> If all the client cares about is a continous function, then feed him the simplest spline fit you can find, like a cubic spline. Anything beyond that requires his internal knowledge of the system under test.

Personally, I can't see why he's asking you to provide this for him. It's his design; whatever idiosyncrasies there are in the data should be something that he models, not you.

TTFN

 

[FreddyMusic]

Thanks TTFN

The kernel physical model belongs to nobody at the moment.
Because nobody understand it very clear.
Who knows it better, who has it.

We are responsible engineering and physical world.
My client is responsible at software, and converts the knowledge to market product.

Does any powerful math software assistant for such analyze to find a spline?
MathCAD or Mathematica ? What do you recommend?
Experience or Procedure can share?

 

[electricpete]

Every time I have seen a spline, it amounts to an exact fit to each point. I don't think that's what you're looking for. You are looking for a curve fit.

Here is a very simple curve fit procedure in excel.

Guess the functional form of the function.
It might be any of the following or many others
y = k0 + k1x + k2x^2 +k3 x^3 etc
y = k0 + k1*cos(k3*x)
y = k0 + k1 * tan(k2*x)
etc (you get the idea

Pick one form. P the k_i's into cells and make an initial guess at their values. For each x_i in your data set, compute y_hat_i which is your estimated function.

Compute sum of residuals squared sum(y_hat_i-yi)^2

Use the solver tool to minimize the sum of residuals squared by varying the k0, k1, k2 etc.

If you haven't used solver, let us know. We can walk you through it.

There are other curve fitting tools in excel as well but this seems like a good simple approach to me and extremely flexible in terms of the functional form of the solution (pick any form you want).


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

"(pick any form you want)"

I didn't mean it was a random choice. I meant that if your eyeball gives you a clue of what type of function or the theory gives you some type of clue of what type of function, you can make that function your guess.

Also note that this approach is not limited to one input variable. You can compute your y from multiple inputs. You could also have multiple outputs in which case you would have to sum the residuals from estimates of all outputs into a single cell.

Another form often used is
y = k0 + k1 * x^k2

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

A correction to the trigonmetric functions. Should allow for a phase shift as follows:
y = k0 + k1*cos(k3*x+k4)
y = k0 + k1 * tan(k2*x+k4)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[melone]

I still don't follow. Your X,Y data points must mean something. If X is force, and Y is displacement, then (assuming a linear relationship)):

Y=mX+b => Displacement = m*Force + b

If you have 3 variables, then you will have to graph in 3D. Unfortunately, I don't think Excel can regress a 3 variable equation.

As far a digital versus continuous data, you will NEVER have truly continuous data. You will always work from a finite data set and use some sort of regression to define the relationship between the variables.

 

[MintJulep]

What is the desired end result; what is your client attempting to do?

If you need a best fit curve, there is no shortage of methods. If the curve is very complex, then as your mathmatician friend says, you may need to superimpose a lot of functions.

If you need a function that has to return each and every coordinate pair exaclty, and interpolate inbetween, than you need a lookup table and a method of interpolation.

 

[diamondjim]

It would be helpful if you listed your type
of input data and what your output data is
for anyone to even guess at what you are
trying to ascertain.
What does your curve look like? Is it always
sloping up, always sloping down, or combination
of slopes?
I am assuming that your input data is constantly
increaseing by some increment?

 

[electricpete]

melone says there is no capability to handle multiple input variables. I think with solver it can be done.

For multiple input/single-output problem with many sets of data i=1..N

yhat_i = k0 + k11*x1_i + k12*x1_i^2 + k13*x1_i^3
+ k21*x2_i + k22*x2_i^2 + k23*x2_i^3
+ k31*x3_i + k32*x3_i^2 + k33*x3_i^3
etc
For the i'th set of data (x1_i, x2_i, x3_i, y_i) compute the residual
r_i = y_hat_i((x1_i, x2_i, x3_i))- y_i

Compute the sum of squares of r_i (1..N) in a target cell.

Use solver to find value of k's that minimize the value of the target cell.

If several outputs, as a first simpler attempt you could add all the sum of the squares together in a single target cell and try to solve all at once. But that would complicate the problem in terms of complexity and the need for a good initial guess. Probably better to split into several multiple-input/single-output problems and solve each individually as shown above.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Needless to say, after you run solver you have to inspect the solution to see if you are satisfied with it. One quick easy way is to look at the computed residuals. If they are all below 0.0001 and you're only looking for 0.001 accuracy, you probably have a good solution. If residuals are not good, you may not have picked a suitable guess at the functional form, the data may not have been well-behaved, or some combination. Also for non-linear minimization problems there is more than one minimum and excel might not find the best one. That situation might be resolved by chaning/improving your initial guess.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Correction:
"Also for non-linear minimization problems there may be more than one minimum..."

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Last correction:
"Also for non-linear minimization problems there may be more than one local minimum..."


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

A random attempt at curve fitting usually generatespoor results. As an example, polynomial fits improve with the order, but the result begins to look like a spline fit, since each additional degree of freedom allows the curve to more closely fit each datapoint exactly, which is what a spline fit does. Without an a priori equation, there's no way to determine the correct order of a polynomial fit.

Without the actual physical model equation, there is no plausible approach to figure out the correct equation.

TTFN

 

[IFRs]

You can curve fit various curves, then look at the statistical deviations to determine which is best. However, you will likely never get to the original equation...

 

[electricpete]

"Without the actual physical model equation, there is no plausible approach to figure out the correct equation."

So if you were asked to fit a set of measured data without having complete access the underlying physical model, you would give up and go home? Most people are a little more resourceful than that. They would use a review of the data itself (along with any limited physical knowledge of the problem they do have) to formulate some educated guesses at the most likely functional forms.

If the data itself and physical knowledge don't give any clues, then there is no harm to try the multi-input/single output polynomial form I suggested above.... When you're all done, assuming the data is not sparse (100 points!) and encompasses the range of interest, the final success of this approach can be reasonably evaluated by examination of the residuals (again without requiring exact knowledge of the underlying phenomena). If residuals are not acceptable than obviously more work is required.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.