Posts by benj

Re: Error when running histogram in Analysis Toolkit

sorry but the problem still exists. and the same error msg exists.

i have made reference in Microsoft Visual Basic to the .xls file holding Data Analysis as well. but somehow it doesnt solve the problem.

I have tried copying my data from col(a) to another new workbook and created a new macro with the line of code. the error still exists. so i really don't hv any idea what's the problem.

Re: Error when running histogram in Analysis Toolkit

how can i confirm if i hve the Analysis Toolpak - VBA installed when i run my code?

I'm just puzzled at why I can run the program smoothly before, but now all these errors are coming in.

Re: Error when running histogram in Analysis Toolkit

hi folks,

sorry about the issue... still hope to receive any feedback and assistance in the matter.

right now I still can't run the macro.

I did a little search on the net and found the following code from Microsoft Support
http:// http://support.microsoft.com/default.aspx?scid=kb;en-us;270844

i tried running it and it worked nicely. so now i'm quite puzzled at why my code was not working when it was working fine previously.

All along, the macro i recorded to generate a histogram is working fine until today when I tried running it again, it gives a 'Run-time error '1004' the macro " cannot be executed.

What could have been the problem?

Application.Run "ATPVBAEN.XLA!Histogram", Worksheets("Calculate").Range("A1:A" & lastrow) _
        , Worksheets("Calculate").Range("$G$1"), , False, False, False, False

any help pls?

Re: trendline gaussian

well i'm really grateful for all your help so far... i really learnt a lot here..

took a look at the workbook. i need to do a cdf of my input values. By a glance, the Gamma distribution of a=15 b=1 fits my curve, but will need to affirm it again.

say i want to include normal, gamma and the beta lines into my curve,
so am i right to say that i merely hv to include into my chart the following 4 lines
1) line of my input values
2) Gamma curve which seems to best fit (1)
3) Beta curve which seems to best fit (1)
4) Normal curve which seems to best fit (1)

and from there i do a visual comparison to see which one fits best to (1)?

Re: trendline gaussian

pls correct me if i'm wrong, i'm supposed to generate the pdf and cdf using the same set of data values but i will have to set the 4th input of each function to True for CDF and False for Pdf.

From what I see in oil.zip, i need to divide the input values by a scaling factor. Is there any way to determine this factor? what if i need to write this into vba code? will it be able to determine the factor each time the program is run or will the factor be fixed?

I'm just wondering, for values of alpha and beta, will I be able to dynamically determine their values each time i run my program that is written in vba? Or can these values be fixed for all cases of inputs?

Re: trendline gaussian

hi, while you were away, i tried to figure things out.. but ur previous post was a great help.. and i managed to come up with the attached file. but my excel file is too big so i only managed to post a screen shot of my graph. btw, my graph is a XY (Scatter)

wondering how can i generate the curves like you did in oil.zip, ie to have the curve depicting the input values and a few other curves showing gamma, sin curve etc? how do i generate the gamma and sin curve?

hi, i have this countif formula that i need it to run thru all the rows of data..

=COUNTIF(A1:A3772,"<"&A*) where * range from 1 to 1000

i wrote the following vba code but it give an 'application defined or object defined error'

Sub fill()
    Dim irow, lastrow As Integer
    Dim frmla As String


    lastrow = ActiveSheet.UsedRange.Rows.Count
    For irow = 1 To lastrow
	  frmla = "=COUNTIF(A1:A" & lastrow & ",<&A" & irow & ")"
	  ActiveSheet.Cells(irow, 4).Formula = frmla
    Next irow
	  
End Sub

anyone knows where went wrong?

Re: trendline gaussian

i got this off the net, but not so sure if it should be as such..

i shd use COUNTIF(A1:A3772,A1:A3772) for all the rows of data as it will give an accurate count of how many times a values appear in the whole list of data.

however, if i use tis, then i wont be able to determine the data point which u mentioned, which i interpretted as the values for the x-axis

Re: trendline gaussian

hi Alan,

sorry, but I looked thru the Countif function, not so sure how to set the criteria part of the formula.. as in you were sayingto find the number of records less than the data point, how to determine the data point? what i thot was like COUNTIF(A1:A1000, "<1000"). so does it mean that the criteria is determined upon how i want my x-axis to be?

Quote

3. Copy all three columns and paste special values

what special values do u mean? quite puzzled by this.

and yes, I do new to find the curve with the best fit.... from wat i have gathered from books, i need to use the Goodness of Fit Test like Chi-square test etc.

i'm sorry, tink i forgot to mention this at the start of my posts, but actually i need to implement all these in VBA code. but i suppose i can also record a macro to cover all these at the click of a button.

Re: trendline gaussian

yes, i understand your post.... just wondering, is there any excel functions or macros that allow me to

Quote

adding columns to the data that count the number of records less than each value then copying these counts and the raw data to new columns and sorting on the counts

??

and also how can i adjust the log function that you mentioned?

Re: trendline gaussian

hi

attached is the input data that I need to use. I'm supposed to use the Operational Risk Loss Distribution Approach (not sure if u r familiar with it) and using severity distribution which includes distributions like gamma, weibull etc and fit the data into a statistical distribution.

the x-axis will be the range of values of the input data while that of the y-axis will be the probability density.

Re: trendline gaussian

hi, thanks for ur v informative reply...

i'm just confirming whatever I have understood from your post to see if i'm right.

for the values of alpha and beta are chosen such that it best suits the required curve, which in the case of the Oil.zip is 'bell-shaped'.

just for curiousity sake, how did u scale down ur values for x and y axis?

well, not that i'm lazy to go and consult my teacher but this project is offered by an external agency to the school. my teacher is only checking on me, and it isn't his field of expertise.. as such, i nd to source ard on the net for advice. hope u understand

Re: trendline gaussian

ok think i understand the gamma distribution now, but still I'm juz curious about how the mean, sd, and scaling is obtained for gaussian-fit.xls? and why is scaling required? Must we do scaling for all sorts of distributions?

i don't really need to use gaussian distribution here but just wan to clarify my doubts. anyone?

then again, A., can pls enlighten me on how u choose the values of alpha, beta, scaling and x-shift?

your help is deeply appreciated.

Re: trendline gaussian

actually you are right, i'm completely new to this field and clueless about the terminologies...

i read from Excel Help that GammaDist requires 4 inputs, other than that, i cant really understand the explainations i got from the stats books that i got.

i have not plotted a gamma function before nor am i familiar wif transforming data to fit distributions or regression.

What I nd to do here is... i have a set of input values (1000 rows of data) however, I nd to visually see which distribution fits best to the input values that i have. as such i will nd to plot the the curve that holds my input values and ALSO the curve that depicts a standard gamma distribution in this case. *in other cases, i wil need to consider distributions like weibull and beta* the reason for choosing these 3 distributions is the requirements set out by my assignment.

not sure if i have provided enough info.

Re: trendline gaussian

hi i have looked at oil.zip and have a few queries to clarify.

1) how is alpha, beta, scaling and x-shift obtained?
2) y must the values from Col L41 onwards be multiplied by M6?
3) what is the purpose of col M of residual?

Re: trendline gaussian

so sorry.. i am refering to the file gaussian-fit.xls...

for each set of my data, i nd to do it like the excel file named above.. 1 line to depict my input data and the other to show the gaussian curve in this case of the file.

I have looked at NormDist... but pardon me, i'm completely new to this area, does the results generated by NormDist correspond to the results generated by the gaussian-fit.xls column E(which uses gaussian instead) so wld i be right to say that if i wan the curve generated by Col E to be of the nature of gamma distribution, i simply change the formula of column E to GammaDist and the corresponding inputs?

Re: trendline gaussian

3 questions...

1) how is the mean, sd and scaling found? by right, I thot it shoud be the mean will be average of B2 to B57. but the result is totally different.

2) what is the purpose of the column E and F? I cant arrive at a conclusion.

3) is i need to use it for other distributions like gamma, weibull etc, can i simply change the formula on column E to that of the respective distributions??

pls advise...

Re: trendline gaussian

we are talking about Normal(or Gauss) distribution here rite?

what i found off the net is that the formula is

f(x) = 1/(sqrt 2*pi) exp [-0.5 ((x-mean)/SD)^2]

Re: trendline gaussian

sorry, but how or where does the special number e come about?