LinkBack URL
About LinkBacks
in column AD:3-700 i have a formula = =IF(R4="",B4,"") which if meets the conditions, gives me B which is a date... im trying to get the oldest date in the column however using this forumula =MIN(AD3:AD700) it just returns a blank cell... any ideas?
in column AD:3-700 i have a formula = =IF(R4="",B4,"") which if meets the conditions, gives me B which is a date... im trying to get the oldest date in the column however using this forumula =MIN(AD3:AD700) it just returns a blank cell... any ideas?
is the date a real date or text looking like a date?
see attached
Last edited by martindwilson; 12-19-2013 at 10:46 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Nope, all dates in column B are in the format date and ive formated all of AD to custom ddmmmyy;; - i can see it worked on your spreadsheet and im basically trying to do the EXACT thing you've done there and it just shows blank.. doing my head in
AFter abit of play around i think it has something to do with the range, it doesnt like =MIN(AD3:AD700) but if i manually do them it will do it e.g =MIN(AD3,AD4,AD5,AD6) and so on, however theres 700.. so i dont want to go through and manually do that lol
are you sure there isnt a 0 lurking in the range?
attach your workbook the may get a better idea,but its 3:30 am and i need some sleep!
Last edited by martindwilson; 12-19-2013 at 11:24 PM.
Are all cells filled with dates, or do some have 0 or ""?
maybe try this ARRAY formula....
=MIN(IF(A1:A12>0,A1:A12,""))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
min ignores any text including ""
MIN ignores text if it's part of an array or a cell reference.Originally Posted by martindwilson
This is a simplified example that may never arise but it demonstrates the difference:
=MIN(0,"text")
=MIN(0,{"text"})
=MIN(0,A1) where A1 = text
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Couldn't see any random 0's anywhere but FDibbins code worked a treat!, =MIN(IF(AD3:AD700>0,AD3:A700,""))
Thanks for your help guys, get some sleep matey its nearly christmas! you need to rest up for more booze drinking time!!
Merry xmas!!!!!!!!!!!!
Happy to help and thanks for the feedback (and the suggestion on resting for booze time - now, if only you can convince my wife of that? *jk*)
well that never cured anything! it would be good to find out why? what you needed to do was put =min($a$d3:ad3) in a spare column and fill down to row 700 and note the first cell that doesnt produce the correct minimum then examine that cell for the reason
Last edited by martindwilson; 12-20-2013 at 05:27 AM.
Another clue...
If the cell appears blank that tells me the formula is actually returning 0 and you either have the sheet set to not display 0 values or you have conditional formatting applied to hide the 0s.
The MIN function will always return some value, either a number or an error, but never a blank.
Here's a more realistic example...
You have an irregular data structure:
Data Range
We want to find the min value that corresponds to Yes.
You might use a formula like this:
=MIN(IF(A2="Yes",B2),IF(A6="Yes",B6),IF(D2="Yes",E2),IF(D6="Yes",E6))
However, that formula returns an incorrect result. Since no value_if_false arguments have been included in the IF functions that argument defaults to FALSE.
In this application the Boolean values are also not ignored unless they are part of an array or a cell reference and in the formula FALSE is being evaluated as 0.
So, we have to supply a value_if_false argument.
=MIN(IF(A2="Yes",B2,""),IF(A6="Yes",B6,""),IF(D2="Yes",E2,""),IF(D6="Yes",E6,""))
But now the formula returns the #VALUE! error. The MIN function is not ignoring the text formula blanks.
So, we need to force the text formula blanks to be evaluated as arrays:
=MIN(IF(A2="Yes",B2,{""}),IF(A6="Yes",B6,{""}),IF(D2="Yes",E2,{""}),IF(D6="Yes",E6,{""}))
This formula returns the correct result of 10.
This technique also applies to other functions that "ignore text" like MAX and AVERAGE. There may be a few others as well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks