LinkBack URL
About LinkBacks
Hello Friends
Please find the attached 2013 version file <Minimum value lookup for 2 conditions excluding zeros 260515 sss.xlsx>
I want to look for the minimum value for the given 2 conditions by omitting zeros.
thanks in advance
thilag
Hello Friends
Please find the attached 2013 version file <Minimum value lookup for 2 conditions excluding zeros 260515 sss.xlsx>
I want to look for the minimum value for the given 2 conditions by omitting zeros.
thanks in advance
thilag
This formula should work in H6 entered as an array.
=MIN(IF((B:B=F6)*(C:C=G6)*(A:A>0)>0,A:A,""))
Last edited by nigelbloomy; 05-26-2015 at 09:57 AM.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
hey dear ur file is not getting viewed or download..pls re attach the file
Try this array formula:
...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.=IF(MIN(IF(B:B=F6,IF(C:C=G6,IF(A:A<>0,A:A,""),""),""))=0,"",MIN(IF(B:B=F6,IF(C:C=G6,IF(A:A<>0,A:A,""),""),"")))
Quang PT
Actually this one will take care of the errors and make sure that blanks appear instead of zeros. It is also an array formula.
=IFERROR(SMALL(IF((B:B=F6)*(C:C=G6)*(A:A>0)>0,A:A,"x"),1),"")
This in an array formula and so must be confirmed with CTRL+SHIFT+ENTER and not just ENTER than drag down=MIN(IF(($B$1:$B$100=F6)*($A$1:$A$100<>0)*($C$1:$C$100=G6),$A$1:$A$100))
Change cell format (for H column)
Cell Format :[=0]"";General
Hello Friends
All of your formulas working fine.
thanks
thilag
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks