Fitting Line to Exponential Data

I should be able to do this but can't figure it out in Excel.

I want to fit a line to some nonlinear data so I can project intermediate
points. I gather I need the LOGEST function for this but I'm not able to
make it work.

Here are the X-Y pairs:

      X Y
      4.5 0
      6 18
      10 49
      20 77
      45 99



Any help, with explanations, would be greatly appreciated so I can do this
on my own next time.

TIA


0
someone913 (592)
4/6/2004 5:31:18 PM
excel 39879 articles. 2 followers. Follow

3 Replies
530 Views

Similar Articles

[PageSpeed] 21

John Smith  -

> I should be able to do this but can't figure it out in Excel. I want to
fit a line to some nonlinear data so I can project intermediate points. I
gather I need the LOGEST function for this but I'm not able to make it
work.Here are the X-Y pairs:
>
>       X Y
>       4.5 0
>       6 18
>       10 49
>       20 77
>       45 99
>
> Any help, with explanations, would be greatly appreciated so I can do this
on my own next time. <

Before using worksheet functions, I suggest plotting the data using an XY
(Scatter) chart and using Excel's Add Trendline feature to try various curve
fits.

Referring to Chapter 15, Simple Nonlinear Regression, in the book "Data
Analysis Using Microsoft Excel: Updated for Office XP," by Michael R
Middleton:

For the shape of your data, the Power and Log functions are appropriate;
Excel's Exponential (implemented in the LOGEST function) is not.

Also, if the first data point must have Y = 0, then you cannot use Excel's
Power function. You could either adjust that value to Y = 0.01, or add a
constant to each Y value before using the Power function.

After you decide on the appropriate function, you should use more
significant digits of the Add Trendline function to find intermediate
values, or you can use appropriate worksheet formulas to get Excel's usual
15-digit precision.

I could provide detailed steps from the book for the specific functional
form you choose.

-  Mike Middleton, www.usfca.edu/~middleton



0
middleton1 (108)
4/6/2004 6:15:06 PM
Hi John
I did a fit to C- A*exp(-Bx) using Solver and found a fair fit with
A=158.8428, B= 0.1124 and C=98.24659
Contact me privately and I'll send sample file

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"John Smith" <someone@microsoft.com> wrote in message
news:%23Swm%23z$GEHA.2768@tk2msftngp13.phx.gbl...
> I should be able to do this but can't figure it out in Excel.
>
> I want to fit a line to some nonlinear data so I can project intermediate
> points. I gather I need the LOGEST function for this but I'm not able to
> make it work.
>
> Here are the X-Y pairs:
>
>       X Y
>       4.5 0
>       6 18
>       10 49
>       20 77
>       45 99
>
>
>
> Any help, with explanations, would be greatly appreciated so I can do this
> on my own next time.
>
> TIA
>
>


0
bliengme5824 (3040)
4/6/2004 7:07:23 PM
"Michael R Middleton" <middleton@treeplan.com> wrote in message
news:eGSlaMAHEHA.700@TK2MSFTNGP09.phx.gbl...
> John Smith  -
>
> > I should be able to do this but can't figure it out in Excel. I want to
> fit a line to some nonlinear data so I can project intermediate points. I
> gather I need the LOGEST function for this but I'm not able to make it
> work.Here are the X-Y pairs:
> >
> >       X Y
> >       4.5 0
> >       6 18
> >       10 49
> >       20 77
> >       45 99
> >
> > Any help, with explanations, would be greatly appreciated so I can do
this
> on my own next time. <
>
> Before using worksheet functions, I suggest plotting the data using an XY
> (Scatter) chart and using Excel's Add Trendline feature to try various
curve
> fits.
>
> Referring to Chapter 15, Simple Nonlinear Regression, in the book "Data
> Analysis Using Microsoft Excel: Updated for Office XP," by Michael R
> Middleton:
>
> For the shape of your data, the Power and Log functions are appropriate;
> Excel's Exponential (implemented in the LOGEST function) is not.
>
> Also, if the first data point must have Y = 0, then you cannot use Excel's
> Power function. You could either adjust that value to Y = 0.01, or add a
> constant to each Y value before using the Power function.
>
> After you decide on the appropriate function, you should use more
> significant digits of the Add Trendline function to find intermediate
> values, or you can use appropriate worksheet formulas to get Excel's usual
> 15-digit precision.
>
> I could provide detailed steps from the book for the specific functional
> form you choose.
>
> -  Mike Middleton, www.usfca.edu/~middleton
>

Excellent, thank you. I shall proceed accordingly. I finally got the LOGEST
function to work and can see that it is the wrong equation for a line for
this data.


0
someone913 (592)
4/6/2004 7:11:25 PM
Reply:

Similar Artilces:

Copying charts and their data with relative cell positions
I have a chart with a data table next to it. The looks of the data table and the chart have been modified to the look I need, however I have to repeatedly show this same chart and data table multiple times in the same worksheet but with different data. What I want to do is copy the chart and it's corresponding data table and paste into into a different location on the spreadsheet. However when I do this the chart always keeps the original cell refernces. Apparently the chart uses absolute referncing instead of relative referncing. When I attempt to modify the chart's data s...

Export Excel Data to Access With a Macro!!!
Hi, I have been trying to create a macro in excel to automatically export data into access, but to no avail. Would somebody please give me a hint? Your assistance will be greatly appreciated. Thanks Using Access either "Link" the spreadsheet as a table or import the excel data. Otherwise research ADO and CreateTable to learn how to push the data cell by cell. Ken "Jac" wrote: > Hi, > > I have been trying to create a macro in excel to automatically export data > into access, but to no avail. Would somebody please give me a hint? > Your assistance ...

getting tight vertical fit automatically
Is there a way to get a tight fit vertically, without having to hand-adjust many row heights (some of three lines, some of 1, etc)? If format|row|autofit is too "loose", then I think manually is the way to go. Kevin wrote: > > Is there a way to get a tight fit vertically, without having to > hand-adjust many row heights (some of three lines, some of 1, etc)? -- Dave Peterson ...

Finding data on another worksheet in same workbook...?
Hello, I previously had data on same worksheet as formula: =COUNTIF(B99:B1000,A3) and so on... But now the data is on Worksheet named: "ItemList" With again all the data being between B99 and B1000.... How do I now put the new worksheet into the formula above so that it will get data from cells B99 to B1000 on worksheet named ItemList? TIA DP David Use: =COUNTIF(itemlist!B99:B1000,A3) Andy. "David Penney" <dpenney@no-spam.iinet.net.au> wrote in message news:3f6b18f4$0$23593$5a62ac22@freenews.iinet.net.au... > Hello, > > I previously had data on...

Year End closing with master data & setup required
Hi We are at the Year end closing, however we want to know if its feasible to close the company, create a new with the same details in terms of setup & master data but without any of the old transaction history and other history data. How can we do this is GP Utilities and is this the correct way ? Rick You can only create a new company using Utilities. You cannot copy any of the data. To copy data from one company to another you have to use a tool like the SQL Server Import/Export wizard or Data Transformation Services or even Microsoft Access. There is a knowledgebase art...

charting hidden data...
This is a multi-part message in MIME format. ------=_NextPart_000_003E_01C464FF.8505E290 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all,=20 Is there any way to have an Excel chart continue to show values when the = source data columns or rows are hidden? I have some charts whose data = sources I want hidden in my main spreadsheet - the only problem is that = when the columns are hidden my charts are blank! Thanks in advance for any help. Harry ------=_NextPart_000_003E_01C464FF.8505E290 Content-Type: text/html; charset="is...

Refreshing pivot table when cut/paste data
I think this is supposed to be easy, but I'm having trouble. I have a pivot table that is run off data called Source. I need to replace the Source data on occasion, and so have been "cutting and pasting" the new data into Source - it's exactly the same in terms of columns, but has more rows. When I got to "refresh", the pivot table says it's invalid data, or some such error, and I have to rebuild. I think there is a way with links to do this, but I can't find it clearly explained. Any suggestions? Debra Dalgleish has some (lots) instructions here ...

Purchase Orders
Hello, I'm trying to create POs using GP (version 8.00g34) on our GP server, however going Transactions -> Purchasing -> Purchase Order Entry generates a "Cannot access this form because the dictionary containing it is not loaded." error. The other areas of GP (which I have access to) look OK. Could there be something wrong with my Dynamics.set file? We have workstations that use GP and the .set file looks the same. Any advice would be greatly appreciated. Thanks in advance, Chris Check to make sure there security is not looking for an alternate Great Plains windo...

Subtotal Lines in the wrong place Excel 2003
When nesting subtotals in Excel 2003, the subtotal lines are not in the correct position. Is there a fix or a work around for this problem? Example: Craft Code Crew Count BA Elec 1 BA Elec 1 BA Total 2 Elec Total 2 Hi AFAIK you should start with the subtotal for the right-most column and then add the other columns step by step -- Regards Frank Kabel Frankfurt, Germany "k2quayle" <k2quayle@discussions.microsoft.com> schrieb im Newsbeitrag news:FF252538...

Drag and drop data only??
Hello, I am working on a very large spreadsheet and need to drag and drop large amounts of data. The problem is that when I do this is drags and drops my borders also, leaving the empty cells with no borders. Is there anyway that I can drag and drop my data only? When you drag and drop use your right mouse button instead of the left. That will give you a menu of options. Select Copy Values Only. -- HTH... Jim Thomlinson "Chanda" wrote: > Hello, > I am working on a very large spreadsheet and need to drag and drop large > amounts of data. The p...

no data echo in fields
I have two spreadsheets. On both, all of the sudden the data in the spreadsheet cannot be seen. If I click on a cell box, the data is at the top. I can click on the top and the cell will fill with the data. As soon as I click another cell the data goes away. The only change I have made is to install Crystal Reports on my computer. Any ideas or thoughts would be very helpfull. ...

storing custom resource data in text format #2
Hi, When I try to insert a resource file, I can choose among standard resource types such as icon, bitmap, dialog, cursor and HTML. I wish to store some SQL text. When I try to insert this as a custom resource, it always assumes it to be binary data, which makes editing it from within the resource editor a pain in the ... So currently I am storing this under "HTML" type, as it is the only resource type that lets me insert text files. Is there some way for me to insert my own "SQL" resource type, and let the resource editor know that this category is "text" en no...

Help required to send data from one worksheet to another
I'm trying to create a spreadsheet that will be used to log details of calls coming into a helpdesk. The spreadsheet is going to be used to provide management information about the nature and type of calls. I'm trying to move all data from one sheet onto another sheet in the same workbook (sheets CLT and CS). I'm looking to transfer data from cells L8,L10,L12,L14 on sheet CLT to sheet CS when a button is clicked. When the button is pressed for the first time the data should record on Sheet CS in Cells A2,C2,E2,G2, the second time the button is pressed the data should record in ce...

Email line spacing
Hi, When writing an email in MSCRM double line spacing is used. Does anyone know of how to change this to single line spacing. At the moment we are having to write the email in Word and then copy/paste it into the MSCRM email. Thanks Lee Instead of pressing Enter, hold down Shirt button and press Enter. "Lee" <l.hargrave@btinternet.com> wrote in message news:394322d8.0412160110.76f2716e@posting.google.com... > Hi, > > When writing an email in MSCRM double line spacing is used. Does > anyone know of how to change this to single line spacing. At the > moment ...

How do I specify using lines or bars on graphs w/ 2nd value axis?
I am building a chart using a secondary axis with both the data displayed in both bars and lines. Everytime I add a new field Excel decides if it will be displayed as a bar or a line. How do I control that decision myself. I understand how to align data to a secondary axis, but not how to control the bar vs line option. Create your chart using all lines or all bars. Select one to change, and use Chart Type on the Chart menu to change it. Select the next to change and use the F4 function key shortcut for Repeat Last Action. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier ...

The data for the search should be organized in a single contiguous range in one column.
Hi ALL, I am new to this forum. basically looking for a macro in excel that would match the values that are opposite and not equal and also in the same column. E;g: 100 250 -100 -100 -25 -25 Basically the values should match and the cell highlighted. I want that values , which are matched are highlighted. In other world , where the sum =0, then it should match all the relevant values and highlight's them Note: the column should be min of 100 lines the result should show:Highlight 250,-100,-100,-25,-25 , as there sum =0 100 250 -100 -100 -25 -25 Hope to see a reply soon .... Hi Sam, ...

Have duplicate rows of data
I have a database in Excel format that I need to trim down. There are duplicate "entries", with identical information by rows. Is it possible to have it automatically trimmed, deleting the duplicate rows? I have 2,100 rows of data, many of which are duplicate. Just want to trim that down... Thanks, Dave --- Message posted from http://www.ExcelForum.com/ Hi Dave have a look at http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows Frank > I have a database in Excel format that I need to trim down. There are > duplicate "entries", with identical informat...

Problem deserializing XML data with xsd.exe
Hi everyone, I'm trying to read an xml config file in C#. To do this, I have used the xsd.exe tool to create an xsd file from my xml, and to generate code to read this xml file. Here is a small sample of my xml file: <feed> <location> <directory>data</directory> </location> <files> <file> <name>name</name> </file> </files> </feed> Whereas I'm able to read the directory value correctly, I cannot read the value of name. After some searching, and after modifyin...

Command Line Interface
Hi, I have a rather peculiar problem, I need to impliement a command-line style interface into my MFC dialog program which can output, read / write strings etc. The thing is the functions I need to step them through are extensive, and do need to be written in C++ function forms, in effect I need to write a console app, within a dialog app, and have the console as part of the dialog window. Does anyone have any idea's how I could impliment such an interface? I understand this may be pretty vuage, - MR Read the second question in http://msdn.microsoft.com/msdnmag/issues/04/0...

How to insert one line after declaration in exsiting xml file?
Here is partial of existing xml file: <?xml version="1.0" standalone="yes" ?> <NewDataSet> <SpecSum> <USER>ANDYK</USER> <Grouper>Allergy/Immunology</Grouper> <Auths>0</Auths> <Cost>0</Cost> </SpecSum> <SpecSum> In asp.net, how to insert one line after declaration in exsiting xml file? <?xml version="1.0" standalone="yes" ?> <insert new line here.......> <NewDataSet> <SpecSum> <USER>ANDYK</USER> &...

Can't transfer money data file between computers
I've tried, but failed, to transfer my money data file from my old computer to my new computer (old is ME, new is XP). No matter what I do I keep getting an error message "Money cannot locate [my file location and file name] or cannot open it." I've tried to make a backup copy from my old computer, and open this with the "restore backup" feature, but got the above message. Anyone have any ideas or suggestions? Thanks In microsoft.public.money, Mark047 wrote: >I've tried, but failed, to transfer my money data file from my old computer >to my new...

Summarization of Excel data
I need to summarize (subtotal) rows in an Excel spreadsheet. The subtotal command gets me part of the way there, but I need more functionality. I have 1000 records that I want to subtotal amount by customer number. But, I also need the customer name, address, record count, etc. in the subtotal record. I would like to be able to use an sql statement to do it, but don't know if possible. Any ideas? I don't know, but it sound like a job for a pivot table to me. As long as your dataset isn't dreadfully huge, I think it would do the trick. Eric Try DATA, SUBTOTAL This will m...

On Line Stmt Balances Different than Account List Balances
I just migrated my money files from an old pc to my new pc by backing up the files on a diskette and then opening the backup diskette on my new pc. I then downloaded about a one week of transactions from my bank and credit cards; however the balances reflected on the "Read Online Statments" screen are different than the "Account List" balances, and the "Read online" balances appear to be the correct balances and agree with the balances on the institution websites. .. I just had a very similar problem. I had downloaded transactions on my old PC and ha...

newbie needs examples of creating a recordset and inserting data using it in Access 2007?
I have created a Access 2007 database, Test.mdb, created a table called "mytest" and created required fields in it. Now, using VBA(Visual Basic for Applications) I want to insert data into it using a Recordset from a Word document. Can anyone please point me to a proper link where there is an example of how data is inserted using a Recordset into a Access 2007 table? I created one using resources on Web, but it is not correct. Sub TestRecordsetexample Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myReco...

Automatically highlight certain data.
I have a spreadsheet with a schedule of duties that are done every Sunday. Different people are assigned each week for each duty. For instance: Sunday, May 2 Greeters Mr. x Ushers Mrs. y Counters Ms. z It's a vey long list and a very large spreadsheet (covers several years of Sundays) I'd like to be able to highlight/change letter color for the Sunday list that has the info for the upcoming Sunday. I've been trying to figure it with 'Conditional Formatting' but there must be a calculation, or something, to figure out h...