Next Assignment Due - Not Working (Strange behavior)

The formula in A3 of Assignment Schedule is intended to show the next assignment I need to work. The assignment with the nearest (to Today) 'Trial Date", with no date in the "Supplement Date" column,

If I understand correctly you want to treat "skipped" the same as "" (empty string).

If that is the case you could try array entering this in A3.

Formula:

Please Login or Register  to view this content.

While it behaves differently, it is not returning as

I've attached a test workbook with the formula which should fail if you will do this:
On Data dump, enter today's date in F14, clearing CFT as the next case to work.
Trustees Maritime Assn should show as the next case to work since its Supplement Date is empty, yet you should see the #NA error in A3.
Now, change the date in Data dump B15 to 1/9/2017 and you should see Trustees Maritime as the next case to work in A3.

If instead I also change B15 to today's date A3 returns Trustees Maritime Assn ... which is consistent with the logic of the previous post ...

What am I missing?

Trustees Maritime Assn should show as the next case to work since its Supplement Date is empty, yet you should see the #NA error in A3.

Which should it be? Indeed I see the #NA / Trustees Maritime... toggle as you describe. So with the understanding that it is to return Trustees Maritime.. also with 1/16/2017 in B15 try this.

Changed this

Formula:

Please Login or Register  to view this content.

to this

Formula:

Please Login or Register  to view this content.

and with today's date in F14 the return is Trustees Maritime Assn whether the date in B15 is 1/16/2017 or 1/9/2017.

I don't know how this will affect the rest of the project. I only know that it affects this particular combination of dates as above.

Is this what you are expecting?

Has anyone ever used "genius" and "FlameRetired" in the same sentence when talking about you?
Man o' man!!! I had a feeling it might only be a comma or some simple fix, since the formula was working up to recently.
And I fixed the formula in the next-door cell as well with this.

This is beautiful. Okay, so by the confused look on your face, above, you did see the strange behavior??

If you can give me another few moments, can you please explain what is going on with this formula and what your fix (greater than, equal to) does? I got the original formula from another expert.

Please Login or Register  to view this content.

I don't quite get the Match function here.

What does the "1" do as the lookup value?
How is the IF statement working here?

You're a genius FlameRetired!!

Thank you,
Delain

Thank you for the kind words and the rep.

I will do my best to explain. I would encourage you to study the presentation on arrays here.

http://xldynamic.com/source/xld.SUMPRODUCT.html

The reason I suggest this is because:

• This formula generates a rather complex array of TRUE / FALSE
• Any explanations I give here are going to be a bit "kludgy".
• That presentation greatly simplifies the concepts. (No "kludge")
• There is no need for me to re-invent the wheel.

Having said that, here comes the "kludge".

Let's start with the original formula. Type 1/9/2017 in B15.

=INDEX(CauseNMs,MATCH(1,(TrialDates=MIN(IF(SuppDates="",TrialDates)))*(Comm_or_Rez=$C$3)*(SuppDates=""),0))

Each factor in that blued part generates an array of TRUE / FALSE. Applying math operators to them coerce TRUE / FALSE into their underlying numeric values 1s and 0s. The effect of multiplying produces a logic AND ... more 1s and 0s. Again, check that link above out. It will become clearer.

In the formula bar select those blue parts above. Press the F9 function key and you will see this array.

{0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

The ";" are understood to be array row separators. MATCH attempts to match the first occurrence of 1 in that array and return the row number in which it is found. In this case 14. Since that is in the row argument of INDEX it tells INDEX to return the data in the 14th row of CauseNMs which is Trustees Maritime Assn ILA vs HCAD.

Change the date in B15 to 1/16/2017. You get the #N/A error. Perform the same selection and press F9 and you see this array.

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

It's all zeros. MATCH can find no 1s to match and returns #N/A.

Now use the revised formula,

=INDEX(CauseNMs,MATCH(1,(TrialDates > = MIN(IF(SuppDates="",TrialDates)))*(Comm_or_Rez=$C$3)*(SuppDates=""),0))

select the same section and press F9 now. The array is ...

{0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;1;1;0;1;1;1;1;1;1;0;1;1;1;1;1;1;1;1;0;1;1;1;0;0;0;1;1;1;1;1;1;0;0;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

Remember that MATCH attempts to match the first 1 in the array. Again that is row 14.

Now change the date in B15 back to 1/9/2017, do the select / F9 steps again and you will see the identical array with a match return of 14.

Another way to gain insight into formulas is use of Evaluate Formula ... Fx for short. Click FORMULAS in the ribbon > Evaluate Formula. Clicking the Evaluate button repeatedly reveals how Excel calculates step by step. In this case the array is large and the view port painfully small. There is a scroll bar though.

These two devices F9 and Fx are helpful for self instruction and formula dissection. I recommend them whenever I can.

That said and regarding the IF portion of the formula I would suggest copying that and in a temporary cell enter that formula section by itself and apply Fx. "Explanation" is likely more complex than I am prepared to do well. It is a component of one of the factors. The formula will explain itself better inside Fx.

I hope I didn't leave anything out. LOL

Did this help?

All I had was the sense of the formula and prior thread, the behaviors you described and the output you expected. After that it was a process of elimination.

Do you know what was causing the original formula to hang up with just the equal(=) operator?

After the filtering of the MATCH function there could be no TrialDates equal to 1/9/2017 unless B15 was changed to that. The only way I could see to get the earliest TrialDate with 1/16/2017 in B15 was to change the criteria to match the first date greater than or equal to the results of the MIN / IF.

I must admit my eyes glazed over several times examining the array build. Kudos to tigeravatar who did all the heavy lifting in that thread.

You're welcome. Thanks again for the feedback.

Hopefully a better understanding of the formula will come to me the more I use it.

Yup. That's been my experience.