#1
|
|||
|
|||
Exlude Blank Fields using MIN in an ARRAY
=MIN(IF($A$2:$A$113>(TODAY()-90),(E2:E113))) Where column A contains dates and Column E contains values and blank cells.
=SMALL(IF($A$2:$A$113>(TODAY()-90),(E2:E113)),COUNTBLANK(E2:E113)+1) does not work either because I am counting all blank cells in column E, not just the last 90 days. |
#2
|
|||
|
|||
It's not clear what you're trying to do. Functions such as MIN and SMALL ignore empty fields. If that is not what you want, you can just add another column, fill it with references to the first column, and then take MIN or SMALL from there.
|
#3
|
||||
|
||||
Could you perhaps post a small sample file showing what you have and expected results. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
||||
|
||||
Perhaps replace the countblanks part with
=COUNTIFS(A2:A113,">(TODAY()-90",E2:E113,"") ( hope the syntax is correct I prefer sumproduct)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
I would use:
=MIN(IF(($A$2:$A$113>TODAY()-90)*($E$2:$E$113<>""),$E$2:$E$113)) It's an array formula so remember to hold down Ctrl and Shift before pressing Enter. |
#6
|
|||
|
|||
Perfect, thank you. I'm still not sure why it quits ignoring blanks when it's in an array, but that works.
|
#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 Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Convert String Array to Integer Array from a User Input? | tinfanide | Excel Programming | 4 | 12-26-2012 08:56 PM |
Form Fields - Create blank form text field with if statement? | LAssist2011 | Word | 5 | 12-14-2011 03:02 PM |
How to turn all blank lines into non-blank for legal forms | sieler | Word | 3 | 04-12-2009 01:38 PM |