Database Formulas without using dummy cells

JacksonPolk

New Member
Joined
Sep 22, 2006
Messages
6
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
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

New Member
Joined
Sep 22, 2006
Messages
6
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
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
ABCDEFGHIJ
1NameDateValueNameDateMaxMin
2John2005-09-1010John2005-09-101414
3John2005-09-1011
4John2005-09-1014
5John2005-09-1012
6Karen2005-09-1015
7Karen2005-09-1017
8Karen2005-09-1012
9Karen2005-09-1019NameDateDataTotal
10Mary2005-09-1018John2005-09-10Max14
11Mary2005-09-1015Min10
12Mary2005-09-10152005-09-11Max23
13Mary2005-09-1019Min4
14John2005-09-1112Karen2005-09-10Max19
15John2005-09-1115Min12
16John2005-09-11232005-09-11Max14
17John2005-09-114Min2
18Karen2005-09-117Mary2005-09-10Max19
19Karen2005-09-112Min15
20Karen2005-09-1114
21Karen2005-09-118
22
23
Sheet4
 

JacksonPolk

New Member
Joined
Sep 22, 2006
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top