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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

pgc01

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

Forum statistics

Threads
1,141,139
Messages
5,704,515
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top