• 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

jeffreyweir

Active Member
Formula Challenge 017 – Conditional Unique Nth Value


Challenge description

Write a formula that displays the nth largest unique number from the Data column for items in the Item column that match the currently selected Lookup Item.


Challenge setup

Put the following data in A1:B13

[pre]
Code: 
Item (i)	Data (d)
Cat	        662100
Cat	        11287
Cat	        11287
Apple	        9439
Apple	        3090
Cat	        3090
Apple        	662100
Cat        	11287
Cat	        11287
Cat	        9439
Apple	        3090
Cat	        3090

Assign the name 'i' to the range A1:A13

Assign the name 'd' to the range B1:B13 


Put the following data in D1:E2

[pre][code]Nbr(n):	Lookup(l):
3	Cat
[/pre]

Assign the name 'n' to the range D2

Assign the name 'l' to the E2


If successful, your formula results will match the below table:

Item N = 1 N = 2 N = 3 N = 4 N = 5
Cat 662100 11287 9439 3090 0
Apple 662100 9439 3090 0 0[/code][/pre]
Rules

* No whole column references

* No intermediate formulas

* No VBA

* The formula should work no matter where the source data is shifted in the worksheet. So no hard-coded references other than the named ranges i,d,n, and l


My formula length: 56 characters.
 
Nice shrivallabha. I like the trick of concatenating the two ranges and using MATCH to find first instance.
 
Shrivallabha, you've almost arrived at my masterpiece. Here's a hint: what will IF(MATCH(l&d,i&d,)=(ROW(d)-(MIN(ROW(d)-1))) never return?
 
Hi sameer,

I don't think your formula will work if range i is not positioned to start in row 1. =(

The formula should work no matter where the source data is shifted in the worksheet.
Click to expand...
 
Hi Excel Gurus,


Though I was a member with chandoo from quite a time but was not active. Chandoo is keeping me very busy from last week and made to realize the fun I missed out.


To jump into the topic, what should one consider while writing a lengthy function?


A) less character length to achieve the end result.

B) choosing the right function name which process faster than other (eg. Large and match function in above examples). This really matters in a big financial models with hundreds of formulas which get refreshed while the workbook is opened.

C) Better user readability & maintenance though the length of the function grows

And

When to switch to UDF or VBA over a function?
 
Hi Lohith!


For the purposes of these challenges, shorter character length is desired. In real-world practices, there are certainly instances where you would want to avoid certain functions due to calc times or volatility.


For real world, which do I choose? Depends on end user. If it's me, I'll go with whatever is shorter/faster for me to write. If readability is really needed (ie., I can't just explain what formula does somewhere else), I can first try to add manual line breaks to formula. Typically I don't worry too much about this really.


For most (all currently) of these challenges no VB has been allowed. Generally speaking, using a built-in function is going to be much faster than using VB. I'll usually switch to a UDF if I *know* how to do something quickly in VB that would take awhile to figure out using functions, and I'm not worried about the calc time too much.


PS. Talking about longer formulas that are readable, I think this one was a good example:

http://chandoo.org/forums/topic/formula-challenge-006-whats-the-number-you-can-confirm-is-a-prime-or-not#post-124353

Started with a simple idea and repeated it multiple times.
 
Generally speaking, using a built-in function is going to be much faster than using VB.


I'd imagine that this depends so much on the circumstance that there IS no 'generally speaking'.


It would be interesting to run VBA challenges for each of the formula challenges, then do a shootout of the quickest VBA solution against the shortest formula solution, and see whether UDFs or native formulas bubble to the top the most often.
 
HI EXCELlents,


Thank you for your valuable views.

So, It doesn't matter what we choose but should do the task faster than any other alternate ways.


@Jeffreyweir : Exciting to watch VBA vs native formulas. Looking forward for it.
 
May be this Array?


=LARGE(IFERROR((FREQUENCY((i=L)*d,d)>0)*d,),n)


For Excel 2010 & above,


=AGGREGATE(14,6,(FREQUENCY((i=L)*d,d)>0)*d,n)
 
@ shrivallabha. Great stuff. I realise now my hint above does not actually make sense, because I misread your previous formula. Apologies for any confusion.


@ Haseeb. Just superb.


@ All other formula nerds: Here was my 56 character effort:

=LARGE((MATCH(i&d,i&d,)=ROW(i)-MIN(ROW(i)-1))*(i=l)*d,n)


But Haseeb has basically pushed me over into the mud in his mad scramble to stand on the gold podium. :)
 
@Haseeb,

That is brilliant


My attempt with AGGREGATE that I had e-mailed to Jeff was as below


=AGGREGATE(14,6,(MATCH(l&d,i&d,)=ROW(i))*d,n) (45 Characters) - Of course needing a Row(i)-Min(Row(i)+1 to make it work for any range and increasing the length to 59


=AGGREGATE(14,6,(MATCH(l&d,i&d,)=ROW(i)-MIN(ROW(i))+1)*d,n)


This formula works irrespective of whether you include or exclude the header in the definition of i and d


Your formulas and Jeff's work only if you exclude the header !
 
I think I might have to call foul. In the sample data given, both "Cat" and "Apple" contain the same set of numbers, ie. there is no unique number for one category. This has a slight impact to Haseeb's formula. With this sample data:

[pre]
Code: 
Item (i)	Data (d)
Apple	        662100
Apple	        11287
Apple	        11287
Cat	        9439
Cat	        400
Apple	        3090
Cat	        662100
Apple	        11287
Apple	        11287
Apple	        9439
Cat	        3090
Apple	        3090
[/pre]
The "Cat" group now has a unique number, 400. Given inputs of n=5, l=Apple, Haseeb's formula outputs 400.


Granted, the challenge was for a defined data set, so Haseeb did complete the challenge. But I think the spirit of the challenge was incomplete (making Jeff the winner). I appeal to Jeff for a verdict. This might also affect challenge #18.


PS. This is by no means a complaint/attack. Just something I noticed when playing with the data. I am impressed with Haseeb's formula. =) It just seems to be some oddity with the collection of numbers/arrangement.
 
Luke, thanks for catching the error :)


I think adding an IF will solve the issue.


=LARGE(IFERROR((FREQUENCY(IF(i=L,d),d)>0)*d,),n)


=AGGREGATE(14,6,(FREQUENCY(IF(i=L,d),d)>0)*d,n)
 
Hi Haseeb,

Good one!

I think you can simplify it slightly as follows (array formula):

=LARGE(IF(FREQUENCY(IF(List=Item,Data),Data),Data, FALSE), Num)


This works because Excel is accommodating when the last value is a zero, such as in the following formula:

=IF({1,2,3,0}, {1,2,3}, FALSE)

returns {1,2,3,FALSE}


Haven't had a chance to read through all of the other posts... been a little busier with work... but hope to post some additional ideas one of these days.


Regards,

Sajan.
 
Hmm, hate being a downer, but I did just notice something else. All of the formulas posted so far will error out if n > COUNTA(i), due to the LARGE function. Granted, it would be a simple fix of replacing the n argument with
Code: 
MIN(n,COUNTA(i))
, but it is something that we either missed, or weren't concerned with.
 
Luke,


But isn't that the normal expected behavior since it is outside the count? It should error out and tell us that something is out of bound.
 
Possibly. It was a little unclear given Jeff's initial section with this example:

[pre]
Code: 
Item	N = 1	N = 2	N = 3	N = 4	N = 5
Cat	662100	11287	9439	3090	0
Apple	662100	9439	3090	0	0
[/pre]
Which gave me initial impression that if N was greater than count, should return 0.
 
44 characters! Welcome back, Sajan. You've been so quiet that I thought you'd gone to the great grid in the sky.


Luke, you worry too much ;-)


Don't worry, be happy, and error out, dude. (Translation: errors are fine, as are zeros. I just never thought of it when setting the challenge).
 
You must log in or register to reply here.
Back
Top