![]() |
#7
|
|||
|
|||
![]()
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. |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
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 |
![]() |
tinfanide | Excel Programming | 4 | 12-26-2012 08:56 PM |
![]() |
LAssist2011 | Word | 5 | 12-14-2011 03:02 PM |
![]() |
sieler | Word | 3 | 04-12-2009 01:38 PM |