Posts by A9192Shark

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: trendline gaussian


    Quote from Dave.Kimble@Liz

    Why did Hubbert use a probability function to fit a times series? It was a case of trying the easiest thing first.

    Not previously knowing anything about this science I have just done a web search. I know I am being pedantic, but Hubbert did not use a probability distribution to fit a time series. He most likely used simple arithmetic to calculate the data and then plotted the points on a graph and draw a curve through them that had a 'characteristic' shape. No formulae have been given (that I can find). To fit the curve subsequent analyses have used a Gauss or Normal curve, but it is not a distribution, it is simply a mathematical equation that 'fits' the data.

    The key words are 'probability distribution' vs 'curve fit' they are not the same.

    In fact the drawings of his original curve suggest that a very wide stdev is required to get the wide curve at the top of the curve, but there are the problems that the tails are ill defined. Can it be considered that oil production had a specific start date? I suspect that it is a long way before the data that is being analysed.

    I could keep going, because it intrigues me, but it will not help you solve your problem and it is not a primary issue for this forum (and I am sure there are many others more qualified in this subject to comment)!

    However, on the use of R2 have a look at this article it gives an example of why simply searching for a function taht fits is not necessarily the best approach.

    With respect to your problem I presume that you are doing an analysis for several data sets and will hen combine the curves to provide a revised estimate of the global or regional peak oil prediction. An interesting problem and one that does appeal to my analytical mind!

    With respect to your question on developing the solver, have a look at the attached workbook. You should only edit coloured cells. Note that the formulae used for the curves are all filled down the coloured columns that correspond to the graph and the titles of the curves.

    I have put starting values in that help the solver. It is clear that the solver has limitations, I had to do a lot of the adjustments manually, especially for the Gamma distribution.

    Note that you can not modify a cell other than the cell holding a function, therefore the solver will always need to be run as a macro.

    Good luck,



    Having opened the template I note that the four boxes you refer to all have the expression RecordSetClone.RecordCount in them, eg:

    =IIf([Print Invoice Time Subform].Form.[B]RecordsetClone[/B].RecordCount=0,0,[Print Invoice Time Subform].Form![Total Hours])

    I did a search on the MS Help and found that these are blocked in Sandbox mode.

    I have not come across Sandbox Mode before and can not advise on anything about it. If you do not get any further responses on this thread then open another thread asking about Sandbox mode.



    Re: import data from asp page into ms-excel

    Your subject and message contradict eachother. Do you want to Import or export?

    Assuming that you mean import from a web page then:
    Open the page in a web browser
    Open Excel with a blank workbook
    Select Data > Import External Data > New Web Query
    Copy the URL from the address bar in your browser and paste it into the address bar of the query browser.

    You should hopefully see the page in the query browser and alot of small arrows showing you the meta data areas that you can download. Click on the ones you want.

    Click OK.

    Note also that MS Excel already has a query for doing this that gets the data from MS Money web site.

    To use this make a list of your ticker labels in a single column.
    Select Data> Import External Data > Import Data and select the Stock quotes one.

    You will hopefully be able to follow through from the wizard.

    Good luck,


    Re: Excel object nesting

    Do you mean can you lock a graph to a cell? For example can yousize a graph such that it is the size of a cell and then lock it in place?

    If so then yes you can.

    Place the object on the worksheet. Select the object by left clicking. Then press Alt and drag the pull handles to size the object. The pull handles will align to the rows and columns on the sheet. Now when the cell is resized the object will be resized.



    Re: Relating the inventory to a specific location

    Tai Chi,

    Ceck your PMs, I have sent you my e-mail address. I think what you are asking is quite simple to implement (but perhaps difficult to explain). It will depend on how you have stored the data, but I think that we should be able to get a list of staff who have signed out a specific key (an the number that should be in the key locker).



    Re: Relating the inventory to a specific location


    Remove any reports and forms that do not relate to your problem. That will reduce the file size significantly.

    If that does not work (or you have already done it) then PM me and we will sort out an e-mail for you to send it to.


    Re: trendline gaussian


    Unfortunately my company will not let me view your web site so I can not see the examples you have pointed us to.

    The discussion on whether or not the missing SD is needed should be resolved by simply using teh NormDist function. You should use this for two reasons.
    1. It is a clear function that others can easily see what you have done. It will not have the same limitations of approximations (even if the impact is limited on teh results).
    2. Someone who looks at your worksheet may see the missing SD and question the validity of the analysis. Whilst you may be correct that teh scaling simply gets around the mathematical issues it does not get around the quality and confidence issues.


    Re: trendline gaussian


    I do not want to detract from your excitement, but are you using the normal distribution in a sensible manner?

    What you have done is:
    Assumed that the oil production is following a definable function f(x) where x is the year. You are trying to find that function, presumably to predict what the production would be in a given future year.

    Firstly this is a time series not a distribution. You are therefore not plotting the normal or Gaussian disribution, but plotting the curve defined by the same equation. Why is this important? A distribution is related to probability of occurrence, you are analysing a time series. To assume a distribution would be to assume that the oil production next year could be 3 000 000. Clearly this is contrary to the time series.

    I appreciate that you are trying to obtain a curve that best describes the data and would (presumably) allow a prediction of the future oil production.

    A few boundaries exist for the curve. As the year gets larger we assume production decreases (this assumes that no significant new reserves are tapped into). Also the production can not be less than zero. Several curves spring to mind as possibly describing the data. However we must understand why we think the data fits and what is the real world reason for the data fitting that curve.

    To undertake this analysis I would search for a comparitively simple curve. Have a look at the different types of trend lines that you can put on the graph. By allocating a 3rd order polynomial I think that you will probably get a much better fit. The question will then be "Why is a 3rd order poly the best fit to use?" It is not sufficient to say because it matches the data, we must understand why it matches the data.

    I suggest therefore that you takeone of the following approaches.
    1. Use the trend tool in Excel to examine the fit for many different types of curves as given in the chart tool.

    2. Transform your data such that it would appear linear. This is fraught with problems for anything but an easy transformation and I would avoid this.

    The key to understanding all of this is "Why does the data behave the way it does?" If you can not explain why production went up and then started to decrease then you have to be very careful of using a curve that may fit the data verywell now but is no good for forecasting.

    I hope that the above helps, I do want to see your analysis work, but it must be valid.



    PS (Ithink, but need to check, that your equation for normal distribution is missing a division by the standard deviation. I have used the NormDist() function in Excel.)

    Re: trendline gaussian

    Why are you doing this?

    If what you are actually trying to do is find out whether or not some observations are from a particular distribution then you should be doing the correct statistical test.

    For example. If you have 100 samples and you want to estimate the true mean and standard deviation of the population then we should be using a t-test (although if you have many more observations then we can simplify this).

    To do what you are trying to do would require either a transformation of the data or some sort of iterative solution as suggested. Although you would then be able to calculate an r-sq value you would still need to test if it was a significant value. You could start to build a worksheet to do this, but the actual test is much easier.

    If I can (time permitting) I will build a simple demo sheet.


    Re: Need help reducing execution time


    I have had another quick look. I am sure that this could be done witha few queries and then data output very quickly using a normal Access report. You appear to pass through one recordset (rsHead) and then add data into specific sections of the form from the appropriate tables.

    Can you post the mdb structure? If you remove all data apart from perhaps one head and the associated data I am sure that we can get this working properly such that run ti e is a few seconds.

    Why is the report output to a text file?


    Re: Need help reducing execution time

    Not sure how much of an improvement you will get as it is difficult to estimate without knowing what the data that is being output looks like.

    Here are some suggestions and modified code.

    1. Reduce the number of If statements in FPut
    2. Use Space rather than a while loop to add spaces to the numbers.
    3. Only output to a file if a new line is to be written.

    I am not sure if the way you have created a text file and then output to it is slower or faster than using the Writeln and Print statements.

    Also, I suspect that the fastest way would be to create the data in a report that is formatted to print exactly as you want. Even if all you did was create a single field that has all of teh data in it.

    Try this code and see if there is any improvement.

    Note, you may also find it useful to add some code that prints the run time to the debug window. Then you can also start to see where the time is being taken by printing it out every now and then from different procedures.

    Good luck,


    Re: Transpose table

    How much data are you working with?

    It could be a red herring, but if the data set is small then you might want to consider using the Excel Copy and Paste Special>Transpose.


    Re: Auto complete like winamp's media library


    Are you suggesting that the user types into a text box and as the user types the list box is updated?

    If so then I think you need to add an OnChange event to the list box that will refresh the list box record source. The List box record source would be a SQL statement that links to the text box.

    Have a look at the attached mdb.

    Try typing
    A and the list shortens to Alan
    *e and the list shortens to George and Neil

    Is this what you need?


    Re: Correlation Matrix in VBA


    Try this function. I think it should work but do not have suitable test data. If you have some suitable test data and known/expected results then I would test this a little more.

    Have you had a look at the Correlation matrix in the data analysis add-in? It may do the task for you.

    Re: Split address details


    What Will and I are saying is that every time you process data you need to compare the amount of time you spend coding and developing a process with how long it would take you to do it manually. I think that the trick with this data is to develop something that gets you 50% say quickly, but the remainder requires work.

    To answer your questions:


    If I had two tables with City and County names. Would it be possible to match these fields to the address field so I could determine which city or county each address is in?

    This might help in some cases, but what about if the address is on Guildford Road, or the address gives the name of a house or estate/subburb that could be misinterpreted. You could spend ages coding tocatch all of these possibles and actually not help the overall process.


    I have to say I am bit confused by your solution using a word processor. Could you explain in a bit more detail?

    The method basically assumes that you remove the commas and replace them with another character to delimit the different lines of the address.

    If this is genuinely a large job that will be done frequently then you may want to research 3rd party software as Will has suggested. This may at least help you get many more correct. Nonetheless, I suspect that you will still have to have a human to look at the results and correct any mistakes.

    I think that your solution needs a little more thought by myself before an acceptable solution can be found.