#### Determining the apex of a order 2 polynomial

```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.
```
 0
fpytel (13)
7/25/2007 2:12:02 AM
excel.charting 18370 articles. 0 followers.

3 Replies
604 Views

Similar Articles

[PageSpeed] 51

```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

"Frank Pytel" wrote:

> 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.
```
 0
7/25/2007 11:34:01 AM
```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

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

Frank

"Jerry W. Lewis" wrote:

>   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
>
> "Frank Pytel" wrote:
>
> > 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.
```
 0
fpytel (13)
7/25/2007 11:48:00 PM
```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

"Jerry W. Lewis" wrote:

>   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
>
> "Frank Pytel" wrote:
>
> > 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.
```
 0
fpytel (13)
8/31/2007 4:20:01 PM

Similar Artilces:

multiple emails being sent out #2
It appears that when a message is sent through a > distribution list that sometimes the email gets hung in > the outbox. Outlook believes that the message has failed > to send but does not bring up an error message or > anything, it will just attempt to send it again approx. 2 > mins later. > The sending computer has in its sent items that the > message was sent at 10:30 AM > Two of the recieving computers recieved the message at > 10:14 AM > 10:18 AM > 10:20 AM > 10:22 AM > all computers recieve the time from the domain controller > so that the ti...

inkjet receipt printers for RMS 2.0?
We're currently using laser printers for receipts - read BIG footprint and slooowwww printing. However, copy is super clean! I'd like something smaller and faster. I hate the fading, flimsy thermal paper print solutions, and so I'm considering either the Ithaca or Epson Inkjet receipt printers. Though they aren't as fast as the thermals, they are fast enough for us. Pros? Cons? Anybody use them? Will they work? Nathan Jung ...

Get The Independent Investor ebook FREE #2
Dear Friends While searching the net I came across an excellent ebook called The Independent Investor This 75-page eBook will stand conventional investment notions on their collective head, and explain market behaviors that have always been considered "inexplicable" using hard facts and generous price charts. The eBook is an instant and free download Get it now from this link. http://www.elliottwave.com/a.asp?url=/wave/independentebook/?code=aff&cn=6ss Best Regards Deepak Sahijwala ...

Great Plains Integration for CRM 1.2??
Is there a new version of the GP Integration kit for CRM 1.2 or will the 1.0 version still work? There will be an upgrade to the Integration (v1.2) but it hasn't been released yet. Integration v1.0 is compatible with CRM v1.2 though. Annie >-----Original Message----- >Is there a new version of the GP Integration kit for CRM 1.2 or will >the 1.0 version still work? >. > Where can we get hold of this integration 1.0 CD or download? We are aMGP reseller and no one at UK Microsoft knows about this CD. Any advice? J "Annie" <anonymous@discussions.microsof...

How do I stop Excel from changing 02117240000 into 2.11724E+11 in.
I am working with files, trying to convert electronic billings into *.csv files for a customer. It seems to work but when I re-open the file, all of my customer's cost center numbers change from what they are supposed to be (i.e. 0211724000000) to something like 2.11724E+11 How do I stop the numbers from changing? Thanks Hi try formating these numbers as 'Text'. also saving the file as *.txt file may help as Excel will ask you for the type of this column in this case (and you can choose 'Text') -- Regards Frank Kabel Frankfurt, Germany "BrendaC" <Brenda...

Formula for calculating Gross Profit with muktiple discounts #2
List Price less Disc1 less Disc2 equals Net Price. Sell price less net price equal profit divided by sell price equal gross margin percentage. \$10 less 50% less 10% =\$4.50. Sell for \$12.50. \$6.00 less \$4.50 =\$1.50. \$1.50/6.00 = 25% GP. All of these figures are in cells of their own. Cannot get this to calculate correcly. Please help! Urgent CARM Wrote: > List Price less Disc1 less Disc2 equals Net Price. Sell price less net > price equal profit divided by sell price equal gross margi > percentage. > > \$10 less 50% less 10% =\$4.50. Sell for \$12.50. \$6.00 less > \$4....

I say use Account 1, but OL uses Account 2
OL 2003, SP2 I started experiencing a strange phenom a few days ago. I have OL set up with a number of different accounts. The default account we'll call Account 1. I noticed that when I send email to one particular person (at an AOL account, fwiw). my copy of the message (found in my Sent Items folder) shows it to have been sent from one of my other accounts (call it "Account 2"). Since noting that, I've been extremely careful to make sure I've even manually selected the sending account. Other symptoms: 1) The aol recipient is not receiving the messages (tho...

AR aging #2
1. Is it true that AR aging report with options will print amounts in correct bucket even if aging is not run. If I do not perform aging, will my historical aging report come out correctly. 2. For a company that does not charge finance charges and does not print statements, what is the advantage of running aging process? --Scott Scott, Yes, the ageing reports will age the accounts even if the Ageing process has not been run. The Ageing process will (1) update the ageing buckets in the customer card; (2) provide aged amounts for use in Cyrstal Reports or any other external reporting...

Recovery Storage Group error when trying to add database #2
Can you please tell me where I would make the change to the DN as described below? Hi, The problem is that in the distinguished name of the database there is a single parenthesis without its match. Examples of this would include: cn=Mailbox Store 1 (SG1,cn=First Storage Group,.... cn=Mailbox Store 1 (SG2),cn=Second Storage Group (TestServer,cn=... If you add the closing parenthesis and force replication of the Active Directory the "Add Databases to Recover" dialog should work properly. The problem is the LDAP request that is sent to the Domain Controller to render ...

Selective Out Of Office Reply #2
Is there a way to configure the ooo reply so it send different messages depending on the sender No, use Wizard Rules to create a custom reply for this. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Leon" <greg_wikle@balinet.com> wrote in message news:O3QiWKMcEHA.2660@TK2MSFTNGP12.phx.gbl... > Is there a way to configure the ooo reply so it send different messages > depending on the sender > > ...

Font Color #2
When responding to an email, you have the option to mark your comments with your name or whatever. The comments font color always default to blue. Is there any way to change that default color without having to manually change it in the message? ...

Email replies #2
Running OL 03, and would like to know if there is a way to automatically have set-up CC reply to's, without having to manually type in set contacts each time? Thanks, RED anglirich <anglirich@lexcominc.net> wrote: > Running OL 03, and would like to know if there is a way to > automatically have set-up CC reply to's, without having to manually > type in set contacts each time? Check your sending rules. You'll see that there is one that will add Cc contacts automatically. It's "Cc the message to people or distribution list". -- Brian Tillma...

multiple images in list control #2
Hi, I need to insert individual images for individual columns in a list control. How to go about this ? Thank you. ...

Inbound e-mail statistics for 2 domains under Exchange 5.5
Hi, I'm currently running an Exchange server with email being retreived using an ETRN. We retrieve email from 2 domains one of which feeds into the other. For some time the intention of our company has to be to retire the older of our two domain names and work using only one. Our problem is we need to to work out how much email we recieve through our older domain name compared to our newer one as we don't want to retire it prematurely and confuse our customers. Does anyone know a way of doing this inside Exchange or using other, hopefully free, tools? Our ISP is unable to help and ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! ...

Organization Forms #2
Unable to see the "Organization Forms" library in Outlook 2003. Anybody experience this ? I've created the folder below "EForms Registry" in ESM, checked permissions but the library does not show up. Enviroment: Exchange 2003, SP1,4-Node cluster, 3 Virtual Servers, Public Folder tree on 1 Virtual Server. Any help would be appreciated ! -Mike ...

How do I determine the number of cell formats in this workbook
How do I determine the number of cell formats in this workbook I have an Excel addin that performs that task. Email me for it at ng@officearticles.com ************ Anne Troy www.OfficeArticles.com "tucsonmissiledude" <tucsonmissiledude@discussions.microsoft.com> wrote in message news:C4A64A8E-BACC-434C-A0A1-AC6819EA5694@microsoft.com... > How do I determine the number of cell formats in this workbook Hi TucsonMissileDude, Try the following code from Leo Heuser. To delete unused formats, answer 'yes' to the imitial message box; ansewring 'no' will re...

Copying one cell to muliple cells #2
Cell a1 is a date cell. Cells b1-b30 are date cells. What I am trying to do is every time that I enter a new date in Ai, I want it to go to a blank cell in b column and not over write the dates that are already there. A B 2/3/04 1/6/04 1/20/04 1/25/04 2/3/04 right click sheet tab>view code>copy\paste this>format col B as desired date >save. Now when you put a date in a1 such as 8/1, the last cell+1 in col b will get that date. Private Sub Worksheet_...

Rank order with conditions
I am trying to rank order data so that there are no ties. If there is a tie in the score (column A) then Yes precedes No in column B. If there is still a tie Yes precedes No in column C. If there is still a tie rank order descending in column D. If there is still a tie rank order descending in column E. If there is still a tie rank order descending in column F. If there is still a tie mark "Tie - rank #" I cannot sort and filter as this is a very large spreadsheet and I need it to do it automatically for someone who only knows how to enter data on Excel. I have managed...

Excel problem #2
When I start excel is wants to open all the files in the directory automatically. This is a big pain, is there anything I can do outside of removing Office 2000 and reinstalling? Thanks in advance Excel will open all the files in your XLStart folder and all the files specified in: Tools|Options|General Tab: "at startup, open all files in:" <xl2002 wording> "Alternate Startup File location" <before xl2002 wording> Erase what you typed in there. (don't delete the files/folder--just the reference to it.) Sport wrote: > > When I start excel is...

HTTP/1.1 503 Service Unavailable #2
Hello all, My Exchange 2003 has been up and running for some time now. A Tornado came thru and power lines were down. When Power came back, I restarted my system and started all the Exchange services except Pop3 and replication, the Exchange is being accessed thru OWA and when I try getting on the exchange thru OWA, I initially got my logon page. Somehow I had a link problem for about 5 minutes, and when my link came back up, I tried getting on my exchnge but the page gave me HTTP:/1.1 503 Service Unavailable. Can someone please tell me what the problem might be? Thanks -- An Avvid S...

FTP #2
Greetings. I have an MFC application working just fine using ftp with CInternetSession and CFtpConnection, and I'm using windows ftp server. this app needs to check file's size and dates where CFtpFileFind seems to works perfectly. I have some doubts: 1. this app seems to work fine with filezila ftp server... is there somethig I have to know working with other ftp servers specially servers runing on linux? I have seen that list files is not standard... 2. MFC can handle FTPS?? if not, does any one knows a good library? I'm starting to see libcurl. Any suggestions are welc...

Entourage 2008 (12.2.4 update) meeting invites
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I can't seem to send meeting invites from Entourage. I click on &quot;Invites&quot; and then enter an email address the &quot;send now&quot; button gets greyed out. Also, when an invite is received from Outlook the &quot;accept, decline and tentative&quot; buttons are greyed out as well. Right click option is the same too. We are using Exchange 2007 and any help would be appreciated... On 3/15/10 3:55 PM, Maunir@officeformac.com wrote: > I can't...

SendKeys Macro to Comapct Database in Conversion 2.0 to Access 200
Hi All, I am trying to convert an Access 2.0 database to an Access 2003 database however I am having trouble converting a SendKey macro that in Access 2.0 compacts the database. I dont full understand how it is doing this. the macro is: SendKeys "c:\statement\database.mdb~c:\statement\temp.mdb~" DoMenuItems 4, 0, 2 I am not too sure what this is trying to do, but it ends uo comapcting the database in Access 2.0. Thanks for any help I'm not sure about those menu items in version 2, however you could do away with the macro entirely. In 2003 there is an option (Tools, Opti...

CRM 1.2 database, but no CRM Server, new AD, needing to be upgrade
We have the SQL database files from a 1.2 CRM installation. There isn't much data in it, but enough that we don't want to lose it. The client (on their own, btw) moved the data into a SQL 2005 environment on a new domain. Then they pretty much killed the old one, including their CRM 1.2 installation. They tried to apply the upgrade themselves, but it didn't work. Now we have to figure out a way to at least save the data and bring it into a 3.0 environment. It doesn't look like the redeployment tools will work, since the old server is no longer around. Any thoughts?...