Determining the apex of a order 2 polynomial

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

History:

I created a spreadsheet with functions only that will provide feedback from
a data point on an xy scatter chart. I charted several thousand cells with
simple functions such that =if(and(a,b),1,0) to provide feedback based on
these coordinates and then used v or h lookup to come up with the adjusted
information. This is for a field determination of what the data points should
produce.

I thought it would be nice to duplicate the form for a similar process in
the lab. We frequently test the field information to gain more exact
information in the lab. This would allow me to show both the field
determination and the lab results on the same chart.

The lab test is performed and results in 3 to 8 x,y data points. There will
never be less than 3 or more than 8 even if it results in 400 samples. We
will only use the last 8 at the maximum. Through testing I have found that
the 2nd order polynomial trend line exactly pinpoints the results 99.9% of
the time,(I haven't had a miss yet but you never know). I need to know how to
draw the x,y data point from the apex of the polynomial trend line.

Example:

x = 13.0,15.1,16.9,19.2
y = 120.0,125.0,130.0,127.0

X will always grow no matter how many tests we perform(at a relatively
stable rate). Y grows and breaks, declines, at a very unstable rate. Y will
always break 1 data point prior to the last. All points are calculated to the
nearest tenth (#.#)

I have set the series up such that if the y point breaks, the remainder of
the empty x and y cells are automatically set to the final break point
(127,19 see above). This seems to eliminate all the empty data points by
merging them into the last and subsequently eliminates them from the
polynomial trend line.

Question

How do I find the apex of this polynomial trend line? I ran several hundred
samples and have found that the trend line equation that excel emits is
always different. I tried running the regression analysis but found no two
cells that could be used as some sort of factoring.

If this is a VBA answer and you choose to help me, please feed me pablum as
I have no clue how to even begin with VBA. I do know javascript fairly well
if there is any coorelation.
 
For a quadratic
a*x^2 + b*x + c
the maximum (or minimum) value occurs when the first derivative is zero
2*a*x + b = 0
or equivalently when
x = -b/a/2
To get adequate precision, you should either format the chart trendline
equation to scientific notation with 14 decimal places, or else use LINEST to
fit the polynomial
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Jerry
 
Jerry;

Uh, Yeah. See, I am a blithering idiot that is simply incapable of
understanding what you just said.

In your formula - a*x^2 + b*x + c - it looks like you are saying that the x
is my x value, but which one. I have no clue what a, b or c is. I know that
the carot means multiply x by itself, how ever many numbers are after the
carot. That said, this formula looks quite a bit like the one that excel
produces on my chart when I check the "Display equation on chart" box in the
format trendline dialog.

My problem is that this number changes with every lab test that is
performed. Thus, the chart equation changes with every test result that is
keyed into the spreadsheet.

I have tried using the linest function and changed all of the constant and
stats values and I keep getting the same number. I get the same number
whether entered as an array or copy and paste or grab and drag. Frankly, I am
not sure what to do with this number. Am I supposed to give excel a Y and
calculate the X based on the value of the linest()?. If this is the case, it
doesn't work because I don't know what the Y is. That is, I would very much
like excel to tell me what this number is. Both X and Y at the apex of the
curve that is.

Please do not mistake me for a smart ass. I am truly and idiot. I haven't
had a math class in 30 some years. Your and all other people who may decide
to try to explain this to me are greatly appreciated. I would certainly be in
your debt.

Thank you all in advance for your help

Frank
 
Jerry;

The linest() worked great. I messed up by continuing to try to pull out
three variables. When I asked for just 2 it clicked in and gave me all the
information I was looking for at the exact point I was looking for.

Thanks for all your help Jerry. I really appreciate it.

Frank Pytel
 
Back
Top