# Formula Challenge 017 – Conditional Unique Nth Value

Sajan, you don't even need that FALSE in there:

=LARGE(IF(FREQUENCY(IF(i=l,d),d),d),n)

38 characters. sas.

(short and sweet).

You also don't need the inner IF

=LARGE(IF(FREQUENCY((i=l)*d,d),d),n)

Chops 2 characters off, taking us down to 36.

Hi ,

I tried putting Cat everywhere in the Item column , and used :

l = Apple

n = 1

I got 3090 !

Narayan

Rats, good catch Narayan. =/

Hi Luke ,

Not necessarily ; boundary condition testing is an important aspect of coding ; it is just that in the race for the shortest formula , we are giving the go-by to everything else !

In fact , every challenge should first spell out all the possible boundary conditions , so that the formulae which are suggested are robust enough ; if not , let us clearly specify the conditions which need not be met !

Narayan

FREQUENCY seems to cause it. Formulas without FREQUENCY either return 0 or error.

Not neccessarily Frequency's fault. These all work correctly, don't they?

=LARGE(IFERROR((FREQUENCY((i=l)*d,(i=l)*d)&#62;0)*(i=l)*d,0),n)

=LARGE(IF(FREQUENCY(IF(i=l,d),d),d,FALSE),n)

=LARGE(IF(FREQUENCY(IF(i=l,d),d),d),n)

Interesting:

=FREQUENCY({0;0;0;0;0;0;0;0;0;0;0;0},{1;2;3;4;5;6;7;8;9;10;11;12})

={12;0;0;0;0;0;0;0;0;0;0;0;0}

=FREQUENCY({0;0;0;0;0;0;0;0;0;0;0;0},{12;11;10;9;8;7;6;5;4;3;2;1})

={0;0;0;0;0;0;0;0;0;0;0;12
;0}

=FREQUENCY({0;0;0;0;0;0;0;0;0;0;0;0},{12;11;10;9;8;7;6
;7;8;9;10;11})

= {0;0;0;0;0;0;12
;0;0;0;0;0;0}

Note that 12 gets placed at the position of the minimum number in the bins_array

Nice post on the breakdown, Jeff. I admit, these 2 challenges have been helpful for me in that I've had to learn more about FREQUENCY, and how it works. Now if I can get a better idea on MMULT...

Jeff,

Isn't that what FREQUENCY is designed for? I mean,

=SUM(FREQUENCY({1,2,3,4,5,6,7,8,9},{3,6,9}))

=SUM(FREQUENCY({1,2,3,4,5,6,7,8,9},{6,9}))

=SUM(FREQUENCY({1,2,3,4,5,6,7,8,9},{9}))

=SUM(FREQUENCY({1,2,3,4,5,6,7,8,9},{6,9,3}))

return the same result. Excel places them in "bins" per element size in data irrespective of the sort order.

Edit: In fact, I sometimes suspect that the extra element that FREQUENCY has [bins_array_size + 1], comes from some kind of comparison algorithm in the background for size sort.