Multuple rules/conditions to range of cells

users
#1

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.

#2

There's a STYLE() function. Could it be of any use here?

#3

Hello Jean-Francois,

There's a STYLE() function. Could it be of any use here?

Thankyou! You pointed me in the right direction and I finally got there...I
think :smiley: Just been testing it and this seems to work:

=SUM((N13+L14)-(P14+R14))+STYLE(IF(CURRENT()<1000,"BPC1";IF(CURRENT()<=2000,"BPC2";IF(CURRENT()<=3000,"BPC3";IF(CURRENT()<=4000,"BPC4";"BPC5"))

although Calc instantly coverts the above to:

=SUM((N13+L14)-(P14+R14))+STYLE(IF(CURRENT()<1000,"BPC1",IF(CURRENT()<=2000,"BPC2",IF(CURRENT()<=3000,"BPC3",IF(CURRENT()<=4000,"BPC4","BPC5")))))

which, from what I can see, works.

Took me a while to work out how to nest the IFs.

#4

I don't know why I got this e-mail. Please don't send me such e-mails again.

I do have a question. I copied a book that I wrote with MS Word on to
LibreOffice, but none of the pictures transferred, only boxes where the pictures
would be. The computer on which I wrote the document ran Win XP Home Edition.
I transferred all my files to this new computer, runs Win 7, downloaded
LibreOffice and copied and pasted the book to LibreOffice. No pictures. Much
frustration as there are many pictures. What's the fix? Or, will someone fix
this problem in LibreOffice. It must be a common one.
Lynn

#5

Thank you so much, Jean-Francois. The only information given about joining this
was that I could post a question, nothing was said about receiving e-mails of
every question posted. If that had been made clear, I would not have joined.

Your answer to my question, I'm afraid, makes no sense to me because I do not
know where I should look for "Insert>Images, etc." original MS Word document or
the one I copied to Libre Office? With the original document, as I changed
picture sizes, the whole document reformatted to accommodate changes. In the
Lib. Off one I have only black boxes with red lettering inside showing some type
of designation for each picture. Can you clarify your response considering the
above information? Often I have problems with help because the helpers assume
I have more technical knowledge than I do. I'm just a retired teacher who uses
e-mail, writes with WORD and does web searches. I have no technical computer
background.
Lynn

#6

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.

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.
0 Red_Bold
1000 Red
2000 Blue_Bold
3000 Blue
4000 Orange
5000 Default
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

#7

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))

Second method is much easier but resticted to the formula cells you mentioned.
1. Create your set of cell styles as in the above solution.

2. =SUM((N151+L152)-(P152+R152))+STYLE(VLOOKUP(CURRENT();$X$1:$Y$6;2)
Function STYLE returns zero, so the cell value won't be affected.

#8

Hello Andreas,

Second method is much easier but resticted to the formula cells you
mentioned.
1. Create your set of cell styles as in the above solution.

2. =SUM((N151+L152)-(P152+R152))+STYLE(VLOOKUP(CURRENT();$X$1:$Y$6;2)
Function STYLE returns zero, so the cell value won't be affected.

Thanks. I shall give that a try that later today. :slight_smile:

#9

Hello Lynn,

I don't know why I got this e-mail. Please don't send me such e-mails again.

As Jean-Francois has already replied, you received my message reply because
you subscribed to the LibreOffice mailing list. So it was not my intention
to SPAM you ;-), but then it is not within my power to prevent you receiving
replies made to this list so you'll have to unsubscribe from the list if you
don't want to receive any replies from me again. :slight_smile:

I do have a question. [...]

Although Jean-Francois has already replied to your question, when (or if)
you ask another question you would really be better off starting a new
thread rather than replying to an existing one.

When you reply to an existing thread that has a specific question in the
message subject line your own question would inevitably become 'lost' in a
thread that was started to ask a different question. That's because each
email message sent contains a unique message ID which email clients are able
to use to 'thread' related messages. If you reply to an existing message and
the subject line is unmodified no one will know that your reply doesn't
relate to to the original question, and if mailing list users are ignoring a
thread your question may be ignored as well.

Hope this helps.

#10

Hi :slight_smile:

The "Insert>Images etc" refers to the menus at the top of the Writer window.
Note that the menus at the top are
File Edit View Insert .... Help
So click on the Insert menu. The menu drops down and contains similar items
grouped together rather than being alphabetical. The various "Picture" (or
"Image" in other releases and Word i guess) are near the bottom. "Object" just
below it is for video or sound. If you select "Picture" then a sub-menu appears
offering "From File" or "Scan ...". The "Scan ... " option is for
scanner/printers so you probably want the "From file" choice. A 'pop-up'
'dialogue box' will appear so that you can navigate to the picture/image you
want to include.

Some of the terms are quite geeky but if you follow the instructions then you
will quickly understand what they mean.

Good luck and regards from
Tom :slight_smile:

From: Lynn Wilde <life48dancer@att.net>
To: users@libreoffice.org
Sent: Mon, 6 June, 2011 8:19:26
Subject: Re: [libreoffice-users] Multuple rules/conditions to range of cells

Thank you so much, Jean-Francois. The only information given about joining
this

was that I could post a question, nothing was said about receiving e-mails of

every question posted. If that had been made clear, I would not have joined.

Your answer to my question, I'm afraid, makes no sense to me because I do not

know where I should look for "Insert>Images, etc." original MS Word document
or

the one I copied to Libre Office? With the original document, as I changed
picture sizes, the whole document reformatted to accommodate changes. In the

Lib. Off one I have only black boxes with red lettering inside showing some
type

of designation for each picture. Can you clarify your response considering
the

above information? Often I have problems with help because the helpers
assume

I have more technical knowledge than I do. I'm just a retired teacher who
uses

e-mail, writes with WORD and does web searches. I have no technical computer

background.
Lynn

________________________________
From: Jean-Francois Nifenecker <jean-francois.nifenecker@laposte.net>
To: users@libreoffice.org
Sent: Sun, June 5, 2011 10:59:33 PM
Subject: Re: [libreoffice-users] Multuple rules/conditions to range of cells

> I don't know why I got this e-mail. Please don't send me such
> e-mails again.

You get the emails from this list because you decided to subscribe :slight_smile:

Subscribing to a mailing list such as this one has many advantages: you may
ask

questions and read the answers and you may answer the questions for which you

know the solution. Mailing lists are very valuable mutual help sources.

Would you decide to unsubscribe, just have a look at the bottom of the
messages,

where the mailing list server adds a few information:

Unsubscribe instructions:

Send a (empty) message to the server at E-mail to users+help@libreoffice.org.
A

few moments after, you should receive an email giving more details about the
unsubscription process.
In short, you'll have to send a (empty) message to the unsubscription address

then wait a minute for an answer from the server. Then you'll have to answer
back (another empty message) to that last message in order to confirm your
desire.

Be aware that:
a. You'll have to run the unsubsription process using the very address you
used

for subscription;
b. Your mailing program could consider the answers from the server as being
spam. If you don't seem to receive any answer within a few minutes, just look

there, just in case.

Now, back to your question...

>
> I do have a question. I copied a book that I wrote with MS Word on
> to LibreOffice, but none of the pictures transferred, only boxes
> where the pictures would be. The computer on which I wrote the
> document ran Win XP Home Edition. I transferred all my files to this
> new computer, runs Win 7, downloaded LibreOffice and copied and
> pasted the book to LibreOffice. No pictures. Much frustration as
> there are many pictures. What's the fix? Or, will someone fix this
> problem in LibreOffice. It must be a common one. Lynn
>

Embedding images within a document can be made under two options:

1. The images are actually made part of the document, causing it to get bigger

and bigger, according to the images size. The advantage being that you've got
an

all-in-one item that is easy to carry along.

2. They can be just *linked* to the document. This way, the document size
remains small. The drawback is that you have to carry the whole lot (document
+

images) together everytime you want to move things around.

Now for two new questions...

-- How do we opt for 1.or 2.?

Look at the Insert > Images > From file dialog. You'll notice there's a
checkbox

saying something like "Insert as link" (not sure about the English phrasing as

I'm using a French version). Make sure it is UNchecked to embed the images
within the document.

Note that this checkbox state is saved from a session to another. This can
lead

to some "strange" behaviour if it was (un)checked by mistake once.

If the images size is not gigantic, I'd suggest embedding them within the
document.

-- What if the images are simply linked and you want to store them in the
document?

Go to the Edit > Links dialog, select the images (multi selection is possible)

and click the "Unlink" button (once again, I'm not sure about the exact
wording

of the button but you get the idea). Wait a few moments and you're done.

Save.

Hope this helps,
-- Jean-Francois Nifenecker, Bordeaux

-- 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

--
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

#11

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

#12

Hi Lynn,

I do have a question. I copied a book that I wrote with MS Word on to
LibreOffice, but none of the pictures transferred, only boxes where the pictures
would be. The computer on which I wrote the document ran Win XP Home Edition.
I transferred all my files to this new computer, runs Win 7, downloaded
LibreOffice and copied and pasted the book to LibreOffice. No pictures. Much
frustration as there are many pictures. What's the fix? Or, will someone fix
this problem in LibreOffice. It must be a common one.

Please check the following setting within LibreOffice:
Tools > Options > LibreOfficee Writer > View
Is there a checkmark in front of the text "Graphics and Objects" under Display? If not, check it and see, if the pictures appear in your text.

Sigrid

#13

Hi :slight_smile:
You should be able to just double-click on documents that were written in Word
and they should just open in LibreOffice now. If not then right-click on the
Word file and choose "Open with ..." and then find LibreOffice or Writer in the
list. It is smart to create a back-up copy before doing something unfamiliar
just in case something unexpected happens. So that was still a smart move.

Please note that a lot of people on this list like to post their answers at the
bottom of a thread. So Sigrid's idea is worth trying.

Good luck and regards from
Tom :slight_smile:

#14

Am 06.06.2011 15:12, Michael D. Setzer II wrote:

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?

Difficult to tell without concrete 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.

Looking up the style name has 2 advantages: You can easily modify the style names in the stylist and in the lookup table and you can modify the threshold values in one place for all calculations including conditional formattings. Quite often such a lookup table includes more than 2 columns. Finding all occurrences of a particular format conditon can be difficult.

#15

range of cells
Date sent: Mon, 06 Jun 2011 21:59:48 +0200
Send reply to: users@libreoffice.org

Am 06.06.2011 15:12, Michael D. Setzer II wrote:
>
> 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?
>

Difficult to tell without concrete formula.

I had an earlier formula that was working using the reference to a
cell like A1, but when I replaced that with current(), it did not work
correctly. I assuming that since the current() was inside the other
functions that it was giving some other value.

The lookup options is also something I've used, and it is very
useful, but just another option that is available.