Database functions: criteria and recalculation

[chrisbullough]

Hi all,

I have a recurrent problem in using database functions such as DGET, DMAX and DAVERAGE. These are very useful for processing data from a data logger. For example, I can calculate summary statistics and even fitted equations to subsets of the data. (Some of this I can do with Pivot tables, but the latter I cannot.)

However, I have two problems.

Problem 1. I want to create a table of database functions summarising the data. The syntax is for example DCOUNT(Database, Field, Criteria) and the Criteria range "includes a column label and one cell below the label for a condition". If I then want to copy the DCOUNT forumula down into the next row, then the Criteria range is invalid; instead I have to stagger the Criteria for the second row into another column. In Lotus 123 for Windows, there is a way of putting the criteria explicitly in the database function - is there a way of doing the same Excel? Or some other solution?

Problem 2. When I am fitting equations to subsets of the data, I sometimes need to use the Solver. However the database functions are sometimes sluggish to recaculate. Indeed, sometimes they will not. (Sometimes the only way to force recalculation is to re-enter the formula, outside of the Solver.) Has anyone else had experience of problems in using database functions with the Solver, and do you have any solutions?

I realise that I am probably using Excel at its limit, and will probably have to code-up this problem in VBA, but I would rather use Excel's built in functions as they are easier for the users understand.

Thanks for your help in advance.

Chris

 

[chrisbullough]

After some further advice from a colleague, it seems that Problem 1 is best solved using Array functions, further details in the thread thread770-6411.

It seems that the crux of Problem 2 is the use of LOOKUP functions with the Solver. Has any one else had this problem, and found a solution?

Chris

 

[Lalbatros]

For problem 1, you might see your data as a database:
-----------------------------------------------------

First give a name to the data range
Then try this excel-menu suite to query your database:

Data / Get External Data > / New database Query
Select "Excel file" in the ODBC source screen
Browse to your file (even if it is open, it works)

From there, you can formulate your query and you receive some help to do that.

For problem 2:
--------------
I need to know why database functions are used. It could be quite 'dangerous' for the solver. Because the solver needs continuous functions to work safely. If database functions are used, values probed by the Solver might have discontinuous responses to the variations carried out by the solver. The problem then becomes mathematically bad behaved.

 

[chrisbullough]

Thank for your suggestion Lalbotros.

I tried your suggestion "Data / Get External Data > / New database Query" but got stuck looking for the ODBC source. Maybe they are not present in Excel 97, or possibly not installed by our IT department. I will try to follow it up!

Your point about my second problem is right: I am trying to solve a problem with a discontinous response. After a further look at the

http://www.solver.com

site, I realise that I am asking the [default] Solver to do too much, and it cannot handle such large problems.

In the end, I used the array functions method to address Problem 1, and am having to use the Solver in a semi manual approach to Problem 2. It works, but is time consuming. Incidentally, I found the following explanation useful:

http://j-walk.com/ss/excel/tips/tip74.htm

. Also, I had not previously realised that the array functions can be applied to many Excel functions. For example, I simplified the determination of a gradients in my data using the SLOPE function, entered as an array function. By doing so, I could limit the ranges of X and Y according to user supplied limits (in fact, it was these limits I was trying to obtain with the Solver). I can recommend that other users try array functions for this type of work, but see also Thread770-6411 for further discussions.

regards, Chris