How to calculate area under a line graph accurately

Paul Cooper

unread,

Jan 3, 2003, 4:34:47 AM1/3/03

to

I have an excel graph showing a series of data points, and
need to find the precise area under each point when joined
with a straight line from point to point. Can Excel
perform this form me?, and if so how????

Andy Pope

unread,

Jan 3, 2003, 6:16:24 AM1/3/03

to

Paul,

Have a look at Bernard's page, it should help.

http://www.stfx.ca/people/bliengme/ExcelTips/AreaUnderCurve.htm

Paul Cooper wrote:

--

Cheers
Andy

http://www.geocities.com/andy_j_pope/index.html

Jerry W. Lewis

unread,

Jan 3, 2003, 7:35:17 AM1/3/03

to

Specifically, the OPs request was for the Trapezoid rule, which is the
first method described on Bernard's page.

Jerry

Thomas Bartkus

unread,

Jan 5, 2003, 2:42:13 PM1/5/03

to

"Paul Cooper" <nu...@talk21.com> wrote in message
news:01e601c2b30b$5b508150$cef82ecf@TK2MSFTNGXA08...

I have a VBA routine to calculate that using the trapezoidal rule. I won't
post it here because a superior solution comes from David Braden who I hope
is still lurking around here.

' From David Braden
' Routine to calculate the area under the curve defined by XRange and YRange

'=SUMPRODUCT(OFFSET(RangeX,1,0)-RangeX,(OFFSET(RangeY,1,0)+RangeY)/2)

This is a cell formula that will give you the desired results directly.
[RangeX] and [RangeY] reference the cell ranges you are plotting.

I am relieved of the burden of exlplaining this formula because I don't
understand how it works! I vaguely *sense* that it is implementing the
trapezoidal rule. The only thing I know is that the results coincide
exactly with my more complex VBA looping procedure. If David Braden is
still out there, perhaps he will do us (me :-) the favor of explaining it.

--
Thomas Bartkus
www.BartkusConsulting.com

Jon Peltier

unread,

Jan 6, 2003, 9:41:30 AM1/6/03

to

Tom -

If I may jump in, let me explain the formula. SUMPRODUCT takes two arrays,
multiplies the corresponding values of each (first times first, second times
second, etc.), and adds the products. Trapezoid rule says compute the areas
of the trapezoids formed by points along the curve being integrated; this area
is 0.5*base*height, or base*average height, which is (X2-X1)*(Y1+Y2)/2.

In the formula, RangeX is the array of X(i) values, and OFFSET(RangeX,1,0) is
the array of X(i+1) values, so OFFSET(RangeX,1,0)-RangeX is the array of
widths. RangeY is the array of Y(i) values, and OFFSET(RangeY,1,0) is the
array of Y(i+1) values, so (OFFSET(RangeY,1,0)+RangeY)/2 is the array of
average heights.

Strictly speaking, if the plotted data is in RangeX and RangeY, this formula
uses one extra row in its calculation, and even if the row is blank, the
results will be incorrect. So make sure the appropriate range is included in
the SUMPRODUCT formula.

P.S. Tom - thanks for posting Dave's formula. I hadn't seen it before, nor
would I have thought of it, and it's much more elegant than a VBA solution I
would have come up with.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <u1AhEKPtCHA.2304@TK2MSFTNGP12>, tbar...@hotmail.com says...