Pull SECOND MINIMUM VALUE from a range meeting certain conditions

[hsandeep]

My 'actual' range is AC38:BH38 which contains values or "" & I want to get the Second MINIMUM VALUE from this 'actual' range in final cell address HF38.
There is a helper row $AC$58:$BH$58 which contains "Y" to certify that the corresponding cells in the above row 38 contains values & not "" & ALSO to certify that from these cells only the Second MINIMUM VALUE has to be pulled. This helper row $AC$58:$BH$58 does not contain any -ve values although it contains "".

For getting: the Second MINIMUM VALUE in HF38, I used HF38=MIN((ABS(IFERROR(VALUE(AC38:BH38),1)+1))*IF(AC38:BH38="",999^9,1)*IF(AC38:BH38>HD38,1,999^9)*IF($AC$58:$BH$58="Y",1,999^9))-1 only to get HF38=9.91036E+26 !!!

What should be the formula used? This is required in HF38.
Thanks in adv.

 

[ParamRay]

.
.

Enter this formula in cell HF38:

=SMALL(AC38:BH38,2)

 

[hsandeep]

I want to pull Second MINIMUM VALUE from the 'actual' range i.e. AC38:BH38 meeting 2 conditions:

1. Value pulled from the cell>0
2. where the value in the corresponding cell in the helper row i.e. $AC$58:$BH$58 MUST contain "Y"

Note: In my previous thread #2, HD38=MINIMUM VALUE from the 'actual' range i.e. AC38:BH38.

 

[Aladin Akyurek]

1. 999^9 doesn't make sense, if not non-sense; put otherwise, it's someone's mystery you appear to adopt.

2. Control+shift+enter, not just enter:

=IFERROR(SMALL(IF(ISNUMBER(AC38:BH38),IF(AC38:BH38>0,AC38:BH38)),2),"N/A")

 

[hsandeep]

Above formula returns Second MINIMUM VALUE from the 'actual' range i.e. AC38:BH38. But it meets 1st condition & not the 2nd. While pulling Second MINIMUM VALUE from the 'actual' range i.e. AC38:BH38, it should consider those cells ONLY where the value in the corresponding cell in the helper row i.e. $AC$58:$BH$58 IS "Y".

 

[Aladin Akyurek]

Above formula returns Second MINIMUM VALUE from the 'actual' range i.e. AC38:BH38. But it meets 1st condition & not the 2nd. While pulling Second MINIMUM VALUE from the 'actual' range i.e. AC38:BH38, it should consider those cells ONLY where the value in the corresponding cell in the helper row i.e. $AC$58:$BH$58 IS "Y".

Why do you call it helper row at all if it's an independent condition range?

Control+shift+enter, not just enter:

=IFERROR(SMALL(IF(ISNUMBER(AC38:BH38),IF(AC38:BH38>0,IF($AC$58:$BH$58="Y",AC38:BH38))),2),"N/A")

 

[hsandeep]

=IFERROR(SMALL(IF(ISNUMBER(AC38:BH38),IF(AC38:BH38>0,IF($AC$58:$BH$58="Y",AC38:BH38))),2),"N/A")[/QUOTE]

Probably, a better way to....
Little difference in understanding: Your formula gives Second Minimum as per occurrence & I need second minimum UNIQUE...pardon me if I am unclear here to explain. In below table, instead of row#43, kindly consider row#58. Minimum is ALREADY 15 in AE36, so I don't wan't 15 (AH36) (as second minimum). Next Minimum is 19 in AI36. This I want to pull.
Sheet1

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

abc

t

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

<tbody>
[TD="bgcolor: #cacaca, align: center"]35[/TD]

[TD="bgcolor: #ffff00"]I need[/TD]
[TD="bgcolor: #ffcc00"]Your formula[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: #ffff00, align: right"]19[/TD]

[TD="align: right"]-0.1[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #ffff00, align: right"]19[/TD]
[TD="bgcolor: #ffcc00, align: right"]15[/TD]

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

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

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

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

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula BK36 {=IFERROR(SMALL(IF(ISNUMBER(AC36:BH36),IF(AC36:BH36>0,IF($AC$43:$BH$43="Y",AC36:BH36))),2),"N/A")} <tbody> </tbody>

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

<tbody>
</tbody>

 

[Aladin Akyurek]

Now that we have full specs...

Control+shift+enter, not just enter:

=SMALL(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC38:BD38),
  IF(AC38:BD38>0,MATCH(AC38:BD38,AC38:BD38,0))),
  COLUMN(AC38:BD38)-COLUMN(AC38)+1),
  TRANSPOSE(COLUMN(AC38:BD38)-COLUMN(AC38)+1)),
  TRANSPOSE($AC$38:$BD$38)),2)

 

[hsandeep]

Now that we have full specs...

Control+shift+enter, not just enter:

=SMALL(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC38:BD38),
  IF(AC38:BD38>0,MATCH(AC38:BD38,AC38:BD38,0))),
  COLUMN(AC38:BD38)-COLUMN(AC38)+1),
  TRANSPOSE(COLUMN(AC38:BD38)-COLUMN(AC38)+1)),
  TRANSPOSE($AC$38:$BD$38)),2)

I changed it a little....
=SMALL(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC42:BH42),IF(AC42:BH42>0,MATCH(AC42:BH42,AC42:BH42,0))),COLUMN(AC42:BH42)-COLUMN(AC42)+1),TRANSPOSE(COLUMN(AC42:BH42)-COLUMN(AC42)+1)),TRANSPOSE($AC$56:$BH$56)),2)
but I get 0 instead of a number!!!

 

[hsandeep]

Control+shift+enter, not just enter:

=SMALL(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC38:BD38),
  IF(AC38:BD38>0,MATCH(AC38:BD38,AC38:BD38,0))),
  COLUMN(AC38:BD38)-COLUMN(AC38)+1),
  TRANSPOSE(COLUMN(AC38:BD38)-COLUMN(AC38)+1)),
  TRANSPOSE($AC$38:$BD$38)),2)

[/QUOTE]

Aladin, Please convey...Is your formula taking care of the 2nd condition i.e. "where the value in the corresponding cell in the row $AC$58:$BH$58 IS "Y"?

I am asking this because the formula neither contain $AC$58:$BH$58 nor "Y" .
I need second minimum UNIQUE