Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 20 Sep 2017 11:47:54 +0200color scale maximum and minimum formula for each rowhttps://ask.libreoffice.org/en/question/131493/color-scale-maximum-and-minimum-formula-for-each-row/So, this is the same as here:
https://ask.libreoffice.org/en/question/34342/copyable-conditional-color-scale/
which is closed with answer, but without solution.
What I want to achieve is color-scale format on rows, where red=min(row), yellow=mode(row), green=max(row)
If I try to repeat the steps you can use in 'cell value is; formatting option, I get different results:
- set up condition with 'all cells' option, green color to formula, =MAX($A2,$U2)
- set up yellow, red colors to their values
- save all settings, see that the 1st line is formatted correctly
- now open conditional formatting again, and for range select whole table
after these steps, values for 'cell value is' option will reference new row, and get new MIN, MAX values for each row.
But 'color scale' does NOT reference new row, and only first row is referenced every time for calculations.
Moreover, also tried trick mentioned in referenced question, but without success. I put =mode(row) into column Z for each row, and used:
=INDIRECT("Z"&ROW())
also points all the time to first row. which means that row() function evaluates every time to first row of a set
So, this is dead end for me, and I think this is a bug in color scale. I welcome all advices, because the table is really long, and only workaround seems to be adding formatting for each line manually.
ppetakWed, 20 Sep 2017 11:47:54 +0200https://ask.libreoffice.org/en/question/131493/Calc - conditional formatting based on month value from datehttps://ask.libreoffice.org/en/question/88768/calc-conditional-formatting-based-on-month-value-from-date/ One of the columns in my Calc spreadsheet is the date of each transaction, with the number type set to 'date'. I want these date cells to be grouped by having a background colour based on the month value - so all cells containing dates in January have one colour, those containing dates in February another colour, etc.
I thought I could use conditional formatting with the 'Color Scale (2 Entries)' option and a formula to get this to work but after wasting about an hour on this I haven't been able to get it to work. I'm not sure I understand how this is supposed to work. I've gone through the help documentation but that didn't provide any clues.
Using the 'Conditional Formatting' dialog for the range A3:A1048576 I've selected 'All Cells' and 'Color Scale (2 Entries)'. What formula do I put into the 'Formula' boxes for each colour to achieve this? I've tried '=(MONTH(A3)=1' and '=(MONTH(A3)=12' as the min and max values but there is never any change to the background colour.
What is the correct way to use a formula for specifying a colour scale in the conditional formatting dialog?PheebleTue, 28 Feb 2017 03:25:23 +0100https://ask.libreoffice.org/en/question/88768/Copyable conditional color scalehttps://ask.libreoffice.org/en/question/34342/copyable-conditional-color-scale/I have a (very large) confusion matrix which I would like to colorize with the 'Color Scale' conditional formatting.
Each row has a different number of inputs (e.g. the sum of each row is different).
The row sum is stored in a cell to the right of the last row value.
I want to color the cells from white to black, complete black only being used if the cell value is equal to the row sum.
I achieved this for the first row by applying a color range formatting with 2 entries.
For the 'left' one I put Value of 0 and color white, and for the 'right' one Formula with '=BC3' (which is the cell with the sum for the first row).
This works, but I have two problems:
1. The sum cell is colored in black, even though the range does not include it (it says C3:BA3, which is correct).
2. I can not copy this conditional formatting, because the formula value is not updated, but still points to BC3 for every other row (it should be BC4, BC5, etc.)
The first one is not that problematic, I could live with that.
But fixing the Formula value for each row is unfeasible, for I have more than 50 rows and many different matrices I would like to color.
Is there a way to enable the use of the Format Paintbrush in this case?fobenWed, 21 May 2014 21:43:21 +0200https://ask.libreoffice.org/en/question/34342/