....
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
''''''''''
