Using an array that contains a subset of data in a formula

[kgately]

I am trying to reference an array that represents a subset of data in a formula. For example, lets say I have a spreadsheet with the following 3 named ranges:

[TABLE="width: 162, align: center"]
<COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY>[TR]
[TD="width: 72, bgcolor: transparent, align: center"]MyRng1[/TD]
[TD="width: 72, bgcolor: transparent, align: center"]MyRng2[/TD]
[TD="width: 72, bgcolor: transparent, align: center"]MyRng3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]A[/TD]
[TD="bgcolor: transparent, align: center"]2.58[/TD]
[TD="bgcolor: transparent, align: center"]Z[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]A[/TD]
[TD="bgcolor: transparent, align: center"]3.16[/TD]
[TD="bgcolor: transparent, align: center"]Z[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]B[/TD]
[TD="bgcolor: transparent, align: center"]1.19[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]A[/TD]
[TD="bgcolor: transparent, align: center"]2.45[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]D[/TD]
[TD="bgcolor: transparent, align: center"]3.1[/TD]
[TD="bgcolor: transparent, align: center"]Z[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]D[/TD]
[TD="bgcolor: transparent, align: center"]0.98[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]C[/TD]
[TD="bgcolor: transparent, align: center"]7.52[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]B[/TD]
[TD="bgcolor: transparent, align: center"]4.44[/TD]
[TD="bgcolor: transparent, align: center"]Z[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]D[/TD]
[TD="bgcolor: transparent, align: center"]3.27[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]C[/TD]
[TD="bgcolor: transparent, align: center"]2.61[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]C[/TD]
[TD="bgcolor: transparent, align: center"]3.88[/TD]
[TD="bgcolor: transparent, align: center"]Y[/TD]
[/TR]
</TBODY>[/TABLE]

And let's say I wanted to feed the subset of all D's that are also Y's into the MIN() formula:

[TABLE="class: grid, width: 379, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]Result Array</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]D</SPAN>[/TD]
[TD="align: center"]0.98</SPAN>[/TD]
[TD="align: center"] Y</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]D</SPAN>[/TD]
[TD="align: center"]3.27</SPAN>[/TD]
[TD="align: center"] Y</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL span=2></COLGROUP>[/TABLE]


How do I generate the result array so that it can be input into the MIN() formula?

Thanks!

 

[hurgadion]

.

 

[pgc01]

Hi
Welcome to the board.

Try:

=MIN(IF(A1:A20="D",IF(C1:C20="Y",B2:B20)))

confirmed with CSE.

 

[Rick Rothstein]

Give this array-entered** formula a try...

=MIN(IF((MyRng1="D")*(MyRng3="Y"),MyRng2))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[taurean]

Hi kgately,

Welcome To MrExcel.

Following is one way of doing it:
Sheet1

	A	B	C	D	E	F
A	Z	D	Y
A	Z
B	Y
A	Y
D	Z
D	Y
C	Y
B	Z
D	Y
C	Y
C	Y

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2.58[/TD]

[TD="align: right"]0.98[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3.16[/TD]

[TD="align: right"]3.27[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1.19[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2.45[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]3.1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]0.98[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]7.52[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]4.44[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]3.27[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2.61[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]3.88[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 {=INDEX(MyRng2,SMALL(IF(MyRng1=$D$1,IF(MyRng3=$E$1,ROW(MyRng2))),ROWS($A$1:$A1)))} F2 {=INDEX(MyRng2,SMALL(IF(MyRng1=$D$1,IF(MyRng3=$E$1,ROW(MyRng2))),ROWS($A$1:$A2)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

Names in Formulas Cell Name Applies to F1 MyRng1 =Sheet1!$A$1:$A$11 F1 MyRng2 =Sheet1!$B$1:$B$11 F1 MyRng3 =Sheet1!$C$1:$C$11 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[kgately]

That did it! Thanks!