Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-11-2016, 01:42 PM
TomSv TomSv is offline Exlude Blank Fields using MIN in an ARRAY Windows 7 64bit Exlude Blank Fields using MIN in an ARRAY Office 2013
Novice
Exlude Blank Fields using MIN in an ARRAY
 
Join Date: Apr 2016
Posts: 2
TomSv is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 04-11-2016, 05:20 PM
Micromegas Micromegas is offline Exlude Blank Fields using MIN in an ARRAY Windows 10 Exlude Blank Fields using MIN in an ARRAY Office 2016
Novice
 
Join Date: Apr 2016
Location: Redmond, WA
Posts: 8
Micromegas is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 04-11-2016, 11:49 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Exlude Blank Fields using MIN in an ARRAY Windows 7 64bit Exlude Blank Fields using MIN in an ARRAY Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 04-12-2016, 03:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Exlude Blank Fields using MIN in an ARRAY Windows 7 64bit Exlude Blank Fields using MIN in an ARRAY Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 04-12-2016, 05:08 AM
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,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

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.
Reply With Quote
  #6  
Old 04-12-2016, 01:10 PM
TomSv TomSv is offline Exlude Blank Fields using MIN in an ARRAY Windows 7 64bit Exlude Blank Fields using MIN in an ARRAY Office 2013
Novice
Exlude Blank Fields using MIN in an ARRAY
 
Join Date: Apr 2016
Posts: 2
TomSv is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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.
Perfect, thank you. I'm still not sure why it quits ignoring blanks when it's in an array, but that works.
Reply With Quote
  #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,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
Reply

Thread Tools
Display Modes


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 05:42 AM.


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