MIN function ignoring values less then a certain number
Say I have an array with 56, 62, 68, 54, 49, 23, 58, 65, 27, 60
I want to find the MIN value but not include any of the values less than 30.
I've tried common ctrl shift enter array formula of
=MIN(IF(A1:B5>30,A1:B5))
but this results in 30, i.e. always the value set in the formula. So in the above question, the answer I want is 49.
Help please, thanks.
Not sure what you are doing wrong but your formula should work. See attached.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Control + Shift + Enter works for me also.
Last edited by skywriter; 03-02-2015 at 03:56 PM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
That formula should work. Under the formulas tab there is an Evaluate Formula button, that may help you find the problem. Or just post a workbook with sample data.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
LOL. Solution so simple yet hard to grasp. (least for me it was)
Turns out that formula does indeed work like everyone is stating.
Problem was that every single number exact I pick to put in the formula actually exists within the data base array!The array consists of around 1 million numbers.
Sometimes it takes a little bit of a wake up call to realize what is happening.
Thanks all.
Last edited by Destroy; 03-02-2015 at 04:18 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks