View Single Post
 
Old 04-12-2016, 04:47 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
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