Re: [libreoffice-users] Re: Multuple rules/conditions to range of cells

On 6 Jun 2011 at 10:55, Andreas Säger wrote:

To: users@libreoffice.org
From: Andreas Säger <villeroy@t-online.de>
Subject: [libreoffice-users] Re: Multuple rules/conditions to 
range of cells
Date sent: Mon, 06 Jun 2011 10:55:43 +0200
Send reply to: users@libreoffice.org

> Am 05.06.2011 18:47, PLO wrote:
> > Hello LO Users,
> >
> > I've been trying - without success - to apply five separate 
conditional
> > formatting rule to a column based on their value. Each of the 
365 cells in
> > the column contain a simple formula. E.g
> >
> > =SUM((N151+L152)-(P152+R152))
> >
> > I need the result in each of the cells to be formatted in 
different colour
> > if it is over or below a certain value. I need five rules in total. 
So
> > formatting would be:
> >
> > < 1000 Red/Bold
> > => 1000< 2001 Red
> > => 2001< 3001 Blue/Bold
> > => 3001< 4001 Blue
> > => 4001< 5001 Orange
> > => 5001 Default
> >
> > I have used conditional formatting with success, except that 
it is limited
> > to three conditions...which isn't enough.
> >
> > In MS Office 2007 I can apply any number of rules to a range 
of cells
> > simply by selecting Conditional Formatting from 'Styles' on 
the Ribbon and
> > adding a rule for each.
> >
> > Maybe there is another way in LibreOffice but I can't find it.

I've tried another method that seems to give the results. 
Unfortunately, it appears the current() doesn't give the results of 
what is before the style when used in a formula?

=(A1)+STYLE(CHOOSE(MIN(INT(((A1)-((A1)
> 1000))/1000)+1,6),"Red_bold","red","blue_bold","blue","orange","default"))

I used a single cell for the formula, but the (A1) could be replaced 
with the formula (SUM((N151+L152)-(P152+R152))) or 
(N151+L152-P152-R152) to give the same results. 

Would have been simpler if all the options where < multiples of 
1000 or all <=. I also added max and min, so a negative or higher 
value would fall into the ranges. 

I created styles with the matching names giving the styles.



> This requires some preparing set up work.
> 1. Create a set of 5 cell styles comprising the attributes you 
want to 
> see. (hit F11, right-click>New...)
>
> 2. Take some free cell range of 6 rows and 2 columns, say 
$X$1:$Y$5.
> 0Red_Bold
> 1000Red
> 2000Blue_Bold
> 3000Blue
> 4000Orange
> 5000Default
> First column has threashold values, second column has your 
style names 
> which represent the formatting attributes you want to see up to 
the 
> respective threashold.
>
> 3. Select the cell range you want to color. Notice the row 
number of the 
> currently active input cell.
>
> 4. menu:Format>Conditional...
> Change <Value Is> to <Formula Is> for condition #1.
> Formula: STYLE(VLOOKUP($H1;$X$1:$X$6;2))
> (assuming the active cell in row #1, compare values in column H 
and the 
> compare list in X1:X6).
> For the active cell in row 99 and your compare values in column 
M the 
> formula would be:
> STYLE(VLOOKUP($M99;$X$1:$X$6;2))
>
> Greetings,
> Andreas
>
>
> -- 
> Unsubscribe instructions: E-mail to users+help@libreoffice.org
> In case of problems unsubscribing, write to 
postmaster@documentfoundation.org
> Posting guidelines + more: 
http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/www/users/
> All messages sent to this list will be publicly archived and 
cannot be deleted
>


+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:mikes@kuentos.guam.net                            
  mailto:msetzerii@gmail.com
  http://www.guam.net/home/mikes
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer 
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
SETI        10840698.799905   |   EINSTEIN     5978952.530851
ROSETTA      3215702.700203   |   ABC          6158864.767707


-- 
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted