Printable View
Hi Everybody. First post here!
I'm developing a program that is using the Lognormal Distribution.
I need to write code in VB.net 2003 that does what the
LOGNORMDIST(x,mean,standard_dev) function in Excel does
(i.e. it returns the cumulative lognormal distribution of x).
If you know how to do this in VB please post an answer here.
Thank you in advance!
karkas
I can’t provide you the answer you asked for but you might find the two attached Excel files with formulas useful for the upcoming work.
Cheers.
Rassis thank you for your reply. I have done that in Excel already. I would like to do the same think in VB .net 2003. I couldn't find a function in VB that does what the LOGNORMDIST(x,mean,standard_dev) function in Excel do. What I'm looking for is a chunk of code (maybe) that can give me the area under the pdf of a lognormal distribution given the point (x) and the distribution's parameters (mean and std deviation). Any help on that will be very appreciated.
Regards
karkas
LOGNORMDIST uses NORMSDIST according to http://support.microsoft.com/default...b;en-us;828281
NORMSDIST is explained here
Pieter
Not interested in Excel. What I'm looking for is source code for a visual basic .net 2003 program. If you input LOGNORMDISTR(1,3,4) in VB it doesn't recognise it. Only Excel does, and I'm not interested in that (done it already). Anybody attempted to calculate the area under the pdf of a lognormal distribution in VISUAL BASIC, not in Excel?
What's area under the pdf mean?
and by lognormal I assume you mean ln (base e).
Pieter, thank you for your help. http://support.microsoft.com/?kbid=214111 gives me the formula of the approximate value of the CDF for the Normal distribution. I guess I can use that to develop something for the CDF of the LogNormal Distribution.
penagate check this to get answers for all your questions:
pdf = Probability density function
http://en.wikipedia.org/wiki/Probabi...nsity_function
Log-Normal Distribution
http://en.wikipedia.org/wiki/Lognormal
Oh right. I thought it was something else. In that case I can't really help much, sorry.
penagate thank you for your kind concern. It is not an easy task and if you haven't done anything like that, I suggest you avoid it (you will live longer for sure ;) )
Perhaps this example might help.
Several steel tubes used in the manufacturing of firing-guns were tested. The resulting failures were found to follow a LogNormal distribution with a mean of 7 and a standard deviation of 2. What is the probability of failure (CDF) of these tubes for a mission (x) of one thousand shots?
You can solve this three ways:
1. Using the LogNormal distribution in Excel:
CDF(1000) = LOGNORMDIST(1000; 7; 2) = 0,481606
2. Using the Normal distribution in Excel:
CDF(1000) = NORMDIST(ln(1000); 7; 2) = 0,481606
So, if you know the VB code for NORMDIST, this last form will be of help. If not, it will be a lot more difficult.
3. Using the integral of the pdf (probability density function):
There is no closed form for the LogNormal, therefore the integration has to be carried out using the attached expression for CDF (where miu subscript y is the mean and sigma subscript y is the standard deviation of the LogNormal distribution). If you would rather work with the Normal distribution you might use the attached conversion formulas where miu subscript x is the mean and sigma subscript x is the standard deviation of the Normal distribution.
http://img265.imageshack.us/img265/7...gnormal3tc.jpg
Rassis:
Thank you!! Very useful! Forgot about the basics!Quote:
.....
CDF(1000) = NORMDIST(ln(1000); 7; 2) = 0,481606
.....
I combined it with the info from
http://support.microsoft.com/?kbid=214111
which is an approximate way to calculate the integral , but unfortunately it only works with the Standard Normal Distribution (mean=0, std.dev=1)...Quote:
The NORMSDIST function returns the result of the standard normal cumulative distribution function for a particular value of the random variable X. The Excel function adheres to the following mathematical approximation, P(x), of the following standard normal cumulative distribution function (CDF)
P(x) = 1 -Z(x)*(b1*t+b2*t^2+b3t^3+b4t^4+b5t^5)+error(x), where
Z(x) = (1/(sqrt(2*pi()))*exp(-x^2/2))
t = 1/(1+px)
p = 0.2316419
b1 = 0.319381530
b2 = -0.356563782
b3 = 1.781477937
b4 = -1.821255978
b5 = 1.330274429
with the following parameters:
abs(error(x))<7.5 * 10^-8
The NORMSDIST function returns the result of the standard normal CDF for a standard normal random variable Z with a mean of 0 (zero) and a standard deviation of 1. The CDF is found by taking the integral of the following standard normal probability density function
Z(x) = (1/(sqrt(2*pi()))*exp(-x^2/2))
I'm using Numerical recipes and I'm currently working on writing a routine to calculate the integral. Here is a preliminary "unsophisticted" attempt.
VB Code:
What I get is the area under the pdf of the normal distribution with given mean (xm) and std. deviation (s) from negative infinity (i.e. -10000) to a point x (x). It works for now, but as I said is... well unprofessional and inaccurate. When I'm done I will post the code here for reference. Meanwhile if anybody can think of a nice way to do the integration please feel free to post here.
karkas
Take into consideration that Z is related to x, xm(mean) and s(std.dev.) through the expression Z = (x - xm) / s. This means that as soon as you get Z, you can proceed and calculate the integral of the CDF by using P(x) = 1 -Z(x)*(b1*t+b2*t^2+b3t^3+b4t^4+b5t^5)+error(x).
I wish you good code.
Rassis, tahnk you very much for all the help you provided.
Best
karkas
It's been a while but a promise is a promise so here is the code I developed (using the invaluable help of Numerical Recipes) to calculate the area under the pdf of a LogNormal distribution. This is given as a reference, use it at your own risk.
karkas
VB Code:
Congratulations Karkas. I appreciated your persistence very much.
Thank you Rassis! I hope my work can be of use to somebody. :wave:
Well! you people are doing great here. I have some what same problem. I have around 10000 records in sqlserver table; each belongs to a customer. I want to assign a random number to each customer. This number should be between 100 and 200 But the distribution should be normal with mean = 60 and s.d = p (say) . How can I use the above code to do the same.
I really want to be of assistance but I just can’t figure out what your aim is. If you have 10,000 clients and actually want to assign a random number to each of them, then you must have 10,000 numbers as well and not 200 – 100 = 100 only, unless you consider decimal numbers instead of integers. But it sounds nonsense and I can see no reason to do it. On the other hand, why do you want to sample from a Normal distribution with a mean of precisely 60?
However, please consider the following if you are to use EXCEL (VB code can then be found easily):
- You sample randomly any integer between two limits by doing =RANDBETWEEN(bottom;top);
- You sample randomly any number between two limits by doing =bottom+RAND()*(top – bottom);
- You sample randomly any number from the Normal distribution by doing =NORMINV(RAND();mean;standard_deviation).
Perhaps I might be able to help you a bit further if you explain in more detail what you actually want to achieve.
Rui
On Lognormals...
Hey all,
I need to integrate under a lognormal curve. However all I get is the pdf which gives an area of 1 (not surprising, I guess). I think I need to integrate with respect to x, rather than y.
- Does anyone know how to do this?
- Can anyone solve the lognormal for x?
cheers
ajmac
To integrate a lognormal function between 0 and x, you have to use the expression that is showed in post #10 above.
Hi guys, i know that a really long time is past from the essence of this post! but i just have a project going on and i tried every thing from manual formulation to read any article and book on the web!
im simulating the excel NORMSDIST in VB6, (i cannot use the worksheetfunction.NORMSDIST as the problem is we need to analyze more than 10000 values on each run and the time goes up like crazy by accessing excel).
I am done with every thing only i dont know wats the way to find ther error of x. i mean the point that we have to add up the absolute/relative error to the actual result we have taken.
when i compare my result with excel's one, the are lower than the excel's one around 0.000000007xx, you see that its the famous < 7.5 * 10 ^ -8. I have no idea how to get the value that is less than 7.5 * 10 ^ -8!
Any help is in advance appreciated! its taking 2 weeks of my time already.
In a nutshell, you used the the approximation from post #11 and you're trying to calculate error(x)? Well, you can't... not without calculating the whole thing with more precision in the first place, which requires a different method. It's not like error(x) is supposed to be calculated; it's just saying that the error term is always smaller than 7.5*10^-8. To be honest, that MS Support article isn't very well-written. I'd go to the original ("Handbook of Mathematical Functions," Abramowitz & Stegun, formula 26.2.17, page 932) for what's probably a slightly better exposition. I'm not sure if it would clear up any confusion or not, but Googling it might be worth a shot.
You seem to say that Excel's result is the standard you wish to compare your results to. Excel 2000 used the above formula, but switched to a more complicated but more accurate method in the 2003 version which gives results accurate to around 15 decimal places as opposed to around 8 with the above method. If you can find out the specifics of Excel's method, you could presumably implement it in VB6. Deriving such a thing is probably rather hideous, though. I certainly won't waste my time trying in such a well-studied area.
I'm a little skeptical that you need such high precision. The above article on the history of the NORMSDIST function is also skeptical, but (reading between the lines) it seems like the accuracy of enough other routines was bottle-necked by NORMSDIST that they put in a better NORMSDIST routine regardless of how directly useful such accuracy would be.
Assuming you have good cause to want such high precision, there are many things left to try. First, Wikipedia has a section on numerical computation of the standard normal CDF. This paper looks promising for implementing your own method. There are a hundred zillion ideas on computing the error function, which is the complicated part of computing NORMSDIST and from which you could relatively easily get a very accurate simulation of that function. Again, though, I'm really not sure you need all that accuracy (and complexity). Could we have more details?
jemidiah thanks for your kind reply.
Actually out of what you are saying im assuming that if i use Excel 2003 the result would be less precise and hopefully similar to mine.
The project im working on is going on for a while and using the excel results. My duty is to convert the code to get the exact same result in VB6. i looked through the internet and most of the jobs applied in this area are in VB .NET and the matter is i am working on VB6; which you know by the way a lot of differences are there, more than a version change.
Any way i have already looked through the internet and some of the links you sent i have already read and also the book. In the book at page 932, there is written |e(x)|<7.5 X 10 ^ -8 and that's it. i tried to trace it back to find it and there is also some sections about Error in computations and some formulas, but there are two matters to be considered:
1. Seems like that as you said, its not a simple calculation that you give it the X and take the result back; there is a range for minimum and maximum error. and also seems like that you must have a range of values as input to get the result. The matter is that what Excel is doing is just to take one Single value and give back the result.
I found also 5 other sources that introduce the NORSDIST function for VB, even a full module that you are just supposed to insert into your program and have fun! but that one also does not produce what i want. the most similar result im taking is from the from Microsoft that is written up there. and the result is just little bit smaller than the exception as i assume this is error(x). where in formula of Microsoft you can see the final add up on error(x).
2. Second matter is that i know i might seem little bit out of the scope, but believe me its because on not a math guy at all and im just deep in programming. I will appreciate if you can give me some help out here.
Another matter im thinking of is that the numbers as input are very precise. for instance one of them is similar to 0.99998887645521445.
Unfortunately seems like that VB6 does not like deep math like me! for example try this on VB6:
Dim x as double
x = 0.98754554544632156124 and press enter to go to next line
Boom! VB just rounds it into 0.987545545446322. Im afraid that this is happening even in run time of the program and so the result has no way to be more precise that more than 15 decimal points.
I dont have access to my work laptop right now im on my PC. as soon as i get there ill copy the code for you that compares and says the very tiny different between the values.
Thank you again man. I will post the code as soon as i can so that you can feel my situation!
The number 0.99998887645521445 can't be stored in the IEEE 754 double-precision format. The fractional part, 99998887645521445, when written in binary has 57 bits (in full, it's 1 01100011 01000100 01110101 01100000 00001011 10100110 00100101). The above standard can only store fractional parts with at most 53 bits (52 bits + implicit 1 bit at start; it's unimportant for you to know the intricacies of the standard). For an example using the IEEE 754 single-precision format in VB6, look here. You only get between 15 and 16 decimal digits of precision with the standard double-precision format. Your second example has 20, so it's no surprise VB cuts some off. I'm not sure how Excel internally handles numbers, but I would be surprised if it didn't use the same arithmetic as VB6, since processors perform floating point arithmetic with the 754 standard natively.
It seems like you don't necessarily want the true value of the standard normal distribution's CDF. You seem to just want to be able to emulate Excel 2003's value. I'll reiterate: Excel 2003 (and higher) does *not* use the method given in post #11 according to this Microsoft Support article. It uses another method, briefly described in the article as
I'm reiterating this becauseQuote:
The procedure in Excel 2003 and in later versions of Excel uses two different computational procedures depending on the value of z. The first is for z between -5 and +5; the second is for z values in the extreme left or right tails, below -5 or above +5. Accuracy was improved for all values since, over the range of z values where each was used, these two methods were both superior to the single method used in earlier versions of Excel. Typical accuracy is now 14 to 15 decimal places.
doesn't make sense in light of the above.Quote:
im assuming that if i use Excel 2003 the result would be less precise and hopefully similar to mine.
Even if you want to use the Excel 2000 method as quoted in post #11, you do not calculate error(x). The "error(x)" term is purely a notational device to tell you "if you calculate the other terms, 1 -Z(x)*(b1*t+b2*t^2+b3t^3+b4t^4+b5t^5), you'll be off by some error term which depends on x, where the error term is bounded in magnitude by 7.5*10^-8". It would make sense if you're using this Excel 2000 method and comparing your results to Excel 2003 (or later) that your results would differ after the ~8th or so decimal place. I'm reiterating this because
is talking about computing e(x), which just isn't correct.Quote:
In the book at page 932, there is written |e(x)|<7.5 X 10 ^ -8 and that's it. i tried to trace it back to find it and there is also some sections about Error in computations and some formulas, but there are two matters to be considered:
1. Seems like that as you said, its not a simple calculation that you give it the X and take the result back; there is a range for minimum and maximum error. and also seems like that you must have a range of values as input to get the result. The matter is that what Excel is doing is just to take one Single value and give back the result.
I'm going to guess that what you want is the algorithm Excel 2003 (and later) use to compute NORMSDIST. In a brief search, I haven't found the method they used. It's definitely possible the method hasn't been released, in which case you're pretty much out of luck without just calling the Excel version (which you said you don't want to do already). Sorry.
Any way thank you for your kind reply . at least now i know what im looking for is some how not feasible.
I will give another short try to find Excel's formula and not normal distribution formula.
any thing ill let you know. I still didnt get my laptop to show you my code sorry. i think tonight ill get it.
Thanks again man.
I finally got access to my work laptop. i uploaded the exe file.
this is just exe if you need the code also tell me to upload it (i didnt because its part of a very complicated and large code..) if you need should take it out and write in a different project and upload it.
any way after extract, run the file and in the upper text box write a number, press go button. then you will see a list of calculated NORMSDISTs!
there are 5 functions that are calculating, first one is excel one as is written there. if you press on the 'result' of the 2nd, 3rd, 4th and the 5th one, the difference of clicked one with the excel one will appear on the bottom textbox.
ill be glad if i hear your suggestions.
PS: as you can see the two last results are more similar to the excel one with slight different.
Unfortunately I don't have access to Excel so I can't run it. The code would be more illuminating anyway, but even then I'm not sure what I'd learn. Most likely you've found several methods to calculate the standard normal CDF, some of which happen to be closer than others to the method in Post #11 and/or the method used by Excel 2003 (and later), but none of which give exactly the result you want.
I wonder why you want to reproduce the Excel results. You could try to find a more accurate (but slow) method, or you could use a less accurate (but fast) method depending on exactly what you'll do with the results. Perhaps one of these would be good enough for your application?
This may not help but in VB6 if you want to have higher precision than double then use subtype Decimal of type Variant. Decimal can gives you up to 28 digits.Quote:
Dim x as double
x = 0.98754554544632156124 and press enter to go to next line
Boom! VB just rounds it into 0.987545545446322.
Code:Dim x As Variant
Dim y As Variant
x = CDec("0.98754554544632156124")
y = x / 2
Debug.Print y
Hi every one, first of all thanks for everyone help (and also anhn it was very helpful in what im doing now)
finally i found what i was looking for but the matter is that the code written in c. i tested it and it gives the exact result that i want. This code is part of project R, i got it from HERE
First of all i wanna know is there any way to link the c code in vb6? i mean i use the c code in vb6 without the need to convert it all into vb6 or no.
second option is converting...
this code is copyrighted by
* Copyright (C) 1998 Ross Ihaka
* Copyright (C) 2000-2002 The R Development Core Team
* Copyright (C) 2003 The R Foundation
in case of converting i am not clear about some parts. If you read the code mentioned in the link, there are some variables/functions that ill be glad to hear your help if you are familiar with them:
1. What are
in line 91Code:double ***, double *ccum, int i_tail, int log_p
2. how do i convert this lines into vb6:
in line 148Code:#ifdef NO_DENORMS
double min = DBL_MIN;
#endif
3. What does it mean:
in line 161Code:lower = i_tail != 1;
4. what's happening in
in line 196Code:#define do_del(X) \
xsq = trunc(X * SIXTEN) / SIXTEN; \
del = (X - xsq) * (X + xsq); \
if(log_p) { \
**** = (-xsq * xsq * 0.5) + (-del * 0.5) + log(temp); \
if((lower && x > 0.) || (upper && x <= 0.)) \
*ccum = log1p(-exp(-xsq * xsq * 0.5) * \
exp(-del * 0.5) * temp); \
} \
else { \
**** = exp(-xsq * xsq * 0.5) * exp(-del * 0.5) * temp; \
*ccum = 1.0 - ****; \
}
Thank you for your help guys.
I'm curious, are the results exactly the same for every digit on all inputs you've tested? Or are they just correct for many decimal places? I ask since the comment says it computes the error function to ~18 digits, which is very close to the Excel 2003 NORMSDIST documentation's ~15 digits.Quote:
Originally Posted by Luinox86
I forgot about R. It's a language for heavy duty math computations. It certainly makes sense they would have a good implementation of the routine you want. It might even make sense for Microsoft to borrow it; I dunno.Quote:
This code is part of project R
Yes, if you could compile the C code into a DLL, it could be called like any other API call. I've never done it myself; you'd probably get better help asking elsewhere on these forums with coding questions.Quote:
First of all i wanna know is there any way to link the c code in vb6?
*c um [space added to avoid profanity filter] is used to return the lower CDF, that is, the error function from negative infinity to x. *ccum is used to return the upper CDF, from x to infinity. i_tail specifies whether the lower, upper, or both CDFs should be computed and returned. I don't know what log_p is; you might look at the R documentation.Quote:
1. What are
in line 91Code:double ***, double *ccum, int i_tail, int log_p
I'm assuming you know that it's a C preprocessor compiler directive. The exact conversion to VB6 depends on what NO_DENORMS means. Presumably, it's explained in one of the included header files:Quote:
2. how do i convert this lines into vb6:
in line 148Code:#ifdef NO_DENORMS
double min = DBL_MIN;
#endif
Code:#include "nmath.h"
#include "dpq.h"
It's saying that, if i_tail is not 1, lower is True. If i_tail is 1, lower is False. Equivalent VB6 isQuote:
3. What does it mean:
in line 161Code:lower = i_tail != 1;
Code:lower = (i_tail <> 1)
I assume you're asking about the backslashes. They're line continuations, like the _ in VB6, but here used in a C compiler macro declaration. As for the algebra, I'm sorry, I'd have to puzzle through it and maybe read the paper referenced in the file comment to explain it, which isn't worth it.Quote:
4. what's happening in
in line 196Code:#define do_del(X) \
xsq = trunc(X * SIXTEN) / SIXTEN; \
del = (X - xsq) * (X + xsq); \
if(log_p) { \
**** = (-xsq * xsq * 0.5) + (-del * 0.5) + log(temp); \
if((lower && x > 0.) || (upper && x <= 0.)) \
*ccum = log1p(-exp(-xsq * xsq * 0.5) * \
exp(-del * 0.5) * temp); \
} \
else { \
**** = exp(-xsq * xsq * 0.5) * exp(-del * 0.5) * temp; \
*ccum = 1.0 - ****; \
}
Thank you so much jemidiah,
I was just reading an article about how to get the DLL and import it in VB, i hope it could lead some where... if i could get it done i will let you know.
anyway,
They are exactly the same, until the latest decimal digit. just Exact as the NORMSDIST in excel 2007!Quote:
are they just correct for many decimal places?
Thank you for translating the #ifdef command, i now know what to do with that.
About reading the documentation, yes that is possible but i was hopping that some one is already familiar with them, because for instance there was a variable DBL_EPSILON, i was hopping to find in the documentation, there are some thing like 130 files that this word has appeared more than 100times in them as the result of my search. (Just some hint, thanks to developers of a perfect software 'notepad++' you can search inside 130 files in less than 2 seconds! download it now if you have not!)
about the last part, thanks for your effort but my question is not about the back slashes, i have little background of C, the code is
As i remember in C such a line could appear in 2 ways,Code:#define do_del(X)
1. Prototype that is supposed to finish with a ';'
2. Definition that is supposed to end with a {, so the functions starts from.
i don't understand what #define do_del(x) does and i just doubt that it is saying that this function is included in one of the other files of the project and we may use it now!
what do you think about that?
Thank you so much, ps: i could write a simple C code and use it in VB6, i hope i can do the same thing on this HUGE code! wish me luck!
I am looking forward for your opinion man.
I agree, Notepad++ is really nifty. A coder friend of mine lists that and TortoiseSVN as the only two reasons to develop on Windows :).Quote:
Originally Posted by Luinox86
You could narrow your search greatly by looking for where the macro variable in question is actually defined, that is, whereQuote:
About reading the documentation, yes that is possible but i was hopping that some one is already familiar with them, because for instance there was a variable DBL_EPSILON, i was hopping to find in the documentation, there are some thing like 130 files that this word has appeared more than 100times in them as the result of my search. (Just some hint, thanks to developers of a perfect software 'notepad++' you can search inside 130 files in less than 2 seconds! download it now if you have not!)
and similar is used.Code:#define DBL_EPSILON
As for
it's defining a compiler macro. There's a decent discussion at the C preprocessor Wikipedia article. It means that every time the do_del routine is used, the preprocessor (before compilation) textually substitutes the body of the macro for each do_del call. It's a hideous feature (IMO), but in this case it allows the code to define the same function in one place without having to add the overhead of an extra function call. I believe they could have gotten all of those benefits along with better readability by defining an inline function, but ah well. That code is very clearly geared towards "it just works".Code:#define do_del(X)
Thank you very much jemidiah,
sorry for the late reply. my progress:
in the past few days i have been going deep inside some real huge systems! i have been trying to get Project R functions to be compiled in Visual Studio C++, if i can do so then i can generate the DLL and import it into VB and just pass this step.
Now for compiling the Project R code, i found out i need to compile it from a linux-based system that is much better-easier-more powerful than visual c++. To do so i needed to install Ubuntu on my machine!
I will let you know when i come out with any possible solution.
As talking about project R, i need to get the "Test statistic of D'agostino" in project R, i already found the 'dagostino' command in the package called 'moments'; as a first time shot on the package, i ran this command:
and the output is:Code:> set.seed(1234)
> X = rnorm(1000)
> skewness(X)
[1] -0.005202026
> agostino.test(X)
The matter is that what i need is only the p-value and the test statistic value for a 80 cells array.Code:D'Agostino skewness test
data: X
skew = -0.0052, z = -0.0446, p-value = 0.9644
alternative hypothesis: data have a skewness
I dont have much experience in Project R, any help is in advanced appreciated, how make the input to this function, a array of 80 elements, and how to get test statistic value for the range.
Thank you.
Sorry, unfortunately I don't know any R. I'm sure there are tutorials somewhere, though. I've also never used the statistic you mentioned, though it didn't seem like you needed anything related to it. I suppose using MS VC++ should work to avoid translation. I'm not sure how many differences there are (aside from libraries, which are probably entirely different) between regular C++ and VC++. When I glanced through the code you linked it didn't seem like it used anything very exotic, so if you can get the compiler macros working it seems like porting should be easy. However, that's a very rough guess. You're getting into systems I've only tangentially used. Good luck with them, though.
Thanks every one for the effort.
I solved the matter about a week ago. I wanted to upload the module here (encapsulated module) but the matter is that i have used some other peoples/company's/forums codes that I can not find the reference now; so i can not simply upload it.
I will as soon as I can do it.
Thanks again and enjoyed the forum ;)
Hi Luinox86,
Can you give me a copy of your code to calculate cumulative lognormal distribution?
This post has been long time ago, hope you can still see it. Thanks in advance if you can send it to me via e-mail address removed, please contact each other via our PM system
Hi, a few years past by but I actually could use this code as well! Ever received the code?Quote:
Originally Posted by adamleewiner
Appreciate any reply
Regards Robert