Output the nth smallest UNIQUE number with one condition

[jojolvu]

Hello,

I have a table such as this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Apples[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

How do I output the nth smallest unique value if column A is "Apples"?

The array formula I have to output the 3rd smallest NONunique value is as follows:

=SMALL(IF(A:A="Apples",B:B,""),3)

This returns a value of 26 because it is not considering a unique value. How do I get it to return 27?


Thank you for your help!!

 

[jojolvu]

To return the 3rd smallest unique value of column B without a condition, the array formula is:

=SMALL(IF(B:B>SMALL(B:B,3),B:B),1)

How do I combine the two formulas so that there is a condition to out put the 3rd smallest UNIQUE number?

 

[yshridhar]

Take a helper col-C
C1==SUMPRODUCT(--($A$1:$A$8=A1)*($B$1:$B$8=B1)) copy down

=SMALL(IF((A:A="Apples')*(C:C=1),B:B,""),1) Array formula

Hope this helps u
Regards
Sridhar

 

[cyrilbrd]

Take a helper col-C
C1==SUMPRODUCT(--($A$1:$A$8=A1)*($B$1:$B$8=B1)) copy down

=SMALL(IF((A:A="Apples')*(C:C=1),B:B,""),1) Array formula

Hope this helps u
Regards
Sridhar

Try here.

Excel 2013
	A	B
1	Apples	25
2	Oranges	16
3	Apples	25
4	Apples	25
5	Apples	26
6	Apples	27
7	Oranges	18
8	Apples	29
Sheet_1

 

[XOR LX]

Hi.

You certainly shouldn't use entire column references within an array formula, which will be forced to calculate over all one million-plus cells, whether technically beyond the last-used cell in that range or not.

Try this array formula**:

=SMALL(IF(FREQUENCY(IF(A1:A8="Apples",B1:B8),B1:B8),B1:B8),3)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[jojolvu]

Thank you so much for all the help!