Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #7  
Old 04-12-2016, 04:47 PM
xor xor is offline Exlude Blank Fields using MIN in an ARRAY Windows 10 Exlude Blank Fields using MIN in an ARRAY Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,101
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Try for a moment for clarity to change the formula to:

=MIN(IF(($A$2:$A$20>TODAY()-90)*($E$2:$E$20<>""),$E$2:$E$20))

Now try the following
Select the cell with the formula. In the formula field highlight exactly the following part of the formula
($A$2:$A$20>TODAY()-90) and press F9. You will see something like this
{FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE ,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FA LSE}
That is TRUE if the condition is met, FALSE otherwise. Press Escape.
Now in the same way highlight
($E$2:$E$20<>"") and press F9. Again you will see something similar to the above with TRUE where where condition is met and FALSE otherwise. Press Escape again.
Now you should highlight ($A$2:$A$20>TODAY()-90)*($E$2:$E$20<>"") and press F9. You will once more get an array with TRUE and FALSE. Remember that
TRUE*TRUE = TRUE which is converted to 1
TRUE*FALSE = FALSE which is converted to 0
FALSE*TRUE = FALSE which is converted to 0
FALSE*FALSE = FALSE which is converted to 0
That is you get an array something like:
{0,1,1,0,0,1,0,0,1,0,0,0,1,0,1,1,0,1,0}
with 1 where both conditions are met and 0 where zero or only one condition is met.
The formula now is reduced to something like:
=MIN(IF({0,1,1,0,0,1,0,0,1,0,0,0,1,0,1,1,0,1,0},$E $2:$E$20))
Finally this formula will return what is in the second position of E2:E20 because the second position in the 0/1 array is the minimum element that is 1 (TRUE).

You may also want to try the Evaluate Formula tool. Select the cell with the formula and press Evaluate Formula, then press Evaluate repeatedly.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Exlude Blank Fields using MIN in an ARRAY looking to persist and array or something vthomeschoolmom Word VBA 1 09-16-2015 02:12 PM
Array to iterate through variables and trap blank variables Marrick13 Word VBA 5 08-04-2015 06:19 AM
Exlude Blank Fields using MIN in an ARRAY Convert String Array to Integer Array from a User Input? tinfanide Excel Programming 4 12-26-2012 08:56 PM
Exlude Blank Fields using MIN in an ARRAY Form Fields - Create blank form text field with if statement? LAssist2011 Word 5 12-14-2011 03:02 PM
Exlude Blank Fields using MIN in an ARRAY How to turn all blank lines into non-blank for legal forms sieler Word 3 04-12-2009 01:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft