OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Conditional Format for Smallest Numbers excluding 0

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
TengoKbailar
General User
General User


Joined: 19 Oct 2011
Posts: 9

PostPosted: Sun Jul 29, 2012 3:16 pm    Post subject: Conditional Format for Smallest Numbers excluding 0 Reply with quote

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
Back to top
View user's profile Send private message
CSLam
General User
General User


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

PostPosted: Wed Aug 29, 2012 11:39 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group