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

Can someone find an elegant method of...

P

Paul92688

New Member
I just watched a YouTube video Chandoo posted about a year ago. It was tips for IT users of Excel. One of the features was showing the forumula that would find the 2nd smallest and third smallest items in a list. In his example, he showed that if there were two equal items, one would be the smaller and the other would be found in the search numerically after. i.e.,

5,4,1,2,8,2

Smallest returns 1
Second smallest returns 2
Third smallest returns 2
Fourth smallest returns 4

The rational is that the list is sorted and the n'th number is returned.

In his example he uses a date, and the number and shows the date associated with the number. Problem is that even though there are two 2's only the date associdated with the first one is returned. I had a similar circumstance in a sheet I created a few days ago and got around the problem with a extremely clumsy formula. My guess is that a better Excel programmer has a great solution. I am obviously not that person.

In any case, here is a test sheet illustrating my question. I attached a JPG not knowing how people here feel about downloading spreadsheets from strangers. I know I don't like to.

The code behind the 2nd smallest number and date is in H4 and I4 and shown at the bottom of the image.

My solution was not to use a named table and start the search in the row after the prior search was found. It works but is clumsy, especially if the length of the list grows. I was tagging values that were repeated more than 3 times in the list.

Regards,

Paul






74692
 

Attachments

  • unnamed.jpg
    unnamed.jpg
    1.1 KB · Views: 1
But we prefer sample data in a real file, not a screenshot.

BTW: way to go, dad! 90 and still into Excel, WOW!!!

[H4]=SMALL(data[Attendance],ROW(H1))
[H5]=AGGREGATE(15,6,data[Date]/(data[Attendance]=H3),COUNTIF($H$3:H3,H3))
 

Attachments

  • chandoo 46343 - return smallest dates.xlsx
    18.4 KB · Views: 6
Last edited:
Life gets so much easier with Excel 365!
Code: 
= SORTBY(data, data[Attendance])
To pick out the first 10 (and reverse the column order), one can use INDEX. Dressed up with LET this appears as
Code: 
= LET(
  k, SEQUENCE(10),
  sortedData, SORTBY(data,data[Attendance]),
  INDEX(sortedData, k, {2,1}) )
 
GraH - Guido said:
But we prefer sample data in a real file, not a screenshot.

BTW: way to go, dad! 90 and still into Excel, WOW!!!

[H4]=SMALL(data[Attendance],ROW(H1))
[H5]=AGGREGATE(15,6,data[Date]/(data[Attendance]=H3),COUNTIF($H$3:H3,H3))
Click to expand...

Thanks so much Quido. I am sorry not to have posted the sheet. I hate downloading other's sheets because you never know what you might pick up. I will post the sheet from this point on. The ROW() is fast and easy. I often avoid unless it is a long list because I worry if I cut/paste the formulas and forget to check the ROW reference it can cause problems. I will consider using it more often. The AGGREGATE() is unknown to me. I am pretty up to date with Excel from the 90's but I am guessing this is a more recent addition to the program. I will have to read up on the formula as I am completely unfamiliar.

Thanks for your help and thanks for the introduction of the new ( to me ) function.

Paul
 
Peter Bartholomew said:
Life gets so much easier with Excel 365!
Code: 
= SORTBY(data, data[Attendance])
To pick out the first 10 (and reverse the column order), one can use INDEX. Dressed up with LET this appears as
Code: 
= LET(
  k, SEQUENCE(10),
  sortedData, SORTBY(data,data[Attendance]),
  INDEX(sortedData, k, {2,1}) )
Click to expand...

Peter, thanks for the info. I have recently learned about the LET() function. Not familiar with SEQUENCE(). I am assuming that the function returns an array of numbers from 1-10? I also seldom use the INDEX() function. I will study up both of those. I understand creation of vector arrays assuming that is what these functions do. 5050 = +\⍳100 is a fun one in APL. The slash might be backward... It's been a few years for me.

Thanks again.

Paul
 
Paul92688 said:
Peter, thanks for the info. I have recently learned about the LET() function. Not familiar with SEQUENCE(). I am assuming that the function returns an array of numbers from 1-10? I also seldom use the INDEX() function. I will study up both of those. I understand creation of vector arrays assuming that is what these functions do. 5050 = +\⍳100 is a fun one in APL. The slash might be backward... It's been a few years for me.

Thanks again.

Paul
Click to expand...

Given that Excel and APL are both numbers orientated, I just discovered something today that shouldn't have surprised me as it did...

5050 =SUM(SEQUENCE(100)) a heck of a lot easier to read than APL

PJ - I am going to love this forum!
 
Peter Bartholomew said:
Life gets so much easier with Excel 365!
Code: 
= SORTBY(data, data[Attendance])
To pick out the first 10 (and reverse the column order), one can use INDEX. Dressed up with LET this appears as
Code: 
= LET(
  k, SEQUENCE(10),
  sortedData, SORTBY(data,data[Attendance]),
  INDEX(sortedData, k, {2,1}) )
Click to expand...
It does, doesn't it?... DA functions are still to be activated at work. Always forget I've got them onboard at home.
 
[QUOTE="Paul92688, post: 276064, member: 76002]
PJ - I am going to love this forum!
[/QUOTE]
Who doesn't? Welcome Paul. Glad we could help and introduce you to more modern functions. Well, Peter did...
 
Hi @Guido It is the other way for me. Without the anchor of company practice, I am gradually forgetting the ways of the past. I now feel a distinct sense of failure if the result cannot be expressed as a single dynamic array!

@Paul Spreadsheets are somewhat idiosyncratic in that the only data type they support is the 2D array. Not that you would guess that from examining the typical spreadsheet solution! This is because formulas on the grid play some mathematically strange tricks. If you reference a 10x1 array X on the worksheet, the formula interpreter assumes you really want the 1x1 range where the formula row intersects the column of the variable, so giving X(5), say. Only if you commit the formula with CSE would legacy Excel accept that you meant what you wrote and intended to process an entire array.

Now, in Excel 365, the default is reversed and a formula written to a single cell can calculate an array result and cause it to display across adjacent cells. The INDEX formula is just a slightly cumbersome notation that returns X(I, J) by using the notation
= INDEX(X, I, J)
The name is also misleading in that the function does not return an index, but, rather, returns an element of the array using the indices. Despite that, it works well enough and has a further interesting feature in that it returns a reference to the cell and not merely its value.

In future, it will be possible to build your own functions by writing formulas, thus
= LAMBDA(r, c, INDEX(X, r, c))(5,1)
would substitute 5 and 1 for the dummy variables / names and return the reference X(5). If you then defined a name Xλ (say) to refer to the part of the formula written in orange, the name can be used as a function, to give
= Xλ(5,1)

It is an interesting time to get involved with Excel! According to you nature, you might find that to be a blessing or a curse.
 
Peter Bartholomew said:
The name is also misleading in that the function does not return an index, but, rather, returns an element of the array using the indices.
Click to expand...
INDEX uses an index as input and MATCH returns an index. Somehow this has always felt strange to me.

Peter Bartholomew said:
I now feel a distinct sense of failure if the result cannot be expressed as a single dynamic array!
Click to expand...
Now there is a surprise ;-).

Peter Bartholomew said:
It is an interesting time to get involved with Excel! According to you nature, you might find that to be a blessing or a curse.
Click to expand...
When was the last time so many new features/functions arrived in Excel? Frustrating it is, when you see all those MVPs and forum posts that solve challenges using DA, let and even lambda. So for the time being I go with #curse! Not really ;-).
I guess it depends on the line of work, task at hand why and when people adopt those features. If you can solve the challenge with what you know, even if that's not the most optimal way, many just accept that status quo. Learning new stuff takes time.
Personally, I'm evolving more and more towards Power Query and PowerPivot in Excel and I even started experimenting - loads of failure - with PowerBI. I don't see how I could use f.e. DA to my advantage.
 
GraH - Guido said:
But we prefer sample data in a real file, not a screenshot.

BTW: way to go, dad! 90 and still into Excel, WOW!!!

[H4]=SMALL(data[Attendance],ROW(H1))
[H5]=AGGREGATE(15,6,data[Date]/(data[Attendance]=H3),COUNTIF($H$3:H3,H3))
Click to expand...

The AGGREGATE function is slick. It took me a while to digest it as this is the first time I heard about the function. The divide by test is great. It took me a while to figure out what kind of argument would involve a division but then I realized you were testing for equality and a DIV0 error excluded the number. Brilliant.

Paul
 
That is the spirit, Paul, studying the solutions offered is one of the best ways to learn about functions and formulae writing techniques.
On this forum you might consider following @bosco_yip* , @Excel Wizard , @John Jairo V and @Peter Bartholomew and settle in your role as formulae sorcerer apprentice.
I do ommit many others - sorry ladies and gentlemen - who are way better at it then I am, but that is just a way too long list.

* one of the listed resources who helped Mike "Excel-is-Fun" Girvin writing his book "Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic".

Now guess where I learned 'bout Aggregate ( ).
 
You must log in or register to reply here.
Back
Top