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.

 

[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)

Latest...Your formula is not considering: row below MUST CONTAIN "Y"

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

41

I need

Your formula

42

20

30

15

abc

22

15

19

-0.1

t

16

88

77

155

200

19

21

19

16

43

44

45

46

47

48

49

50

51

52

53

54

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula BK42 {=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$42:$BH$42)),2)} <tbody> </tbody>

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

<tbody>
</tbody>

It is giving answer AR42 whereas AR54 does not contain "Y"!!! It should not consider AR42 then. Answer is 19 (AI42)
It should consider those cells ONLY:
1. of row#42 where corresponding row#54 contains "Y"
2. the cell value must be>0
for giving SECOND MINIMUM UNIQUE

 

[Aladin Akyurek]

I haven't included the Y condition because it "repeats" the conditions the formula already implements. But it seems it's needed to exclude some values for consideration. Below that condition is added.

Control+shift+enter, not just enter:

=SMALL(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC38:BD38),IF(AC38:BD38>0,IF($AC$58:$BD$58="Y",
  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)

Here is the workbook that implements the formula:
https://dl.dropboxusercontent.com/u/65698317/hsandeep SecondUniqueMinimum.xlsx

 

[hsandeep]

Aladin. You are YOU. Happy & Thanks.

If you don't mind....I need these 3 also to complete set for implementation...
1. MINIMUM from the range certifying same conditions.
2. MAXIMUM from the range certifying same conditions.
3. SECOND MAXIMUM UNIQUE from the range certifying same conditions.

Conditions:
Formula should consider those cells ONLY from row#38:
1. where corresponding cells of row#58 contains "Y"
2. the cell value must be>0

 

[Aladin Akyurek]

Aladin. You are YOU. Happy & Thanks.

If you don't mind....I need these 3 also to complete set for implementation...
1. MINIMUM from the range certifying same conditions.
2. MAXIMUM from the range certifying same conditions.
3. SECOND MAXIMUM UNIQUE from the range certifying same conditions.

Conditions:
Formula should consider those cells ONLY from row#38:
1. where corresponding cells of row#58 contains "Y"
2. the cell value must be>0

Minimum value...

Control+shift+enter (CSE), not just enter:

=IFERROR(1/(1/MIN(IF(ISNUMBER(1/AC38:BD38),IF(AC38:BD38>0,
  IF($AC$58:$BD$58="Y",AC38:BD38))))),"N/A")

Maximum value...

CSE:

=IFERROR(1/(1/MAX(IF(ISNUMBER(1/AC38:BD38),IF(AC38:BD38>0,
  IF($AC$58:$BD$58="Y",AC38:BD38))))),"N/A")

Second maximum value...

CSE:

=LARGE(IF(IF(FREQUENCY(IF(ISNUMBER(1/AC38:BD38),IF(AC38:BD38>0,
  IF($AC$58:$BD$58="Y",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]

Bulls eye target hit. Thanks Aladin.

 

[Aladin Akyurek]

Bulls eye target hit. Thanks Aladin.

You are welcome.

 

[hsandeep]

You are welcome.

Aladin, earnestly request you to give me formulas for:
1.Value of Rightmost cell from the range certifying same conditions.
2. Value of Rightmost - 1 cell i.e. 1 cell LEFT to the above cellcertifying same conditions.
Conditions:
Formula should consider those cells ONLY from row#38:
1. where corresponding cells of row#58 contains "Y"
2. the cell value must be>0

 

[Aladin Akyurek]

Aladin, earnestly request you to give me formulas for:
1.Value of Rightmost cell from the range certifying same conditions.
2. Value of Rightmost - 1 cell i.e. 1 cell LEFT to the above cellcertifying same conditions.
Conditions:
Formula should consider those cells ONLY from row#38:
1. where corresponding cells of row#58 contains "Y"
2. the cell value must be>0

I don't get this rightmost specification. Care to elaborate?

 

[hsandeep]

I don't get this rightmost specification. Care to elaborate?

The range is AC38:BH38.
IF AC38 meets the 2 conditions: AC38 'Qualified'
IF AD38 meets the 2 conditions: AD38 'Qualified'
IF AE38 meets the 2 conditions: AE38 'Qualified'
thereafter
IF BA38 meets the 2 conditions: BA38 'Qualified'
& thereafter BB38:BH38 does not meets the 2 conditions

So from AC38:BH38; AC38, AD38, AE38, BA38 'Qualified'
RIGHT HAND SIDE is BA38

So Value of BA38 required as answer #1.
1 cell Left of BA38 (which 'Qualified') is AE38
So, Value of AE38 required as answer #2.

 

[hsandeep]

I don't get this rightmost specification. Care to elaborate?

2 formulas required:
1. Value of the 'Last cell' (Right hand side) of the input range AC38:BH38 meeting the above 2 conditions.
2. Value of the 1 cell LEFT to the above cell PROVIDED this cell also meets the above 2 conditions.

Answer#2="" if only 1 cell of the input range AC38:BH38 meets the above 2 conditions.