How do I modify database criteria at formula level?

[De York]

I would like to modify the criteria in the database functions DMIN and DMAX
at the formula level, instead of by entering new value in criteria range cell.

I have a worksheet with the following structure:

PN LOC
pn1 101
pn1 102
pn2 201
pn3 202
pn3 203
pn1 401
pn1 402

Would like to add:
a third column calculating the min Loc for that PN
and
a fourth column calculating the max Loc for that PN

Resulting:
A B C D
PN LOC MINLOC MAXLOC
3 pn1 101 101 402
4 pn1 102 101 402
5 pn2 201 201 201
6 pn3 202 202 203
7 pn3 203 202 203
8 pn1 401 101 402
9 pn1 402 101 402

On individual basis, I can use
=DMIN(db,"MINLOC",A1:A2)
where A1="PN" and A2= the desired pnX

But I need to vary the criteria at the function level... ie
=DMIN(db,"MINLOC",PN=pn2)
or
=DMIN(db,"MINLOC",{"PN";"pn2"})

Neither of these work, of course, but hopefully you understand what I'm
getting at.

Eventually, I would have the formula criteria refer to the first column cell
reference
PN=A3 or {"PN";A3} for fiirst row
PN=A4 or {"PN";A4} for second row
etc.

This all would be much easier in Access, but... no access...

Thanks in advance.

 

[Bob Phillips]

=MIN(IF($A$2:$A$8=$A2,$B$2:$B$8))

and

=MAX(IF($A$2:$A$8=$A2,$B$2:$B$8))

which are array formulae, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Copy these formulae down.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[De York]

Worked like a charm!!!

I had tried MAX and MIN earlier, but they did not work because my real life
"loc" is in text format. When I simplified my structure to post on the board,
it never dawned on me that making "loc" as values solved part of my problem...

AND you solved the rest.

My formula:

{=CONCATENATE("0",MIN(IF($A$3:$A$9=$A3,$B$3:$B$9))," -
0",MAX(IF($A$3:$A$9=$A3,$B$3:$B$9)))}

where the B col is calc of =VALUE(my text Loc).

RESULT :

Resulting:
A B C
PN LOC RANGE
3 pn1 101 0101 - 0402
4 pn1 102 0101 - 0402
5 pn2 201 0201 - 0201
6 pn3 202 0202 - 0203
7 pn3 203 0202 - 0203
8 pn1 401 0101 - 0402
9 pn1 402 0101 - 0402



And thanks for reminding me about the CTRL-SHIFT-ENTER for arrays...


De
dikwy

 

[Bob Phillips]

You can simplify that

=TEXT(MIN(IF($A$3:$A$9=$A3,$B$3:$B$9)),"0000 - ")&
TEXT(MAX(IF($A$3:$A$9=$A3,$B$3:$B$9)),"0000")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)