Greater Than
in [Functions]
|
From: Pete_UK on 17 Nov 2009 16:50 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 <m...(a)discussions.microsoft.com> wrote: > still will return 0.00 as the lowest number > > > > "Mike H" wrote: > > Hi, > > > Try this ARRAY formula > > > =MIN(IF(A1:A20>0.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 -
From: Harlan Grove on 17 Nov 2009 16:51
Glenn <addr...(a)not.valid> 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:A20>0.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(A1: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)) |