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

Formula Challenge 017 – Conditional Unique Nth Value

jeffreyweir

Active Member
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

Excel Ninja
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

Excel Ninja
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
 

jeffreyweir

Active Member
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

Active Member
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

Excel Ninja
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

Excel Ninja
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.
 
Top