1

I am doing a nested min if array, and am having issue with it reading blanks.

=MIN(IF(Sheet1!$C:$C<=A24,IF(Sheet1!$AE:$AE>A24,Sheet1!$C:$C),IF(Sheet1!$C:$C<=A24,IF(Sheet1!$AE:$AE="",Sheet1!$C:$C))))

So in English, I'm asking that if the dates in sheet 1 column C are less than or equal to the value in A24, and the date in sheet 1 column AE is after the date in A24, OR the value in sheet 1 column AE is blank, give me the earliest date of what's left from column C. I hope that makes sense!

Any help would be greatly appreciated as I have spent literally hours on this trying isblanks, further nested if, all with no joy.

Improve this question
1
  • Do us a favor and post in your original post some sample data, so we do not need to create our own mock ups. Commented Feb 2, 2016 at 16:57

2 Answers 2

Reset to default
0

If you have Excel 2010 this AGGREGATE() Function will work:

=AGGREGATE(15,6,(Sheet1!C:C/((Sheet1!C:C<=A24)*((Sheet1!AE:AE>A24)+(Sheet1!AE:AE="")))),1)

If you have 2007 or earlier then the array formula should do it:

=MIN(IF(((Sheet1!C:C<=A24)*((Sheet1!AE:AE>A24)+(Sheet1!AE:AE=""))),Sheet1!C:C))

Being and array it must be entered with Ctrl-Shift-Enter instead of just Enter or Tab.

The issue is when using the or in arrays one should use +. So now if (Sheet1!AE:AE>A24) or (Sheet1!AE:AE="") are true it will return a 1 because 0+1=1.

Where the and portion is * because 0 * 1= 0. So both would need to be true to return the 1, or true.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Scott thanks for this, unfortunately im still getting the same issue. how do I attached sample data?
@Dan.B You could upload the file to dropbox, or a picture to imugr and then link to it in your original post.
Hi Scott, After continuing to try and get this to work I found a solution. rather than AE="", I did if(len(AE=0) and this has worked. thankyou for your time and help on this!
0

Do you have to use nested if's?

Why not have the if statement evaluate all three conditions. An OR function to check two conditions (check for blanks OR value in col AE is greater an A24. Then, have the result of the OR function feed into an AND function along with the third check (column C less than or equal to A24). Then if the entire logical check of the IF function returns TRUE, use a MIN function to give you the minimum value in Column C. Likewise, if the logical check of the IF function evaluates to False, do something else (ex. "No Match")

something like this:

=(IF(AND(COL_C <= $A$24, OR(COL_AE > $A$24, ISBLANK(COL_AE))),MIN(COL_C,"No Match"))

Improve this answer

1 Comment

Hi Waz, thanks for your time on this, unfortunately doesn't work either. I cannot put an OR in an array statement

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.