Check This Out Adding The Ei (Exponential Integral) Function to Excel
The exponential Integral – Ei, is widely used in science and engineering for the calculation of system characteristics when it undergoes a transient behaviour. In oil and gas engineering, the exponential integral is used to compute pressure distribution inside or near an oil and gas well bore when it is infinitely acting (the well drains an infinite area such that P tends to Pi as r tends to infinity)
The exponential integral is given as: Ei(x) = -[int(-x,inf)(exp(-u)*(1/u)*du)], where x>0
In Microsoft excel 2007, 2010; this function is not part of the built in functions, thereby causing engineers to resort to other computational tools for analysis of their field data and under utilizing the rich computability power of excel. The code below shows how a User Defined Function (UDF) for the Exponential integral could be added to Microsoft Excel using VBA (Visual Basic for Applications) that is packaged with Microsoft office products.The Ei function is based on the Series representation, Continued fraction algorithm and Asymptotic expansion for certain range of values.
POINTS TO NOTE ABOUT THE EXPONENTIAL INTEGRAL: Ei(x)
This exponential integral function, Ei(x) should not be confused with the standard definition: Expint(n, x) where n=1, 2, 3,… and x>0. Nevertheless, there is an added function to this code, Expint(x), which is used to calculate the Expint(n, x) when n=1. In such case, x should be greater than 0.
When computing Ei(x), x could take real values (x could be positive or negative); but when computing Expint(x), x can only take positive real values (x>0).
To compute Ei(-x); one of its application in Well Testing (Petroleum Engineering), the argument x is to be negative on the Microsoft Excel spreadsheet.
Steps:
– Enable the VBA developer tab (if not enabled) in the microsoft excel ribbon
click on the office button
click on the Excel Options button: This opens the Excel options dialog
on the popular pane, tick: Show Developer tab in the ribbon
click on OK to close the dialog: This shows the developer tab in the ribbon
– Click on the Developer Tab, then click on Visual Basic (Alt + F11): This opens the Microsoft Visual Basic Window
– Click on the Insert menu in the menu bar, then click on Module in the pull down menu of the insert menu: This opens the module code window.
– Copy the text from this PDF: Ei(x) Function.pdf and paste it in the module window (this function is highly accurate even to the 15 significant digit and more, but limited by Excel’s maximum cell precision value of 15), or copy the text below (The text below might be distorted). There is a simple guide on YouTube via http://youtu.be/EyEjvX5OnoA . Also, free download of a macro-enabled workbook that calculates the Ei function at the end of this post. ‘================================================================================ Public Function Ei(x As Double) As Variant
Dim i, n, nmax As Integer
Dim sum, fact, nterm, prevterm, ll As Double
Dim a, b, c, d, h, del As Double
Const EPS As Double = 0.000000000000002 ‘EPS is the floating point precision; ‘an approximation of the significance of the nth term in the sum of all the terms
‘FPMIN is a number near the smallest representable positive floating-point number; for Microsoft Excel, the
‘smallest representable positive floating-point number is 4.94065645841247E-324
Const FPMIN As Double = 4.9E-308
Const EulerMascheroni As Double = 0.577215664901533
ll = -Log(EPS)
Select Case x
Case 0#
Ei = “-inf”
Case 0# To ll
sum = 0#
fact = 1#
nmax = 100
For i = 1 To nmax
fact = fact * (x / i)
nterm = fact / i
sum = sum + nterm
If nterm < sum * EPS Then
Exit For
End If
Next i
Ei = EulerMascheroni + Log(x) + sum
Case Is > ll
sum = 0#
nterm = 1# ‘Value of the first term
nmax = 100
For i = 1 To nmax
prevterm = nterm
nterm = nterm * i / x
If nterm < EPS Then ‘Since the final sum will involve adding 1, it will be
GoTo 2 ‘greater than 1. Therefore, the “nterm”
Else ‘could act as an approximation for the relative error.
If nterm < prevterm Then ‘Expansion still converging: add new term.
sum = sum + nterm
Else ‘Expansion diverging: Subtract previous term and exit
sum = sum – prevterm
GoTo 2
End If
End If
Next i
2: Ei = Exp(x) * (1# + sum) / x
Case -1# To 0#
x = -x
sum = 0#
fact = 1#
nmax = 100
For i = 1 To nmax
fact = fact * (-x / i)
nterm = fact / i
sum = sum + nterm
If Abs(nterm) < Abs(sum) * EPS Then
Exit For
End If
Next i
Ei = -(-EulerMascheroni – Log(x) – sum)
Case Is < -1# ‘Lentz’s algorithm
nmax = 100
x = -x
n = 1
b = x + n
c = 1# / FPMIN
d = 1# / b
h = d
For i = 1 To nmax
a = -i * i
b = b + 2#
d = 1# / (a * d + b) ‘Denominators cannot be zero.
c = b + a / c
del = c * d
h = h * del
If (Abs(del – 1#) < EPS) Then
Ei = -(h * Exp(-x))
Exit For
End If
Next i
End Select
End Function
‘=====================================================================================================
‘An added function to compute the Exponential Integral, E(n,x) when n=1
‘when n=1; E(n,x)=E1(x)
‘This function EXCLUSIVELY defines E1(x) as Expint(x)
Public Function Expint(x As Double) As Variant
If x = 0 Then
Expint = “inf”
Else
If x < 0 Then
Expint = “NaN” ‘x>0
Else
Expint = -Ei(-x) ‘Ei(-x)=-E1(x) by analytic continuation; therefore, E1(x)=-Ei(-x)
End If
End If
End Function
‘=================================================================================
– Click on the save button in the standard tool bar – Close the Visual Basic Window
– Now go to any cell within the work sheet and type Ei(x), where x should be a real number, then press enter. The Ei value of the argument x will be imputed in that cell.
-To compute Expint(x), go to any cell within the worksheet and type Expint(x), where x should be a positive real number, then press enter. The Expint(x) value of the argument will be imputed in that cell.
This would be very useful to you because you could input the arguments in one column and get corresponding Ei values in the next column
Note: This works for only that particular work book; therefore, the workbook must be saved as a macro enabled workbook and anytime computation are to be done, the work book should be used. A way out of this is to save that particular work book as the default work book in the StartUp folder of Microsoft Excel so that any time the Excel application is launched, the workbook is automatically activated. That’s for another day!
Also ensure to enable macro in the Developer’s Tab: Go to macro security button, then tick the best option that suits you.
Free download of an excel macro workbook that calculates the Ei function at here: Ei Function Mod-1.1.xlsm. This work is highly accurate even to the 15 significant digit and more but limited by Excel’s maximum cell precision value of 15.
Free download of the previous workbook that calculates the Ei function at here: Ei Function Mod.xlsm. This workbook is still accurate on the average, to 8 significant figures.
References:
Press W.H., Teukolsky S. A., Vetterling W. T. and Flannery B. P. 1986,Numerical Recipes in Fortran 77, The Art of Scientific Computing, 2nd Ed., Vol 1, (Newyork: Cambridge University Press), p. 216-220
Adewole, E.S. and Bello, K.O. 2004, Application of the Gauss-Laguerre Quadrature for Computing the Exponential Integral Functions, (Ei), Society of Petroleum Engineers (Nigeria), SPE 98829, pp. 2.
Abramowitz, M., and Stegun, I.A. 1964, Handbook of Mathematical Functions, Applied Mathematics Series, Volume 55 (Washington: National Bureau of Standards; reprinted 1968 by Dover Publications, New York), Chapter 5.
The exponential Integral – Ei, is widely used in science and engineering for the calculation of system characteristics when it undergoes a transient behaviour. In oil and gas engineering, the exponential integral is used to compute pressure distribution inside or near an oil and gas well bore when it is infinitely acting (the well drains an infinite area such that P tends to Pi as r tends to infinity)
The exponential integral is given as: Ei(x) = -[int(-x,inf)(exp(-u)*(1/u)*du)], where x>0
In Microsoft excel 2007, 2010; this function is not part of the built in functions, thereby causing engineers to resort to other computational tools for analysis of their field data and under utilizing the rich computability power of excel. The code below shows how a User Defined Function (UDF) for the Exponential integral could be added to Microsoft Excel using VBA (Visual Basic for Applications) that is packaged with Microsoft office products.The Ei function is based on the Series representation, Continued fraction algorithm and Asymptotic expansion for certain range of values.
POINTS TO NOTE ABOUT THE EXPONENTIAL INTEGRAL: Ei(x)
This exponential integral function, Ei(x) should not be confused with the standard definition: Expint(n, x) where n=1, 2, 3,… and x>0. Nevertheless, there is an added function to this code, Expint(x), which is used to calculate the Expint(n, x) when n=1. In such case, x should be greater than 0.
When computing Ei(x), x could take real values (x could be positive or negative); but when computing Expint(x), x can only take positive real values (x>0).
To compute Ei(-x); one of its application in Well Testing (Petroleum Engineering), the argument x is to be negative on the Microsoft Excel spreadsheet.
Steps:
– Enable the VBA developer tab (if not enabled) in the microsoft excel ribbon
click on the office button
click on the Excel Options button: This opens the Excel options dialog
on the popular pane, tick: Show Developer tab in the ribbon
click on OK to close the dialog: This shows the developer tab in the ribbon
– Click on the Developer Tab, then click on Visual Basic (Alt + F11): This opens the Microsoft Visual Basic Window
– Click on the Insert menu in the menu bar, then click on Module in the pull down menu of the insert menu: This opens the module code window.
– Copy the text from this PDF: Ei(x) Function.pdf and paste it in the module window (this function is highly accurate even to the 15 significant digit and more, but limited by Excel’s maximum cell precision value of 15), or copy the text below (The text below might be distorted). There is a simple guide on YouTube via http://youtu.be/EyEjvX5OnoA . Also, free download of a macro-enabled workbook that calculates the Ei function at the end of this post. ‘================================================================================ Public Function Ei(x As Double) As Variant
Dim i, n, nmax As Integer
Dim sum, fact, nterm, prevterm, ll As Double
Dim a, b, c, d, h, del As Double
Const EPS As Double = 0.000000000000002 ‘EPS is the floating point precision; ‘an approximation of the significance of the nth term in the sum of all the terms
‘FPMIN is a number near the smallest representable positive floating-point number; for Microsoft Excel, the
‘smallest representable positive floating-point number is 4.94065645841247E-324
Const FPMIN As Double = 4.9E-308
Const EulerMascheroni As Double = 0.577215664901533
ll = -Log(EPS)
Select Case x
Case 0#
Ei = “-inf”
Case 0# To ll
sum = 0#
fact = 1#
nmax = 100
For i = 1 To nmax
fact = fact * (x / i)
nterm = fact / i
sum = sum + nterm
If nterm < sum * EPS Then
Exit For
End If
Next i
Ei = EulerMascheroni + Log(x) + sum
Case Is > ll
sum = 0#
nterm = 1# ‘Value of the first term
nmax = 100
For i = 1 To nmax
prevterm = nterm
nterm = nterm * i / x
If nterm < EPS Then ‘Since the final sum will involve adding 1, it will be
GoTo 2 ‘greater than 1. Therefore, the “nterm”
Else ‘could act as an approximation for the relative error.
If nterm < prevterm Then ‘Expansion still converging: add new term.
sum = sum + nterm
Else ‘Expansion diverging: Subtract previous term and exit
sum = sum – prevterm
GoTo 2
End If
End If
Next i
2: Ei = Exp(x) * (1# + sum) / x
Case -1# To 0#
x = -x
sum = 0#
fact = 1#
nmax = 100
For i = 1 To nmax
fact = fact * (-x / i)
nterm = fact / i
sum = sum + nterm
If Abs(nterm) < Abs(sum) * EPS Then
Exit For
End If
Next i
Ei = -(-EulerMascheroni – Log(x) – sum)
Case Is < -1# ‘Lentz’s algorithm
nmax = 100
x = -x
n = 1
b = x + n
c = 1# / FPMIN
d = 1# / b
h = d
For i = 1 To nmax
a = -i * i
b = b + 2#
d = 1# / (a * d + b) ‘Denominators cannot be zero.
c = b + a / c
del = c * d
h = h * del
If (Abs(del – 1#) < EPS) Then
Ei = -(h * Exp(-x))
Exit For
End If
Next i
End Select
End Function
‘=====================================================================================================
‘An added function to compute the Exponential Integral, E(n,x) when n=1
‘when n=1; E(n,x)=E1(x)
‘This function EXCLUSIVELY defines E1(x) as Expint(x)
Public Function Expint(x As Double) As Variant
If x = 0 Then
Expint = “inf”
Else
If x < 0 Then
Expint = “NaN” ‘x>0
Else
Expint = -Ei(-x) ‘Ei(-x)=-E1(x) by analytic continuation; therefore, E1(x)=-Ei(-x)
End If
End If
End Function
‘=================================================================================
– Click on the save button in the standard tool bar – Close the Visual Basic Window
– Now go to any cell within the work sheet and type Ei(x), where x should be a real number, then press enter. The Ei value of the argument x will be imputed in that cell.
-To compute Expint(x), go to any cell within the worksheet and type Expint(x), where x should be a positive real number, then press enter. The Expint(x) value of the argument will be imputed in that cell.
This would be very useful to you because you could input the arguments in one column and get corresponding Ei values in the next column
Note: This works for only that particular work book; therefore, the workbook must be saved as a macro enabled workbook and anytime computation are to be done, the work book should be used. A way out of this is to save that particular work book as the default work book in the StartUp folder of Microsoft Excel so that any time the Excel application is launched, the workbook is automatically activated. That’s for another day!
Also ensure to enable macro in the Developer’s Tab: Go to macro security button, then tick the best option that suits you.
Free download of an excel macro workbook that calculates the Ei function at here: Ei Function Mod-1.1.xlsm. This work is highly accurate even to the 15 significant digit and more but limited by Excel’s maximum cell precision value of 15.
Free download of the previous workbook that calculates the Ei function at here: Ei Function Mod.xlsm. This workbook is still accurate on the average, to 8 significant figures.
References:
Press W.H., Teukolsky S. A., Vetterling W. T. and Flannery B. P. 1986,Numerical Recipes in Fortran 77, The Art of Scientific Computing, 2nd Ed., Vol 1, (Newyork: Cambridge University Press), p. 216-220
Adewole, E.S. and Bello, K.O. 2004, Application of the Gauss-Laguerre Quadrature for Computing the Exponential Integral Functions, (Ei), Society of Petroleum Engineers (Nigeria), SPE 98829, pp. 2.
Abramowitz, M., and Stegun, I.A. 1964, Handbook of Mathematical Functions, Applied Mathematics Series, Volume 55 (Washington: National Bureau of Standards; reprinted 1968 by Dover Publications, New York), Chapter 5.
No comments:
Post a Comment
Comment Here