Generating Values from a Graph Plot

[feenix]

Hi, i hope im not being a divy and/or have totally overlooke a previous thread that answers my queries.

So, I have a small set of data which isnt as accurate as id like it. The only way i can make it more accurate (aka, filling in incremental values) is to plot a graph of the data i have and then use the graph to read off the values.

The only issue with this is reading it off by eye is way too innaccurate and i dont have a clue how to get excel to create points on a graph to give me an exact value or if its even possible.

I desperately need this to work so any help would be great.

Cheers,

Chris

[MrShorty]

I'm not certain I understand what your problem is. Do you want to take the limited data set you have and curve-fit/interpolate to obtain other points?

It might help if you could post a sample data set and explain what you want to do.

[feenix]

I can do one better, i can give you the data i have and explain what its about.

Attached is the set of data i have, a graph of the data, and what i require the data set to look like, with dashes in place of the missing data that i need to "accurately guesstimate".

In my experience, drawing a Trend line on the graph and using this to calculate values is terribly inaccurate and no trend line fits this line well enough.

The graph shows the percent of combustion complete against crank anle and i need to have the percentage of combustion completed for every degree, rather than the CA at 2,5,10,50, and 90% combustion completion. i hope that makes sense.

To recap, i need to estimate the percentage combustion for every degree from the data provided.

Id like for there to be a totally automated way of doing this as i have to do it a few more times than just once :P

[MrShorty]

I'm sorry, but I can't download those kinds of things, so I can't look at your data in particular.

What you describe sounds to me like a curve fitting problem. IMO, the first place to start with any curve fitting problem is to determine what you are going to use as your curve fitting function. Since you say that you can't get any of the Chart trendline options to work, I'm guessing that none of those basic forms is quite right. My personal preference is to use the LINEST function, because you aren't as limited in the form your function can take.

[feenix]

Thanks for your reply. Im far from being any good at excel so i need all the help i can get, which is ironic being that my final year project is based almost entirely around Excel.

This is the data that i have:

_____________________
¦ X (CA) ¦ Y (% Burnt) ¦
¦ -7.373 ¦ 2 ¦
¦ -4.541 ¦ 5 ¦
¦ -1.766 ¦ 10 ¦
¦ 10.802 ¦ 50 ¦
¦ 24.101 ¦ 90 ¦
----------------

Sorry for the shoddy table. The data i need is the values of Y at incremental integer values of X.
Ive had a look at the LINEST function and im sorry to say i really dont understand how to use it for what i need.
Thanks again for all the help, its much appreciated.

[MrShorty]

I looked at your data a little bit.

I'm no expert on combustion, but, off the top of my head, I would hypothesize the curve should have kind of an S shape to it. The simplest equation I thought of that readily generates an S shape is a cubic, in this case writing X (CA) as a function of Y (%comb) (x=a+by+cy^2+dy^3).

LINEST does this kind of thing very readily. =LINEST(CA's,%comb^{1,2,3}) This equation seemed to fit the data reasonably well, but not as good as one might like. I expect there are better possible equations out there (using exponentials, maybe??), but I didn't take the time to look into them.

One potential issue with this equation is that you may not know how to get %comb given CA. Solver or Goal Seek can do it, but these are a little tricky to automate. If you know numerical methods and VBA programming, it would be real easy to write a UDF that solves for %comb given CA using the Newton Raphson method.

As I said, the best first step with curve fitting is deciding on the equation to be used. The cubic equation I've shown here as an example might be adequate for your needs. If not, the concept readily extends to any equation that can be expressed in the form g(y)=a1*f1(x)+a2*f2(x)+a3*f3(x)+...

[scubohuntr]

I put your data into a chart and put a simple cubic trendline on it. It comes up as y = -0.0033x3 + 0.1016x2 + 2.5892x + 14.286 with R^2=1.

Just fill the "A" column with the values you want there and put "=(-0.0033*A1^3)+(0.1016*A1^2+2.5892*A1+14.286) in B1. Then fill the "B" column with that equation. It will generate a nice cubic curve. If it were me, I would be very, very hesitant to extrapolate that much data from these few points, but as long as it's clearly labeled as a projection or some such, I guess it will work.

[feenix]

Thanks for all your help

MrShorty, your right, the true graph should be an "S" shape in theory but in measured results, it never completely fits the theory and for it to be complete a 100% burn would need to be measured which is never possible, also a combustion start point needs to be measured which is again very difficult to do.

I have ended up going back to what i totally overlooked in the first place and used a Trend line and it seems to working ok for me now.

Thanks again,

Chris