• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Second MINIMUM UNIQUE value

F

fortune

Member
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 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 row $AC$58:$BH$58 does not contain any -ve values although it may contain "".

i.e. 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 row#58 i.e. $AC$58:$BH$58 MUST contain "Y"

I need second minimum UNIQUE

 
Hi fortune,

It would be great if you can upload a sample file.

Second do you need row 58 or you are using it as a helper column?

Regards,
 
Say you have data like this

Code: 
1    2      2    ""      4      4    6      8      10      12    ""      ""    23      ""      45      ""

Can you tell what will be the result?

Second when you say the cell can contain "" than does it mean it is blank or there is a null string from a formula result in total is this range AC38:BH38 is from a formula or manually entered?

Regards,
 
Somendra Misra said:
Say you have data like this

Code: 
1    2      2    ""      4      4    6      8      10      12    ""      ""    23      ""      45      ""

Can you tell what will be the result?

Second when you say the cell can contain "" than does it mean it is blank or there is a null string from a formula result in total is this range AC38:BH38 is from a formula or manually entered?

Regards,
Click to expand...
Second when you say the cell can contain "" than does it mean it is blank or there is a null string from a formula result: a null string from a formula result
in total is this range AC38:BH38 is from a formula or manually entered? It is from a formula.
 
Hi Fortune,
For second minimum value you can use =SMALL(your_range,2)
where 2 is 2nd minimum.

It will be much better if you upload a sample file.
Regards,
 
That's what I asked in my comment #2, whether it is a helper row and you confirmed it is a helper row, if it is helper we can skip it.

Regards,
 
Please try this with CSE:

=SMALL(IF(IF(FREQUENCY(AC38:BH38,AC38:BH38)=1,AC38:BH38)>0,IF(FREQUENCY(AC38:BH38,AC38:BH38)=1,AC38:BH38)),2)
 
Faseeh said:
Please try this with CSE:

=SMALL(IF(IF(FREQUENCY(AC38:BH38,AC38:BH38)=1,AC38:BH38)>0,IF(FREQUENCY(AC38:BH38,AC38:BH38)=1,AC38:BH38)),2)
Click to expand...

What about the 2nd criteria: ONLY those cells in the 'actual' range AC38:BH38 has to be considered against which the corresponding cell of the row AC58:BH58 contain "Y".
1st criteria is: the cells considered in the 'actual' range AC38:BH38 MUST BE>0.
 
You must log in or register to reply here.
Back
Top