Is there an easy way to compute the area under a graph generated in excel 2007?
Thanks
--
jeff

"sustainability now" wrote in message
...
Is there an easy way to compute the area under a graph generated in excel 2007?
Thanks
--
jeff

Hi Jeff.

Yes, there are ways.
It depends a little on your data.

If your data are XY data,
with X in A1:A10 and Y in B1:B10
then the area under the graph is
=SUMPRODUCT((A2:A10-A1:A9)*(B2:B10+B1:B9)/2)

This is supposing you have straight lines between the points.
X coordinates must be in sequence, but don't need to be equidistant.

A definite integral in the math sense is the area under the functiongraph.

If you are really asking how to do an integral of a math expression,
then there is an easy way witout the dataset,
using one cell, no VBA, giving high precision.
But that may not be what you are asking for.

Hans T.

sustainability now wrote:
Is there an easy way to compute the area under a graph generated in excel 2007?
Thanks

I don't know if this is "easy" but one way to go about it would be to
sum the areas of each parallelogram bound above by adjacent chart points
and below by the x-axis. All the data you need is in the chart source.

On the other hand, if your chart is created by a mathematical equation
all you really need is to figure out the integral of said equation. This
might apply if you are looking at a trendline based on your data.

Take a look at these:

http://en.wikipedia.org/wiki/Integral

http://answers.yahoo.com/question/in...3044149AAaqSix

....
On the other hand, if your chart is created by a mathematical equation
all you really need is to figure out the integral of said equation. This
might apply if you are looking at a trendline based on your data.
....
Hi S.Martin.

Jeff seems satisfied,
but perhaps you or someone else will appreciate this method of integration in Excel.
It is easy to use.

Briefly, three cells next to each other are used, the first two hold the integral limits.
The last one has the integral, with integration sign replaced by =SUMPRODUCT( )
Otherwise not much change
High precision, no VBA, nescessarily.

Three names are required in the workbook.
Most easily defined by the macro below, "DefineNamesForNumericIntegration".
(the names n, x, dx, will be vectors calculated from the limits)
Run that macro.

As an example, calculate
(math notation, S being the integralsign here in the newsreader)

1
S 4/(1+x²)*dx
0
(The answer should be pi)

In XL then 3 cells next to each other

0 | 1 | =SUMPRODUCT(4/(1+x^2)*dx)
Done!

The Sumproductcell 3.14159265358979(4)
Excel's PI() 3.14159265358979(3)
(see the hidden 16. digit by subtracting 3)

The method can be quite accurate.

Most expressions can be integrated that way,
but some are better suited than others.

Hundreds of integrals in a sheet, e.g. for a graph, could be slow.

Excel has many engineering functions, but no Integral button.
This way is useful instead.

Hans T.


Here is the macro, if one doesn't want to define the names manually:

''''''''''
Sub DefineNamesForNumericIntegration()

'By default the names n, x, dx.

'For "Definite Integral" calculation in an Excel workbook:
'To calculate (S being the integralsign)
'
'b
'S f(x)dx
'a
'
'The integral from x=a to x=b of f(x) is calculated (using the names defined by the macro) by
'3 cells next to each other
'a | b | Matrix formula {=SUM(f(x)*dx)} or the formula =SUMPRODUCT(f(x)*dx)
'
'Problem example:
'
'pi/2
'S sin(x)*dx (should give 1)
'0
'
'3 cells next to each other
' ______________________________________
'| 0 | =PI()/2 | =SUMPRODUCT(sin(x)*dx) |
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'gives 1.00000000000003

'The names n, x, dx are vectors, calculated from the limits a and b
'a to b is split into intervals, indexed by n
'The intervals are weighted by "Simpsons rule", which approximates with parabola pieces
'Step functions like INT may therefore be uncorrectly rounded near the step points
'A larger number of intervals can improve accuracy
'For 'nice', smooth, not too steep functions and b-a not too big, the error is at the last digit
'
'NB. Some functions, SUM, MAX, MIN, ...CONCATENATE, BESSEL ,.. can not deliver an array
'Beware of them in the integrand expression
'
'Without this macro the defined names and references might be
'n =ROW(!$1:$1001)-(RAND()<1)
'x =!A1+n*(!B1-!A1)/MAX(n)
'dx =((n0)+(n<MAX(n))+2*MOD(n,2))*(!B1-!A1)/MAX(n)/3
'IF active cell is C1 while the names are defined
'
'Hans T.

Dim intervals&, integrationvariable$
'''
intervals = 1000 'an EVEN number
integrationvariable = "x" 'r and c can't be used
'name for differential of "x" will automatically be "dx"
'''
With ActiveWorkbook

'Uncomment next for own choice of variable name
'integrationvariable = InputBox("Define new integrationvariable?" & vbCr & "Not r or c.", .Name, "x")
If integrationvariable = "" Then Exit Sub

..Names.Add "n", "=ROW(INDIRECT(""1:" & intervals + 1 & """))-1"

' Uncomment next if number of intervals is in active sheet $A$1
..Names.Add "n", "=ROW(INDIRECT(""1:""&$A$1+1))-1"

..Names.Add integrationvariable, "=!RC[-2]+n*(!RC[-1]-!RC[-2])/MAX(n)"
..Names.Add "d" + integrationvariable, "=((n0)+(n<MAX(n))+2*MOD(n,2))*(!RC[-1]-!RC[-2])/MAX(n)/3"

'On Error Resume Next
'.Names("t").Delete
'.Names("dt").Delete
End With
End Sub
''''''''''