Returning Task Name based on a minimum number in Helper that is >0

Ben Donahue

I have previously asked a similar question, but with fewer requirements.

I am trying to return the Task Name associated with the lowest number in a helper column that is >0.

With some help from L@123, I was able to get close, I think, except the following code returns a zero, and there are no zeros in CHILDREN([Task Name]$22):

=INDEX(MIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN([Helper]$22), >0)), 1)

Your help and instruction is greatly appreciated.

Mike Wilday

I wonder if its counting blank cells as 0's? Can you check your data and make sure every cell has a number? Maybe work it out on a test sheet?  

Paul Newcome

It is returning a zero because of the MIN function. The MIN function will always return a number.

 

Let's see if a different approach will work...

 

=JOIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), @cell = MIN(COLLECT(CHILDREN(Helper$22), CHILDREN(Helper$22), @cell > 0))), ", ")

Ben Donahue

There are no blank cells in scope. I haven't yet tried the test sheet approach. I did, for reasons I can't fathom nor remember, try this piece of code that worked, for reasons of which I am unaware:

=INDEX(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), >0), 1)

Here is my thinking (please feel free to correct my understanding as it strays from reality):

The COLLECT function will return, in this case, a list or array; multiple datums in the single return, anyway. The INDEX function is defaulting to the first entry, which, since the data is structured in the sheet in ascending order, is always the MIN, so it works...in this instance with this prearranged data structure.

Ben Donahue

Paul, 

I am getting a #NESTED CRITERIA error. I will eventually need a JOIN, I believe, because, at some point, the return will contain two datums, as one date will mark two milestones, so I am very interested in seeing this through. I will get into parsing out what your code is saying later today.

Here is the code that eventually worked for me:

=INDEX(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), >0), 1)

I asked Mike to chime in on my (mis-) understanding of why this code is working, and I very much invite your thoughts on this:

Here is my thinking (please feel free to correct my understanding as it strays from reality):

The COLLECT function will return, in this case, a list or array; multiple datums in the single return, anyway. The INDEX function is defaulting to the first entry, which, since the data is structured in the sheet in ascending order, is always the MIN, so it works...in this instance with this prearranged data structure.

I also have no idea why the code I posed in the original post is returning a zero, so, any thoughts you have on that would be enlightening, too.

Thank you.

Paul Newcome

Your first formula is returning a zero because you have the main return being the MIN function. MIN will always return a number, so since there are no numbers in your Task Name column, it is going to return a zero.

 

I am looking into the nested criteria error now to see if there is a way around that.

Paul Newcome

You are correct in your thinking about the INDEX formula. It is pulling the MIN because you have the sheet sorted so that the lowest number is at the top.

 

If you were to replace the 0 in your helper column with some kind of text, even "0" or the text "zero", would that cause issues elsewhere? 

Ben Donahue

I added a string and adjusted the date such that it appears before the next scheduled real, not test, event. The result was unchanged. So, the formula only found numerical entries, skipping the string entirely, as is shown in the second screen shot.

So, to answer your question directly, no, it seems not to have any effect.

Paul Newcome

Ok. So if you replace ONLY the number zero with some kind of text, you will then be able to remove the COLLECT function from the MIN which is where the nested criteria error was coming from.

 

So leave all of the numbers above zero as numerical values and change your zeros to some form of a text string.

 

Then you can use 

 

=JOIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN(Helper$22), @cell = MIN(CHILDREN(Helper$22))), ", ")

Ben Donahue

I see. It seems I didn't at all know how the INDEX formula worked. I thought that the index of the returned MIN would be the Task Name, being the Primary Identifier. Of course, indices are always integers, so, my bad on that.

Now I understand, I think, that

=INDEX(MIN(COLLECT(CHILDREN([Task Name]$22), CHILDREN([Helper]$22), >0)), 1)

will look through the children of Helper22, return the Task Name entries associated with the children of Helper22 that are >0, find the minimum numerical value of this list of strings (which defaults to 0), then, with a range of the number 0 (which corresponds to no columns) it will return the first entry in that range, essentially an empty set.

Is that right?

Paul Newcome

Using CHILDREN([Task Name]$22) as the range to COLLECT from is why your MIN is returning a zero. If there are no numbers within a range, MIN will default to 0.

 

Using MIN(COLLECT( means you are collecting for the MIN function when in all reality you are wanting to COLLECT for the INDEX function.

 

=INDEX(COLLECT(CHILDREN([Task Name]$22]), CHILDREN(Helper$22), @cell = ...............................)

.

The problem is that you will need an additional COLLECT function in the MIN where the .................. is to specify the MIN of everything over 0.

 

That COLLECT inside of the COLLECT is where the #NESTED CRITERIA error occurs. 

 

You also stated that you would eventually need a JOIN function because there could be more than 1 task within the range that matches that lowest number above zero.

.

So the solution would be to remove the numerical value of zero from the helper column altogether so that we can remove the nested criteria issue of having to COLLECT inside of the COLLECT.

.

Then we replace the INDEX with a JOIN and swap the row number with a delimiter, and that should work for you.

.

How are the numbers in your Helper column being populated?