Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
Need to take a colum and find the smallest number in it but it must be
greater than 0.01 any help would be great |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
Hi,
Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
still will return 0.00 as the lowest number
"Mike H" wrote: Hi, Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
It returned 0.00, or you think it will so you didn't try it?
msao wrote: still will return 0.00 as the lowest number "Mike H" wrote: Hi, Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
Not in my testing.
Returns 0.010000001 as minimum but not 0.01 or less. How are your cells formatted? Gord Dibben MS Excel MVP On Tue, 17 Nov 2009 12:51:01 -0800, msao wrote: still will return 0.00 as the lowest number "Mike H" wrote: Hi, Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
Did you enter Mike's formula using CSE as advised?
Here's another take on what you might want: =MAX(MIN(A:A),0.1) Hope this helps. Pete On Nov 17, 8:51*pm, msao wrote: still will return 0.00 as the lowest number "Mike H" wrote: Hi, Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater Than
Glenn wrote...
It returned 0.00, or you think it will so you didn't try it? msao wrote: still will return 0.00 as the lowest number "Mike H" wrote: Try this ARRAY formula =MIN(IF(A1:A200.01,A1:A20)) .... "msao" wrote: Need to take a colum and find the smallest number in it but it must be greater than 0.01 any help would be great Mike's array formula would return 0 if there were no values in A1:A20 0.01. If there are no numbers in A1:A20 greater than or equal to 0.01, what should the formula return? If blank, =IF(COUNTIF(A1:A20,"0.01"),LARGE(A1:A20,COUNTIF(A 1:A20,"0.01")),"") or for Excel 2007 & later =IFERROR(LARGE(A1:A20,COUNTIF(A1:A20,"0.01")),"") OTOH, if it should return 0.01, =MAX(0.01,MIN(A1:A20)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Greater than | Excel Discussion (Misc queries) | |||
if less than greater than | Excel Discussion (Misc queries) | |||
greater than, less than, and in between | Excel Discussion (Misc queries) | |||
if greater than? | Excel Worksheet Functions | |||
Greater Than but Less than | Excel Worksheet Functions |