S
sustainability now
Is there an easy way to compute the area under a graph generated in excel 2007?
Thanks
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Hans Terkelsen
sustainability now said:Is there an easy way to compute the area under a graph generated in excel 2007?
Thanks
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.
H
Hans Terkelsen
....
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 =((n>0)+(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, "=((n>0)+(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
''''''''''
....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 =((n>0)+(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, "=((n>0)+(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
''''''''''
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.