The new Icon Set should have a switch for color reversal (like Excel). Small values are not always bad (red) and big values are not always good (green). An alternative would be also an "Icon Set 2 invers colors".
Additionally there should be a way to change each icon but I have no plans to implement that in the near future.
I second this enhancement request. However, maybe it can be solved easier: Instead of switching the order of icon sets, why not just switching the signs. It should be possible to select: "<=" instead of ">=" Then, the problem is solved. I assume this is easier to implement?!?
This has been implemented for a long time. The options are just not exposed in the UI.
The remaining part that needs implementation for this feature is to introduce advanced configuration solutions for the icon set entry in the conditional format dialog. The code for the icon set entry in the dialog is in sc/source/ui/condformatdlg The code for the icon set model part, including the reverse flag, the configuration that allows any image for an icon can be found in sc/inc/colorscale.hxx and sc/source/core/data/colorscale.cxx
Hello, I'd like to work on this, however I don't completely understand this. Can anyone please provide code pointers, and a more clear description of the problem ?
@abstatic: That's great to hear that you want to work on this bug. When I use icon sets in conditional formatting, I frequently need to use workarounds, because Calc is lacking the feature to reverse the color or icons. I illustrate the issue with an example using the icon set "3 smileys": The issue is that both, the order of the smileys :-) :-| :-( AND the order of the sign >= are hard coded. In other words, at the moment, Calc always assigns a :-( to high values. This does not always make sense. For example, if you imagine an exam with 100 points. You fail if you have < 50 points, you pass if you have >= 50 points and you get an excellent grade if you have => 75 points. What Calc can do (this is wrong!): Grades (100 points in exam is very good, 0 points is very bad) 100 :-( 95 :-( 30 :-) 22 :-) 55 :-| What Calc should be able to do (this is correct!): Grades (100 points in exam is very good, 0 points is very bad) 100 :-) 95 :-) 30 :-( 22 :-( 55 :-| What we need is that the order can be inversed. IMHO, it should be possible for the user to switch the sign >= to <= if the scale is inverse. Currently, in the dialog, the sign >= is hard coded and cannot be changed by the user. Please see the attached screnshot "Smiley dialog example" to illustrate that I cannot change the order. Instead of an >= I need an <= for my scale of exam points.
Created attachment 140125 [details] Smiley dialog example.png For some scales, it is important that the user can inverse the order of the smileys/icons/colours, because high values are not always bad or good. In this example with points in an exam, I cannot correctly assign smileys, because higher values mean a better grade. What we need is an inverse order switch.
Additional info: Markus Mohrhard had implemented the conditional formatting feature, so he probably is the person who can help you with the code pointers. He already gave a few hints in comment 4
Hi @Markus Could you please elaborate what do you mean by "introduce advanced configuration solutions".
Hi @Gerry! I am trying to work on this, and I have took some inspiration from Latest MSexcel. In that conditionalformat dialog one can select from "<","<="etc. I have made a list and removing that hard coded label. I would like to ask whether this is accepted or not. About the title of this bug:Iconset should have a color reverse switch, In MS Excel I see the above feature but I can't see the color reversal switch instead of it one can find dropdown in which he can select different types of ICon. Should this be implemented or color reverse switch should be ok? Please let me know if I am not clear.
Hi @Ekansh Jha : Thank you very much for working on this. I think the original bug description was not very precise. I think @Horst actually meant "Icon set should have a reverse order switch". I think Horst had these icon sets in mind, where the icons only differ by color (e.g. the icon set that looks like a traffic light). In other icon sets (e.g. the yellow smilies), it is actually a reverse order switch, which is needed. On your question how to set the UI, I can give you my opinion and ideas, but I guess that @Markus Mohrhard has a clearer idea how the solution could best look like. So, please ask him, too. I am just a user :-) I also added @Heiko Tietze to this bug, because he deals a lot with the user experience and design of LibreOffice. I could imagine following solution: For ITEM SETS: I could imagine to have a selection field where currently ">=" is. The selection field could have the two options ">=" and "<". This in fact reverses the order. The selection field could be there for the first icon and then automatically applies for the other icons (to not make the order inconsistent). [For comparison: Microsoft Excel has a switch called "Reverse Icon Order". Hence, it does not reverse the signs, but just the order of icons. This would also work. Please see attached screenshot]
Created attachment 140316 [details] Excel 2010 Screenshot: Reverse Icon Order button
(In reply to Gerry from comment #11) > Hi @Ekansh Jha : Thank you very much for working on this. I think the > original bug description was not very precise. I think @Horst actually meant > "Icon set should have a reverse order switch". I think Horst had these icon > sets in mind, where the icons only differ by color (e.g. the icon set that > looks like a traffic light). In other icon sets (e.g. the yellow smilies), > it is actually a reverse order switch, which is needed. > > On your question how to set the UI, I can give you my opinion and ideas, but > I guess that @Markus Mohrhard has a clearer idea how the solution could best > look like. So, please ask him, too. I am just a user :-) > > I also added @Heiko Tietze to this bug, because he deals a lot with the user > experience and design of LibreOffice. > > I could imagine following solution: > > For ITEM SETS: > I could imagine to have a selection field where currently ">=" is. The > selection field could have the two options ">=" and "<". This in fact > reverses the order. The selection field could be there for the first icon > and then automatically applies for the other icons (to not make the order > inconsistent). > > [For comparison: Microsoft Excel has a switch called "Reverse Icon Order". > Hence, it does not reverse the signs, but just the order of icons. This > would also work. Please see attached screenshot] Hi @Gerry! actually I have MS16 I think they have removed that color switch icon (afaik). Looking forward to hear suggestions from @heiko and @markus.
Created attachment 140670 [details] Excel 2016 Screenshot: Reverse Icon Order button (.png file) > Hi @Gerry! actually I have MS16 I think they have removed that color switch > icon > (afaik). Looking forward to hear suggestions from @heiko and @markus. Finally, I got hand at MS Excel 2016. They have not removed the reverse switch. Please see the attachment. BTW, you see this dialog after (1) having applied a conditional formatting in Excel, then afterwards (2) select edit conditions. Then you see this dialog.
(In reply to Ekansh Jha from comment #13) > Looking forward to hear suggestions from @heiko and @markus. I have seen that Markus was not copied to this bug to answer the open questions how the UI of the fix should look like. So, I felt free to add @Markus Mohrhard to this bug. Maybe, also @Heiko has not seen your question or was busy. @Markus and @Heiko: The main question here is how the reverse switch for icon sets / color scales in conditional formatting should look like. There is the solution by MS Excel (see the two screenshots) or a solution like mentioned in comment 2 and comment 11.
I'm always in favor of simplicity and against awkward controls to manipulate the presented options. So the easiest solution is to add "3 inverse colored smileys" to the list. Should be an easyhack. But the actual problem here are the hard-coded symbols, as Markus pointed out in comment 1. So what we need is a dropdown with a set of symbols including the smileys, and you select whatever sequence you want. In other words, [Cell value is] [greater than] 10 [Insert] [:-)] (with Insert additional to "Apply Style". Even better the user can add own symbols from the emojis (ideally colored) or unicode dialog.
Thanks Heiko for commenting on this bug. (In reply to Heiko Tietze from comment #16) > I'm always in favor of simplicity and against awkward controls to manipulate > the presented options. So the easiest solution is to add "3 inverse colored > smileys" to the list. Should be an easyhack. Well, we have 22 icon sets, which means that we would duplicate the icon sets to 44. Does this really make sense? I also wouldn't say that the idea in comment 11 is an "awkward control". I suggest to have the >= as a button. If you click on it, it changes to < (and automatically changes the signs below to make it consistent): _____ _____ | | | | | >= | --> | < | |_____| |_____| That's straight forward, isn't it?
Re-evaluating the EasyHack in 2023 This enhancement is still relevant, as the issue is still present in the UI. One should go to "Format > Conditional > Icon Set..." to see the icon set used for conditional formatting.
I am working on this hack, but I am not sure what is the desired solution. Should I make a switch button to reverse order of icons, or just add a "3 inverse color smilies" to the list ? Or is it something else ?
(In reply to Ahmed Hamed from comment #19) > I am not sure what is the desired solution. Comment 2 and comment 4 should make it clear: replace the static ">=" label with as dropdown that allows to pick >,>=,<,<= and perhaps <>. I think it's not convenient anyway, but no idea what control we have over this (eg. change the icons, the number of items, the order...).
Created attachment 193728 [details] Patch with 3 colored icons Tried to test the patch and it works. I attached a test case where I beliebe there is an issue. Can anyone check if the cell with the value 34 should contain a yellow icon?
Created attachment 193731 [details] Re-testing Patch with 3 colored icons (In reply to Andreas Heinisch from comment #21) > Created attachment 193728 [details] > Patch with 3 colored icons > > Tried to test the patch and it works. I attached a test case where I beliebe > there is an issue. Can anyone check if the cell with the value 34 should > contain a yellow icon? I tried to test this case, and I get the right output as expected. I wonder what can be the reason for this difference.
AhmedHamed committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/fcc5770dd1e33dcb4f4f0c28683569af5d1d8368 tdf#61313 Replace the static ">=" with a dropdown of different operators It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified in: Version: 24.8.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: 1f15d097cace14ca6e44e7652f460aa3fa7bd150 CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: CL threaded In release notes: https://wiki.documentfoundation.org/index.php?title=ReleaseNotes%2F24.8&type=revision&diff=752780&oldid=752779
Created attachment 196414 [details] Excel document revealing import issue I think this patch may have broken the import of conditional formatting rules from Excel. Attached is an Excel document that shows the 3 types of arrows when loaded in Excel, but only a couple of arrows when loaded in LibreOffice 24.8.0.3.
(In reply to Michael from comment #25) > Created attachment 196414 [details] > Excel document revealing import issue > > I think this patch may have broken the import of conditional formatting > rules from Excel. > > Attached is an Excel document that shows the 3 types of arrows when loaded > in Excel, but only a couple of arrows when loaded in LibreOffice 24.8.0.3. Why do you think this specific patch broke it? Are you saying that some older version of LO used to be able to show the 3 types of arrows? Any clue about which version of LO would that be? In any case, I would suggest opening a new ticket (and adding a screenshot of what it looks like in Excel, so users have a baseline to compare against).
Shall do, thanks. It was working in previous versions of LibreOffice, which is what led me to track down the likely cause.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/27be65a7d5326608f75839e3964ac322e88224a1 tdf#162938: Revert "tdf#61313 Replace the static ">="... It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Reopening since the patch was reverted. It broke tdf#162938
Xisco Fauli committed a patch related to this issue. It has been pushed to "libreoffice-24-8": https://git.libreoffice.org/core/commit/2e6d189821f5def411647b6f3e21d0617ab299b6 tdf#162938: Revert "tdf#61313 Replace the static ">="... It will be available in 24.8.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Sorry for the patch that caused the problem. I will open a new one that will hopefully solve that :)
AhmedHamed3699 committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/08c170b2fa0ceaae388da4ddbed0330bd57a663a tdf#61313: Allow customization of conditional formatting operators in icon sets It will be available in 25.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Can this be closed as fixed now?