Calculating or estimating the area between to curves

I have seen some great posts about colouring the area between 2 lines but I 
need to calculate numerically the area between 2 lines. A close estimate 
would do. My curves are smoothed lines but they have many roots to fit a 
trendline whose equation I could have integrated. If I use straight lines 
between points I have too big an error.  Even Simpsons Rule is not accurate 
enough at times. Is there a way that Excel can return a figure for the area 
under a curve - I could even use the number of pixels it has shaded between 
the 2 lines!
0
8/13/2006 8:51:01 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
591 Views

Similar Articles

[PageSpeed] 47

Hello, Broadband!
You wrote  on Sun, 13 Aug 2006 01:51:01 -0700:

 BA> I have seen some great posts about colouring the area
 BA> between 2 lines but I need to calculate numerically the
 BA> area between 2 lines. A close estimate would do. My curves
 BA> are smoothed lines but they have many roots to fit a
 BA> trendline whose equation I could have integrated. If I use
 BA> straight lines between points I have too big an error.
 BA> Even Simpsons Rule is not accurate enough at times. Is
 BA> there a way that Excel can return a figure for the area
 BA> under a curve - I could even use the number of pixels it
 BA> has shaded between the 2 lines!

I think from what you say that you realise that the area between 
the two curves is the difference between the areas under them. 
You could use the differences between the integrals of the 
fitted polynomials. With Excel 2002 as I have or earlier, I 
would not use the equation given by the trend line on the chart 
but actually fit the curve.

I'm not really quite sure what you mean by Simpson's rule not 
being accurate enough. It would seem to me that if you have a 
large number of experimental points, it is about as accurate as 
you can get. In fact, it uses all the information available 
unless you have good reason to believe the real graph is smooth.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations: 
not.jim.silverton.at.comcast.not 

0
8/13/2006 2:28:08 PM
Not sure I understand why Simpson's rule (or any other Riemann sum)
wouldn't converge to the correct answer in your case.  It's been a
while since I took calculus, but it seems to me that it can be proven
that, for a "nice" continuous function, Riemann sums have to converge
to the correct answer in the limit as the number of subdivisions goes
to infinity.  Perhaps your problem stems from not having enough data
points to accurately determine the curves, or perhaps you are using too
few intervals in your application of Simpson's rule.

Along the lines of "counting pixels," there's another method of
numerical integration that I think is referred to as "Monte Carlo"
integration.  Basically, you bound the functions within a rectangle,
for which you can easily determine the area.  Then select several
(thousand?) points at random from within that rectangle.  Test each
point to see if it lies within the region of interest.  You then end up
with a ratio of points within the region to the total number of points
selected [n(in)/n(tot)].  The area between the curves then is this
ratio multiplied by the area of the selected box.  I have no practical
experience with this method, having always used Riemann sums or the
Fundamental Theorom to get the area between two curves.  I can't say
for sure if it will be easier for you to implement than Riemann sums or
not, nor am I intimiately familiar with all of the possible pitfalls.


-- 
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181
View this thread: http://www.excelforum.com/showthread.php?threadid=571133

0
8/14/2006 11:20:05 PM
Reply:

Similar Artilces:

Help with an Area chart
I have the following series and values: series actual value 92.95% series normal value 98.00% series stretch value 99.00% series break value 100.00% I need an area chart, which I have, but the series are shaded as follows: the Acutal series is shaded from 86% (chart minimum) to 92.95% and the Normal series is shaded from 92.95% to 98.00%, etc. . . Is there a way that my chart can shade the Actual series from 92.95% to 98.00% and the Normal series from 98.00% to 99.00% and so on? Thanks so much - Hi, If you use the area chart (not stacked area) and change the series order to Break, S...

Software about financial calculators
I am interested in an aplication or a software that can be used like a financial calculators. If it is for free, better. Hi, If you have specific needs, maybe(?) I can create one for you. visit my sites: http://www.geocities.com/excelmarksway http://au.geocities.com/windsofmark regards mark excelmarksway@yahoo.com.au >-----Original Message----- >I am interested in an aplication or a software that can >be used like a financial calculators. If it is for free, >better. >. > ...

[how] CTabCtrl determines display area?
I wanna display somethings (e.g. a dialog without caption bar) in tab, you know I mean which origin is under the tab item just as common propertysheet. How and should I to do? -- Frank F.Han +-----------------------------------------+ | winsays@:-)hotmail:-).com | +-----------------------------------------+ ...

display of calculation result is wrong
I've created a spreadsheet where certain input values seem to cause an incorrect value to appear in a cell. The calculation result should display the calue of 90%, but the cell shows a value of 168%. I have two other cells that reference this cell: one shows the corerct value of 90%, and the other shows 168%. This is really weird - I've neve seen this before. Why is Excel showing the wrong value? I checking the calculation with my hand calculator, the correct value is 90%. When I cut and paste "values" to another cell, the number is the incorrect value of 168. ...

Formula to calculate an age from a date of birth
I'm looking for the formula to calculate the age from a date of birth. One way: =DATEDIF(A1,TODAY(),"y") where A1 contains the DOB. See http://cpearson.com/excel/datedif.htm for documentation on DATEDIF(). In article <FBF2C1DE-CFEF-442D-BD61-2C0CFCEFC682@microsoft.com>, Lisa C <anonymous@discussions.microsoft.com> wrote: > I'm looking for the formula to calculate the age from a date of birth. for more than just years, just expand. from xlfdic01.xls, if date is in D34... ="Age is "&DATEDIF(D34,TODAY(),"y")&" Years &...

How do I create a curved graph in excel?
I'm using MS Office 2003. I've created a simple x-y scatter plot graph. The graph line connecting the data points is a straight line. I'd like a curved line. I don't know that using the drawing tool bar will properly represent the data so I was wondering if there's another way. Hi, Double click the line and on the Pattern tab check the Smooth Line checkbox. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "clearandcorrect" <clearandcorrect@discussions.microsoft.com> wrote in message news:3541CA3D-2841-48F7-B722-B660AD7EFF2D@...

Select a query showing only selective post codes relating a relevant postal area
Hi please could you help as i would like to run a query only selecting relevant post codes from my database. The post codes i would like to select are from ST1 to ST4, ST6 to ST7. Thank you spending time to help this issue Chedd -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 I assume that those are the starting three characters of the post code and that the next character is a space. You could try a query using the criteria Between "ST1" and "ST4 zzzzzz" OR Between "ST6" and ...

Calculation not working
I have entered a RIGHT function, when i go to Insert, Function command and do RIGHT(G3,2), calculation shows up correctly in function box, but does not calculate in cell itself. Still displays command RIGHT(G3,2) What am I doing incorrectly? Have checked Tools, Options, Calculation, and Automatic is checked. Thanks in advance for your co-operation. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200602/1 Did you actually start the formula with an equal sign: =right(g3,2) If you did, try formatting the cell as General. Then select the cell, hit F2 an...

Filter a Reports calculated field
I have a report that draws data from various tables, and makes some calculations with information from the tables. I need to run a report that will automatically filter the calculated field eg. The report will only show the records > 10% Is this possible using SQL and if so how? Andy Gibson wrote: >I have a report that draws data from various tables, and makes some >calculations with information from the tables. > >I need to run a report that will automatically filter the calculated field >eg. The report will only show the records > 10% > >Is this possible ...

Plot the undulation curve and also to know rate of undulation
I'm working on a excel sheet where I need to plot a ground profile from the input distance vs elevation (x vs y). Now I need to give the starting point elevation and end point elevation with the rate of undulation which will produce the same as in normal chart with all the data from starting point to end points. ...

is it possible two different colours in a chart plot area ?
I need to distinguish two diffrent areas in the plot area of an excel chart. Is it possible to colour the plot area with two different colours for that purpose ? Thanks Yes, you can. Just double click your plot area and then use the "fill effect" dialog to do this. You can color your chart plot area with one/two or even a picture. Have fun! Thank you very much fro your response I am afraid I did not made myself understodd. The problem I have is that I measure the lenght od several piece parts and I want to plot those data in a chart but colouring the tolerance field in the ...

Crystal Report Formula for FIFO Costing Calculation
I am writing a report in V9.0 Crystal Reports and having an issue with a formula, hopefully someone can help. My situation is this: If I have a Quantity of 10 on an Order and my Receiving layers are as follows • 1/1/06 – Qty 2 – 10.00 • 1/5/06 – Qty 5 – 14.00 • 1/8/06 – Qty 20 – 16.00 What should happen is if this Invoice is the First Invoice Number or the earliest Invoice Number with this Item then upon Posting the Invoice it will Grab the 2 for 10.00, the 5 for 14.00 and 3 of the 20 for 16.00, giving me an Average cost of 13.80 per unit. I need the report to formulate the same concl...

Telephone Message Area in Outlook #2
I am unsure of which version of Outlook has the option where you can place telephone messages in which your co- workers/managers can view and edit. Is there a specific add-in required? I know that some companies have this function in Outlook, but I would like to find out if this is a created Form in Outlook or if it is a new option in the newer versions. ...

Excel Calculation Question
This may be easy... I am adding hours by day (m-f) & totaling at the end of the wor week... My total includes just the days (EX: =SUM(E6:K6)) But, here i my question, I want to add to the same cell in adition to standar hours, Overtime hours that are not calcuated in the Sum, but show i each individual cell? Can this be done -- Message posted from http://www.ExcelForum.com Hi Add into some range, p.e. E4:K4, standard hours for every day. Total hours without overtime are calculated with array formula (enter with Ctrl+Alt+Enter): =SUM(IF($E6:$K6<$E$4:$K4,$E6:$K6,$E$4:$K$4)) Total ...

Calculate the date of the next Friday dependent on the current dat
I am trying to write a formula that compares a tasks start date with the date of the next Friday depending on the current date. This will negate the need for Project Managers to update the status date in the project plans for my reports to be accurate. Can anyone help with a function that will calculate the date of the next Friday based on the current date. Any help much appreciated. -- Regards Chris Marriott Below are formula that will retrun the friday of the curent week assumming the week starts on Sunday. One that uses the project curent date the other uses th...

access calculation HELP!
when i create a query report i want to divide everyone salary by 24 how would i do that? Where do i go? What do I click? Salary is entered into a table named employeetable anyone please help! Create a query that contains all of the fields in your table. In the field that contains the salary, add /24 after the name of the field. That will create a calculated field that will be named Expr1 in the resultant recordset. Since Expr1 can be confusing, you might want to rename the field by putting a name followed by a colon (like Salary24:) in front of the expression you created. -- Doug Steel...

copy cell area between worksheets
Excel 2000 - How do i copy/paste a cell area contains images between worksheets? For example, cell area (A3: C8) contains 2 images. I want to copy and paste them to another worksheet (cell area A3:C8) with same cell sizes, format and images. However, after i do the paste, the format are changed. How can i do? mooscar hi, The images will have to be copied seperately because they are not attached to the cells. immages just sit on top of the cells and "float". as for the formats, have you tried to paste special formats. edit>paste special>choose formats>ok. >----...

Ytd Calculation In Pivot
Hello everybody. I am trying to simplifie a report, comparing sales and number o customers for 2 years, refreshing it every week (52 weeks per year). I tried to do it using a pivot and i have the following problem: If i add a calculated field, adding week 1+week 2+...+current wee (having in mind to add every week the running one), excel does not le me add more than 25 weeks because it can handle a specific number o characters in the calculated field, Does anyone has an idea how I can overcome this stupid thing? Any other suggstion would be greatly appreciated -- Message posted from http://ww...

What are the formula for calculating ROI all dates (Total return all dates)
From http://money.mvps.org/articles/portfolio_columns.aspx, it says that dividends should be included in calculation of ROI all dates(Total return all dates) So the formula for ROI should be ((market price - purchase price)+ dividend) / purchase price. Is this correct? However, what I am seeing is that ROI all dates is calculated as (market price - purchase price)/purchase price. Is there any other parameter that includes dividends also In microsoft.public.money, rvsw@hotmail.com wrote: > From http://money.mvps.org/articles/portfolio_columns.aspx, it says >that dividends should be i...

Calculated result in query problem
I have written a query (using SQL) that has in it a calculated field which in itself uses a subquery to perfrom a sum() function of a list of values. When I view the query results directly, the returned values are calculated correctly. I then have a report that uses this query as the data source. When I display this calculated result in the report, it is wrong. It is always a larger number in the report than in the query, as though it were being multiplied by some factor. The report does nothing fancy but display the results from the query. Has anyone else experienced this phenomenon? F...

How to increase chart area without affecting plot area?
How can I increase the chart area without increasing or reshaping the plot area itself? Forgive me if my terminology is wrong and confusing. By "chart area", I mean the white area around the plot area. By "plot area", I mean the area between the X and Y axes. My list of series is so large that it is clipped by the current chart area. When I drag the chart area handles, that also resizes the plot area. I want to avoid that. Hi, As far as I know the plot area is sized relatively to the chart area. If you increase the chart area the plot area will also grow. The only ...

How do I calculate interest?
I want to create a spreadsheet that shows my kids the power of compounded interest. I want to show them what regular deposits against a specific monthly interest rate results in, at the end of the year. I want to have the interest rate in cell A1 and starting in row 5, in column A I have the date of deposit and in column B I have the deposited amount. Then I want to have a total that calculates the updated balance including the compounded interest rate each month. Any suggestions? TIA! Robert "darkwing_duck" <rbrown999@gmail.com> wrote: > I want to show them...

Calculating Difference in days
I need to find the difference of the current date and an input value. It will be run in a query as a calculation and then used in a graph. Basically i'm trying to made a Gantt chart that will display 2 weeks back and 2 weeks ahead. The input value is [ExpectedDate]. Thanks Check Access HELP for the syntax on using the DateDiff() function. Regards Jeff Boyce Microsoft Office/Access MVP "cmraguilar" <cmraguilar@discussions.microsoft.com> wrote in message news:750FD1BC-9A87-4F37-9D86-370E205AEFD5@microsoft.com... >I need to find the difference of the current d...

Loan Calculation
Can I get some help with some loan calculation formula? I need to calculate payments based on 360/365/366/365.25 days in a year. That's one part. The other is, I also need to figure accurate payments if the days to first payment from date of contract is not the standard 30, say it is 1, 3, 10, 45, 60, 90, 100 etc, any number that the user can opt. How do I do this? Also I see some about last payment being different from the regular payments. WHat does that mean? Thanks Frank Hi, There are a load of financial functions built into excel. Have a look at the help pages for financial func...

email print area
I am able to email and save my print area. However when creating my form I adjusted the row and column settings. When the attachment to the email arrives the recipient views and prints 4 pages where the original print area covered one 8.5"x11"page. The same thing occurs when viewing and printing the saved copy. Hi Use Ctrl-End on the worksheet and see if it select you last cell with data. If not see http://www.contextures.com/xlfaqApp.html#Unused -- Regards Ron de Bruin http://www.rondebruin.nl "Raven506" <Raven506@discussions.microsoft.com> wrote in ...