Abhishek Jha said:
Hi, I am working on calculating area under a Continuous Curve..
...
Hi all.
Abhishek seems to have lost interest, but for reference
the following is an easy way to calculate integrals in Excel,
without VBA nescessarily.
Define the names n, x and dx, most easily by running the macro below.
Suppose then we want to calculate (S being the integral sign):
1
S 4/(1+x^2)*dx
0
The result should be close to PI().
In 3 cells next to each other write
0 1 =SUMPRODUCT(4/(1+x^2)*dx)
Like SUMPRODUCT is standing in for the integral sign in the matematical formula.
The result is pi with 15 digits, by good fortune.
Only the 16. hidden digit is wrong.
One can unhide it by subtracting 3.
Hans T.
"""""""""""""""""""""""
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 polynomiums of max 3. degree there is full 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
'
Dim intervals&, integrationvariable$
''''''''''
intervals = 1000 'an EVEN number
integrationvariable = "x" 'r and c can't be used
'integrationvariable = "y" '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"
'.Names.Add "nn", "=TRANSPOSE(ROW(INDIRECT(""1:" & intervals + 1 & """)))-1"
' Uncomment next if number of intervals is in active sheet R1C1
'.Names.Add "n", "=ROW(INDIRECT(""1:"" & !R1C1+1))-1"
'.Names.Add "nn", "=TRANSPOSE(ROW(INDIRECT(""1:"" & !R1C1+1)))-1"
.Names.Add integrationvariable, "=!RC[-2]+n*(!RC[-1]-!RC[-2])/MAX(n)"
'.Names.Add integrationvariable, "=!RC[-4]+nn*(!RC[-3]-!RC[-4])/MAX(nn)"
.Names.Add "d" + integrationvariable, "=((n>0)+(n<MAX(n))+2*MOD(n,2))*(!RC[-1]-!RC[-2])/MAX(n)/3"
'.Names.Add "d" + integrationvariable, "=((nn>0)+(nn<MAX(nn))+2*MOD(nn,2))*(!RC[-3]-!RC[-4])/MAX(nn)/3"
'On Error Resume Next
'.Names("t").Delete
'.Names("dt").Delete
End With
End Sub
"""""""""""""""""""""""
8*E2:E8)/SUM(D2