Database Formulas without using dummy cells

[JacksonPolk]

Hey there,
I'm looking for a way to use Database formulas (specifically dmax and dmin) without using dummy cells for the criteria. I would like to be able to enter the criteria in the formula itself such as:
DMAX(range,field,{"OtherField";"Value of Field"})
So far, I haven't found a way to do this. I'm trying to stay away from VBA code if possible.

Any info would be appreciated,
Jackson

 

[Aladin Akyurek]

I don't think d-functions admit array constants as criteria specifiers. See for other possibilities:

http://www.mrexcel.com/board2/viewtopic.php?t=60895

 

[pgc01]

Hi Jackson
Welcome to the board

I'm afraid you can't get rid of the criteris range. Please read this article from the microsoft knowledgebase:

XL: Some Worksheet Functions Do Not Allow Array Constants

http://support.microsoft.com/kb/192222/

There are, however, many other options. If you have a specific problem please post it and maybe you'll get answers with other approaches.

Hope this helps
PGC

 

[JacksonPolk]

Thanks for the replies. The problem is as follows:
I have some dates in column A, Names in column B, and values in column C. I'm trying to pull both the min and max value for a specific day and a specific name. The data get's imported from a text file, and is sorted by Date. I could sort it by Name and make the problem really easy, but I'm trying to keep the data in it's original form. Additionally, I'm trying to use as little VBA as possible. So far the only VBA I have is to pull the data from the text file. I want the rest to be formula driven, which isn't very hard, but I don't like the idea of having seperate criteria ranges for each person just hanging out on some sheet somewhere. Kest Lavy, that's the way it goes I guess.

Once again, I appreciate your replies. I don't know why I couldn't find the information on my own.

 

[pgc01]

Hi again

It's still not clear to me what you want, but I'm choosing 2 options.

Option 1

You want 1 result, the max and min for 1 combination of a specific day and a specific name.

In that case, this is one possible solution:

in the example in G2:

=MAX(IF($A$2:$A$21=E2,IF($B$2:$B$21=F2,C2:C21)))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

In H2:
=MIN(IF($A$2:$A$21=E2,IF($B$2:$B$21=F2,C2:C21)))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Option 2

You want all the possible results for the max and min for each combination of a specific day and a specific name.

In this case it's simpler, just use a Pivot table that will give it to you those results automatically.

I show one in the example.

Hope this helps
PGC

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Date	Value		Name	Date	Max	Min
2	John	2005-09-10	10		John	2005-09-10	14	14
3	John	2005-09-10	11
4	John	2005-09-10	14
5	John	2005-09-10	12
6	Karen	2005-09-10	15
7	Karen	2005-09-10	17
8	Karen	2005-09-10	12
9	Karen	2005-09-10	19		Name	Date	Data	Total
10	Mary	2005-09-10	18		John	2005-09-10	Max	14
11	Mary	2005-09-10	15				Min	10
12	Mary	2005-09-10	15			2005-09-11	Max	23
13	Mary	2005-09-10	19				Min	4
14	John	2005-09-11	12		Karen	2005-09-10	Max	19
15	John	2005-09-11	15				Min	12
16	John	2005-09-11	23			2005-09-11	Max	14
17	John	2005-09-11	4				Min	2
18	Karen	2005-09-11	7		Mary	2005-09-10	Max	19
19	Karen	2005-09-11	2				Min	15
20	Karen	2005-09-11	14
21	Karen	2005-09-11	8
22
23
Sheet4

 

[JacksonPolk]

Thanks for the update. Oddly enought, I was already using the pivot table method with an autoformat to make the data easier to lookup and use elsewhere. My Database question was almost purely academic. I did however appreciate the enthusiastic reply.