Description: I want to format cell values in green (lower) or red (higher) compared to cells containing values for comparison. Attached is a calc document containing two rows of values where the first row has a conditional formatting rule applied. It is expected the cells in the first row are colored red and green based on the result of the formatting rule - the rule covers the whole cell range with values in this row: On a lower value compared to the cell from the second row the cell background is set to green and on a higher value set to red. The coloring ends after the first cell. After reading the documentation page to this funtionality I'm not sure if the created rule is the correct way to do this, but in my understanding of the documentation it should be. Best Alex Steps to Reproduce: 1. Create the formatting templates from two cells where the first cells background is colored green and the second cell background for the second formatting rule is colored red. Name each formatting template depending on the color: for red give the name "Higher" and for green the name "Lower". 2. Create two rows with about 5 columns of data. 3. Create a conditional formatting rule of the range of cells containing values in the first row. 4. Select Cell value and for the comparison rule lower than and select the cell range for comparison in the range of cell in the second row containing values. 5. Add another subrule set and select as comparison rule higher than and select the same range in the second row. Actual Results: First cell in the first row will be formatted. Expected Results: All cells in the first row will formatted. Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: de Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) OS is 64bit: no
Created attachment 191913 [details] Conditional fornatting of cells based on rules for checking values greater than or less than or equal to
I guess your after: CF for A1:E1: 1. Cellvalue »lower|equal« A$2 ⇒ Lower 2. Cellvalue »greater« A$2 ⇒ Higher CF for A2:E2: 1. Cellvalue »lower|equal« A$1 ⇒ Lower 2. Cellvalue »greater« A$1 ⇒ Higher Solved?
More compact: CF for A1:E2: 1. Cellvalue »equal_to« MIN(A$1:A$2) ⇒ Lower 2. Cellvalue »equal_to« MAX(A$1:A$2) ⇒ Higher
Hi Werner as you can see in the attached document, this is what I've configured. Currently only for the range A1:E1 compared with A2:E2. And it is not working. Sorry. Best Alex
AFAIK it is not possible to define a conditional formatting rule where the condition is a range. It has to be either a single cell or the result of a formula. In Excel it is not possible as well. The difference is that Excel throws an error, making it clearer that this is not possible.
Actually Excel offers a way to achieve this: https://techcommunity.microsoft.com/t5/excel/conditional-formatting-row-by-row/m-p/3966290 In Calc the same can be achieved by setting up a Color Scale (Format - Conditional - Color Scale). Then in "Condition" you must choose "Formula is". Then you write "A1<=A2" in the formula and choose the style. Then do the same for the ">" rule. Let me know if this works for you.
Here are 2 good sources for my answer. I'll leave them here for future reference. https://superuser.com/questions/1562267/conditional-formatting-with-libreoffice-calc-activated-with-a-letter-and-the-con https://superuser.com/questions/900618/how-to-color-rows-based-on-two-cell-values-in-open-libre-office
Created attachment 191920 [details] Modified file If I have interpreted fine, what is required. Instead of the second row range in the condition, put $Tabelle1.A$2 Having a relative column, it is adapted by CFF as it evolves on the range with the condition.
(In reply to Alexander Nolting from comment #4) > Hi Werner > > as you can see in the attached document, this is what I've configured. > Currently only for the range A1:E1 compared with A2:E2. > And it is not working. Sorry. > > Best Alex No your CF is different: Cellvalue »lower|equal A2:E2 my suggestion: Cellvalue »lower|equal A2 (you need do declare the CF relative to upper|left Cell in the CellRange !! ))
Dear Alexander Nolting, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping
(In reply to QA Administrators from comment #10) > Dear Alexander Nolting, > > This bug has been in NEEDINFO status with no change for at least > 6 months. Please provide the requested information as soon as > possible and mark the bug as UNCONFIRMED. Due to regular bug > tracker maintenance, if the bug is still in NEEDINFO status with > no change in 30 days the QA team will close the bug as INSUFFICIENTDATA > due to lack of needed information. > > For more information about our NEEDINFO policy please read the > wiki located here: > https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO > > If you have already provided the requested information, please > mark the bug as UNCONFIRMED so that the QA team knows that the > bug is ready to be confirmed. > > Thank you for helping us make LibreOffice even better for everyone! > > Warm Regards, > QA Team > > MassPing-NeedInfo-Ping Dear QA Team Someone has set the NEED INFO flag without adding a comment what Info is missing Can you please add a comment with your question about what is missing. @Rafael: I think you are right. But the current design and overall behavior of LO allows selecting a range and add a rule for it but does not process this correctly. Devs and design board need then to decide if they want mimic exactly the behavior of Excel or have a more sophisticated functionality. Best Alex
Alexander: please review comment 6 through comment 9. Do these help you and can we close this report?
(In reply to Alexander Nolting from comment #11) > @Rafael: I think you are right. But the current design and overall behavior > of LO allows selecting a range and add a rule for it but does not process > this correctly. Devs and design board need then to decide if they want mimic > exactly the behavior of Excel or have a more sophisticated functionality. Ok, let's ask the design team.
(In reply to Alexander Nolting from comment #11) > But the current design and overall behavior of LO allows selecting a range > and add a rule for it but does not process this correctly. What exactly is wrong or difficult with the solution in comment 8?
(In reply to Heiko Tietze from comment #14) > (In reply to Alexander Nolting from comment #11) > > But the current design and overall behavior of LO allows selecting a range > > and add a rule for it but does not process this correctly. > What exactly is wrong or difficult with the solution in comment 8? Hello Heiko there is nothing wrong. But in my opinion not the same way as the function behaves in Excel, to which many of the commenters refering to - for any reason, so let us assume this is the goal of LO to mimic as exact as possible the example. Second, as this is working and this cell reference is also a range, this raises the question why is my example, for which I started this bug report, not working? As the documentation lacks detailed information what values are accepted - and with this knowledge one can see what kind of entry is allowed and what is not, its impossible for the reporter to decide what error pattern is present here? Is it a incomplete implementation of a function approved by design board or is it not implemented as designed or any other imaginable issue. As every one seem to push me to accept any of the commented workarounds (which is by the way an admission that there was wrongdoing here) it may helpful to explain the bug reporter and to close this report or accept that there is a bug and give it a priority, which will allow the organization to work on it in due course. Best Alex
(In reply to Rafael Lima from comment #5) > AFAIK it is not possible to define a conditional formatting rule where the > condition is a range. I wouldn't expect a range for simple comparisons like "is equal to" but when it comes to "is in top N elements" the situation is less clear. https://help.libreoffice.org/24.2/en-US/text/scalc/01/05120100.html > It has to be either a single cell or the result of a formula. Using MIN(A$1:A$2) as suggested in comment 3 works well. The issue was that "$Tabelle1.$A$2:$E$2" is not a formula; less than a range does not compute. Nevertheless I agree with room for improvement. If a function does not return a simple value like A1:A2 or MIN() without parameter, it should trigger a warning and not become accepted. I'll change the summary accordingly. If you disagree, Alexander, feel free to revert.
(In reply to Heiko Tietze from comment #16) > (In reply to Rafael Lima from comment #5) > > AFAIK it is not possible to define a conditional formatting rule where the > > condition is a range. > I wouldn't expect a range for simple comparisons like "is equal to" but when > it comes to "is in top N elements" the situation is less clear. > https://help.libreoffice.org/24.2/en-US/text/scalc/01/05120100.html see below > > > It has to be either a single cell or the result of a formula. > Using MIN(A$1:A$2) as suggested in comment 3 works well. The issue was that > "$Tabelle1.$A$2:$E$2" is not a formula; less than a range does not compute. > > Nevertheless I agree with room for improvement. If a function does not > return a simple value like A1:A2 or MIN() without parameter, it should > trigger a warning and not become accepted. I would like to see that this is possible by two reasons: you have found a other cases; here where a range has to be evaluated by a formula. An algorithm running over a range need to be programmed anyway. This would allow more flexibility for users than the example (Excel). It would also more intuitive as at other places "creating and copying" cell formular or selecting and dragging/filling a number of cells in a row or a column is normal for users. > > I'll change the summary accordingly. If you disagree, Alexander, feel free > to revert. I'm fine changing the summary. Getting a timely fix for this is not important. I would rather see more flexibility, because I perform such cell comparisons frequently and selecting several cells to compare and then inserting the formula for them would save me a lot of work.
(In reply to Heiko Tietze from comment #16) > (In reply to Rafael Lima from comment #5) > > It has to be either a single cell or the result of a formula. > Using MIN(A$1:A$2) as suggested in comment 3 works well. The issue was that > "$Tabelle1.$A$2:$E$2" is not a formula; less than a range does not compute. Nitpick: $Tabelle1.$A$2:$E$2 _is_ a formula consisting of just one range reference that if not used in array context produces the implicit intersection of the range and the current formula position, resulting in either one cell or its value, or an error value if a unique intersection is not possible. I.e. if entered on any other row than row 2 between columns A and E (even on a different sheet) results in a value, entered anywhere else (on row 2 or behind column E) it results in error. > Nevertheless I agree with room for improvement. If a function does not > return a simple value like A1:A2 or MIN() without parameter, it should > trigger a warning and not become accepted. This would boil down to not accepting an expression that yields an error value, except #N/A error maybe that is a valid result of lookup functions.