Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 19 Apr 2018 05:09:51 +0200Color Scale not scaling with max?https://ask.libreoffice.org/en/question/152650/color-scale-not-scaling-with-max/I'm sorry if the title isn't clear enough as I'm not familiar enough with calc stuff to explain it correctly. Right now I have two columns with numbers I would like to have a color scale applied to. In the first column they are simply values 1-10, so I use color scale with the min. green, yellow percentile 50, and max red. This works great, 1 and 2 are green, a 5 and 6 are yellow, 7 is kind of orange, and 9s are red. This is exactly what I want.
The problem is in the second column I have a larger number set, anywhere from 5 to 250. For whatever reason no matter what I do in the settings, at least to what I can figure out, it will not take the "Max" into consideration. With the default color scale settings same as the other column and 20 is yellow which is the exact same as a 250 and everything in-between.
I am not sure exactly how I am supposed to put the settings so it actually applies the entire color scale to it, like it does for the other column, since this is a much larger range of numbers.
Also I don't know why but I randomly put 200 for percentile just to test and see any changes, and for whatever reason when I use the scroll wheel on the mouse the colors actively change as I'm scrolling which seems very odd and the other column isn't doing that. But that's not an issue I'm worried about.
I could solve it by simply putting values strictly with 0-25 being green and 26-50 being yellow and 51+ being red but then I don't think it will scale the colors it will just be those 3 colors.
I'm sorry for any confusion as I only have limited experience with calc type programs which is why I'm asking.zeth07Thu, 19 Apr 2018 05:09:51 +0200https://ask.libreoffice.org/en/question/152650/color 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/