Posts by A9192Shark

Re: trendline gaussian

Benj,

Quote from benj

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?

OK, let's take a step back. What do you know about the Gamma function? These questions suggest to me that you have not plotted one before and you are not familiar with transforming data to fit distributions. Also, question 3 suggests that you are not familiar with regression.

Can you please tell me what you are actually trying to do. There is a danger that you are in a rabbit warren here and I do not want us to get lost.

What data do you have and what are you trying to do?
Why Gamma?

A.

Re: Ranking alphanumeric data

Of course! How many times have I done something similar to that????

Don't you hate it when you know you should have known it!

Thanks,

A.

Re: trendline gaussian

Benj,

I can not comment much more on taht file. I suggest that you have a look at the file that I posted (Oil.zip) that has been accepted by Dave as a solution to his problem.

The Normal curve is the same as the Gaussian distribution.

To determine the mean and stdev you need to first make sure that you are working on the correct data. Can you post a sample of your data and a description of exactly what it is you need to do?

A.

Hello All,

Just checking before I go and code it has anyone written a function for ranking alpha numeric data? In otherwords without actually sorting the data in Excel provide the index of a value in the sorted list.

Thanks,

Alan.

Re: trendline gaussian

Benj,

Give us some help here, this is a long post with at least three different Excel files uploaded. Which file are you looking at? What are you actually trying to do?

Your previous questions suggest that you are using teh Normal distribution. Have you looked at the Excel help for NormDist and NormInv (and then GammaDist and GammaInv)?

Also be aware that these solutions use the solver within Excel. The solver has limited capability with these functions and there is a need to 'help' teh solver by providing a good starting point.

How can we help you?

A.

Re: Query to retrieve 6 records of query

Barry,

Crucially you state that the data in the mdb is updated once every 24hours. Presumably after that the Excel work book is opened to look at the data and do what ever it needs to do. Therefore the workbook is looking at a static database. This is good.

Now, what are the key fields that it needs to be sorted on? Create a Select query that selects all of the records in teh correct order.

In the query designer change the query type to a Make Table and enter the name of the table, call it something like "tbl Order of Data for Analysis".

Execute the query by clicking the red exclamation mark on the toolbar.

Change the query to an append query for the same table.

In the database window open teh table in design view and insert a field called IDSortOrder or something similar. Make the field an autonumber field.

Close and save the table design.

Now we can design a query based on this table that will identify the six records that you want given the IDSortOrder. The query will be a select query that uses a where clause based on the IDSort Order.

It is difficult to explain without the table structures, but I hope taht you have understood.

If not then open a new database.
Import the data tables that we are interested in (I think you can do this and only copy the structure). From what you have said this is only two tables.

This file should easily zip below the 45kb value.

I (or someone else) will create the extra tables etc and post back.

Then we need to solve the problem of running from Excel.

Cheers,

Alan.

Re: Query to retrieve 6 records of query

Good morning Gents,

I am back after a weekend off- looks like you have been busy!

Two things.
A. A 'record number' in an Access recordset is not consistent between sessions of that record set. For example, if you have a recordset that contains the members of your family, then you may be the first record when it is opened the first time, but the third record the next time. To get around this there are two methods.

B. Autonumber fields when set to sequential solve A. Sorting records solves A to a point.

You need a query that will calculate the sequential number of the record and store it. I think that we should be able to do this, but it may be a long query if you have lots of records.

I am sure that the best approach is to use code or....

How many times does the data change?

Assuming that you are looking at a static data set (and not a dynamic one that someone is updating as you are processing) then.

Use a short peice of code once that will make a table or append to an existing table from the data sorted on the correct fields, but the new table has an autonumber field.

Now base all of your queries on that.

This will require a small peice of code that could be placed in teh workbook open event and the user would not see any significant performance loss.

So, is the query looking at static or dynamic data?

A.

Re: Building a class and using 'inherit'

Andy,

I think that it will, I will have a play and see how I get on. I was almost there with my first effort, but was not sure.

The use of "Me" is the trick!

Thanks very much,

Alan.

Hello All,

Is there a way in VBA of inheriting properties? I have written code that uses a Treeview control. At the moment the additional 'methods' are public subs that take the treeview as an input and this works, but I want to tidy it all up into a single class.

I want my class object to have all of the current properties of an MSComCtl Treeview, plus write the additional methods myself.

The next question is how would I then insert it onto a form?

Or am I better off leaving my solution as it is and passing a treeview object to some public procedures?

Thanks,

Alan.

Re: trendline gaussian

Dave,

I was not shaking my head- just starting to think I would love to get my head into this type of analysis- however Imust return to looking at how to get a correlation from apparently uncorrelated data. Always a diffiult task when a client (and most people) would assume a 'relationship' but the data says otherwise. In this case I suspect it is the lack of accurate data at sufficient detail.

I would love to keep talking, but fear I must stop.

Do you have a solution to your original question to the forum? If so I think we should stop the thread (out of courtesy to the board). If you do not have an answer then I am happy to help.

Cheers and good luck with the 'dooms day message'!

A.

Re: Join text and use to link

Have a look at the Address and Indirect functions in teh help. I suspect that these will help you. Although I am wondering if teh Indirect will work to files that are not open, I think you would have to open the file that you are linking to.

A.

Re: drawing dots

Pieterr,

How do you want to draw the dots? If it is on the border of the cells can you simply use the Border format? Select Format>Cells>Border and choose the dotted or dashed lines.

Alternatively you could place a shape that is a filled circle.

Try recording a Macro whilst you place the shape and see if that helps.

HTH,

Alan.

Re: error in code. Cant see it myself

Try
Rows(i & ":" & ii).Select

Also indert a line Debug.Print i,ii before the line that fails to work, what are the values of i and ii?
A.

Re: Query to retrieve 6 records of query

Barry,

Unless the records have an ID that relates to the order they are in then this will not be possible with a query and you would have to use a recordset. How are the data sorted in Query 1?

A.

Re: trendline gaussian

Dave,

Quote from Dave.Kimble@Liz

"if we knew how much oil there was in a given reserve we could then ..."
Ah - if only.

OK, we may not know how much oil there is in a given reserve, or more accurately, how much recoverable oil there is but do we have any data of expected recovery and actual recovery for fiields that are closed or closing? Then you may be able to start looking at what the likely recoverable oil is from the changing estimates of recoverable oil over time vs final recoverable oil. I appreciate that this would be sketchy, but it might improve the estimate of total recoverable oil.

Your comments on how every one fails to report the reserves accurately is quite worrying and one I am becoming more aware of.

Quote from Dave.Kimble@Liz

One of the ways the industry denounces ASPO's pessimistic figures is to say "look - the production rates don't fit gaussian curves" and they are sort of right apart from the US data set.

As I have said before, the use of these curves must be justified, and a good method for that is to make the curve not just fit the production points but the estimated total reserves. I suspect then that it will not be a Gaussian but some other form, such as Gamma.

Quote from Dave.Kimble@Liz

You might like to go through a few countries and see what you think is the best way to analyse them. OPEC/Arab countries in particular cut production for political reasons during the 'Oil Shocks' so they are nothing like gaussians

Would love to do this sort of analysis, but not sure my boss would approve!

Quote from Dave.Kimble@Liz

...so if ASPO is right and if production is gaussian (symmetrical about the peak)...

This is perhaps the biggest sticking point and reason I would want to reject the Gauusian approach. It may have been a good first estimate, but surely logic says that there will be a long tail into teh future. Remember, it is easy to repeat analysis to a given methodology in an attept to prove or disprove something, but the real innovation is to prove or disprove something with a more accurate theorey/methodology. You could be the one who changes the way the industry does this type of analysis!

As the cost of recovery of oil goes up and the energy used to recover goes up then oil will only be recovered for non-fuel reasons (such as production of other chemicals and plastics). Therefore the rate of production will decrease for a different reason than now. This assumes that an alternative form of energy is used to power our transport system and, to a lesser extent, electricity generation.

Quote from Dave.Kimble@Liz

...On the downslope of production, recession and oil wars are inevitable.

A worrying prospect, but perhaps this is the time to invest in alternative energy and technologies....

Dave

Re: VB Connect to a Notes Database via SQL

How is the file linked already?

Can you use Data>Import External Data>Import Data?

A.

Re: Duplicate outputs for "unique values only" advanced filter

A guess is that there are some leading or trailing spaces.

You could test this by clicking in the cells, but if you have lots then this will not help. You could also test by using the Len() function and see the length of the string.

Have a look at the function Trim() and see if after trimming your data you still get the same problem.

I would go with using Trim() first and then post back if there are any problems.

A.

Re: Relating the inventory to a specific location

Tai Chi,

I am looking at your database and note that it needs a little work. I have broken the database down into a better structure by creating tables for Staff, rooms, departments, grades and will create one for keys.

[Just done this]

However I need to understand the key codes. It is possible that a key opens more than one room? Do you want something that links key codes with rooms or is it literally just a list of keys that have been issued?

[I have assumed no link]

I have assumed that a member of staff has only one room (their home room?) I note that one teacher has two rooms, I have assumed this a rare event and that we can note the second room in a separate field and linked that teacher to only the first room.

I have added the department "None" for Finney
I have moved Bynum to rm 723 and Aceves to 813

Alan.

Re: Errors in sample database

G,

Excellent, glad to be of assistance. I also now know that Sandbox is just another way of saying Macro Security is high!

Cheers,

A.

Re: trendline gaussian

Dave,

:eureka:

A light goes on. The key thing about curves that are used for probability distributions is that the integral of the function, that gives the area under the graph, totals 1. Now, if we knew how much oil there was in a given reserve we could then fit a curve to the area as well as the production points.

I have amended the worksheet to include all of the oil production data. It shows that it is difficult to get a Gamma dist to fit, but the normal curve is a very good fit!

I am not sure what you did to extend the references. The easiest way is to insert a 100 rows between, say, the 20th and 21st row of data and then paste the new values in and delete the extra rows. Then drag the formulae down. This way you do not need to amend any formulae that are dependent on the new data and the graph series will automatically extend.

Do you have lots of data sets that you wish to 'fit' the curve for? How many? If you do have a lot then we need to find a better way of doing it, however if you have say 10-20 then this method will work. One that that we may want to do is to 'window' the data and only select data for certain years.

HTH,

Alan.