ERF () in excel to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ERF () in excel to SQL

Author

Topic

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-09 : 21:13:41

Hi Guys,

Does anyone knows how to do the Excel ERF() in SQL?

Below is the discussion of ERF on Excel....
ERF(lower_limit,upper_limit)
Lower_limit - is the lower bound for integrating ERF.
Upper_limit - is the upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit.

Remarks
If lower_limit is nonnumeric, ERF returns the #VALUE! error value.
If lower_limit is negative, ERF returns the #NUM! error value.
If upper_limit is nonnumeric, ERF returns the #VALUE! error value.
If upper_limit is negative, ERF returns the #NUM! error value.

I need this because our client gave us a spreadsheet that have computations on it using excel that looks like this...
=IF(($G94/(SQRT(2)*I$12))>27,H$12,H$12*ERF(G94/(SQRT(2)*I$12)))

My problem is how do I do the ERF(G94/(SQRT(2)*I$12)) part in SQL...

Thanks in advance...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-10 : 07:22:59

SQL has very little support for mathematical and statistical
functions. There is no built in error function. Your choices that I can think of are:

1. Calculate the data in Excel and use SQL as a place to store rather than to calculate.
2. Implement error function on your own in SQL (painful, debugging, testing etc).
3. Write a CLR stored procedure which calculates the error function. If you google for it, you will find C# implementations of error
function.
4. Write a CLR stored procedure and in the procedure use COM Interop to invoke excel's error function. You will need to have Excel installed on the server.

If I had to do it, my order of preference would be 1, 4, 3, 2.

Sunita

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 07:27:57

http://office.microsoft.com/en-us/excel/HP052090771033.aspx

E 12°55'05.63"
N 56°04'39.26"

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-10 : 08:56:06

Thanks guys, appreciate your replies.

sunitabeck, i'm looking forward doing 1. :)

Peso, yes i've read that already... but I still dont get how ERF() works in excel... :(
see below of the values that returns when there is an ERF and when it is omitted on the computation.
ERF(G73/(SQRT(2)*I$12)) = 0.086514479
G73/(SQRT(2)*I$12) = 0.076822321

ERF() is affecting the result by a change of 0.009692159

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 09:59:02

The link I provided tells you why and how ERF is calculated.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 10:55:14

Maths is not my strong suit but I found a ERF function in python:
http://stackoverflow.com/questions/457408/is-there-an-easily-available-implementation-of-erf-for-python/457475

I've converted it to T-SQL and it matches a few test examples I ran against Excel. This version takes a single input, but to work out a 2 part input is straight forward (according to another site): erf(a,b)= erf(a)-erf(b)

declare @Z decimal (18,10)
declare @t decimal (18,10)
declare @ans decimal (18,10)
set @z=0
--erf(a,b)= erf(a)-erf(b)
--so erf(1,0) = erf(1)-erf(0)=0.84270 -0
set @t = 1.0 / (1.0 + 0.5 * abs(@z))
-- use Horner's method
set @ans = 1 - @t * exp( -@z*@z - 1.26551223 +
@t * ( 1.00002368 +
@t * ( 0.37409196 +
@t * ( 0.09678418 +
@t * (-0.18628806 +
@t * ( 0.27886807 +
@t * (-1.13520398 +
@t * ( 1.48851587 +
@t * (-0.82215223 +
@t * ( 0.17087277))))))))))
if @z >= 0.0
Select @ans
else
select -@ans

--erf(0.076822321) = 0.0865154485
--which 0.0001 out from the value of 0.086514479
--which chrianth quoted from Excel.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-11 : 08:04:11

That was nice darkdusky... :)

Some of the values that I plugged in on your code exactly match the excel output but some are greater/less than (0.0000001+)....But I think I can leave with it for now and test it a little more and try to found out what is causing this...

Thank you very much.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-11 : 08:23:57

Cheers - there was a slightly different calculation on the same page of the link I referenced. You could try using those figures.
From searching yesterday I found that there are known bugs in the Excel ERF so perhaps this version is more accurate! - the guy who did the maths went to Princeton so he probably knows his maths.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 09:00:00

Here is an ERF-clone which will give you maximum accuracy of your choice.
It also works for SQL Server 2000.

http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 09:34:03

[code]SELECT dbo.fnErf(0.076822321, DEFAULT)[/code]returns 0.0865144799321197

E 12°55'05.63"
N 56°04'39.26"

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-12 : 04:55:44

That's very good Peso. It works well.

Thank you very much guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources