Re: [libreoffice-users] Re: Multuple rules/conditions to range of cells
On 6 Jun 2011 at 21:59, Andreas Säger wrote: To: users@libreoffice.org From: Andreas Säger Subject:[libreoffice-users] Re: Multuple rules/conditions to 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. > > > > =(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. > > > -- > Unsubscribe instructions: E-mail to users+h...@libreoffice.org > In case of problems unsubscribing, write to postmas...@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:mi...@kuentos.guam.net mailto:msetze...@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 SETI10840698.799905 | EINSTEIN 5978952.530851 ROSETTA 3215702.700203 | ABC 6158864.767707 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
[libreoffice-users] Re: Multuple rules/conditions to range of cells
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. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
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 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 to 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+h...@libreoffice.org > In case of problems unsubscribing, write to postmas...@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:mi...@kuentos.guam.net mailto:msetze...@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 SETI10840698.799905 | EINSTEIN 5978952.530851 ROSETTA 3215702.700203 | ABC 6158864.767707 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
Re: [libreoffice-users] Re: Multuple rules/conditions to range of cells
Hello Andreas, On Mon, 06 Jun 2011 11:00:26 +0200 (your time) you said: > 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. :-) -- Si (PLO) #17510. Rho Win Wed Egos? ¶ Auxiliary Information: • LibreOffice 3.4.0 OOO340m1 (Build:12) • Windows XP Pro 5.1.2600 Service Pack 3 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
[libreoffice-users] Re: Multuple rules/conditions to range of cells
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. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
[libreoffice-users] Re: Multuple rules/conditions to range of cells
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< 2001Red => 2001< 3001Blue/Bold => 3001< 4001Blue => 4001< 5001Orange => 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 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 to 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+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@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
