[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
TengoKbailar
General User

Joined: 19 Oct 2011
Posts: 9

 Posted: Sun Jul 29, 2012 3:16 pm    Post subject: Conditional Format for Smallest Numbers excluding 0 Hi. I have a column of numbers and I am trying to highlight the smallest numbers excluding 0 I have tried IF(AND(AA358>0,AA358<=SMALL(AA\$3:AA358,AA359))) I then use the format paintbrush on the whole column which leaves all the 0s without any format but 0 is still being counted in SMALL and each 0 is being counted individually so if there are 20 X 0s I do not see any format until I enter 21 into AA359 and I am looking for the smallest 10 I have searched and Googled for an answer but not found one so I am hoping that someone can help me with this. Thanks Michael
CSLam
General User

Joined: 08 Aug 2012
Posts: 29
Location: Hong Kong

 Posted: Wed Aug 29, 2012 11:39 pm    Post subject: For locating the smallest number, I can think of two solutions :- (1) If your column is just a column of values, use Global Replace changing all "0" to nul. In such a way, the MIN() function will only report the smallest value next to zero. (2) If your column contains at least some formula, which can calculate to zero, try using this :- MIN(IF(A1:A1048576=0,999,A1:A1048576)), where (i) A1:A100 is the column being considered; (ii) 999 an arbitrary number big enough not to be the smalleest value; (iii) this is an array function: after entering the formula, you will need to hold down both Shift & Control together, then press the Enter key. You should see {MIN(IF(A1:A100=0,999,A1:A100))} in the status bar, ie. the formula enclosed by curly brackets. I hope this helps. CSLam
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum