hi
I need to find the minimum value in a range of just 5 cells but I want to exclude the lowest value
I need to know the earliest start time in a range of 5 start times but exclude where the value is midnight
So I want the return to be 07:00 in this example below
00:00 00:00 08:00 08:00 07:00
thanks in advance
Try this array formula..
Assuming your numbers are listed in A1:E1
Confirm with Ctrl+Shift+Enter and not just Enter
Please Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
Use this array formula:
=MIN(IF(A1:A5>0,A1:A5))
Confirm with Ctrl-Shift-Enter instead of the usual <Enter>
Hope this helps.
Pete
Last edited by Pete_UK; 10-04-2012 at 07:56 AM.
Another way:
Formula:
Please Login or Register to view this content.
Never use Merged Cells in Excel
=MIN(IF(A1:E1>0,A1:E1))
Array formula(C+Sh+En)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Don't you actually meanOriginally Posted by zbor
=SMALL(A1:E1,COUNTIF(A1:E1,0)+1)
Your formula will give incorrect results unless the desired result happens to be the mid-smallest (3 out of 5, 4 out of 7 etc.) value in the range
Yes, true.. Didn't test it and take conclusion on this particular case..
Thanks guys - for some reason I can't see all replies in normal mode but did in print mode - I've tried the =min(if( but am getting the old #Value error
Example attached!
Forum Min If.xls
That's because you didn't comfirm with ctrl+shift+enter like everyone told you.
Confirm this usisng Control+Shift+Enter. Not just Enter
You need to confirm with Ctrl +Shift+Enter and not just Enter
Once you do that, you should see {} around the formula. Dont type these in manually
Format cell to h:mm
Great - that works now thanks but
"like everyone told you" ???? It wasn't obvious sorry - that's why I asked for help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks