Calculating or estimating the area between to curves

[Guest]

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!

 

[James Silverton]

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

 

[MrShorty]

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.