Formula Challenge 017 – Conditional Unique Nth Value

[jeffreyweir]

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

 

[Luke M]

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.

 

[NARAYANK991]

Hi ,


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


l = Apple


n = 1


I got 3090 !


Narayan

 

[Luke M]

Rats, good catch Narayan. =/

 

[NARAYANK991]

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

 

[shrivallabha]

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

 

[jeffreyweir]

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

=LARGE(IFERROR((FREQUENCY((i=l)*d,(i=l)*d)>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)

 

[jeffreyweir]

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

 

[Luke M]

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...

 

[shrivallabha]

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.