Hello,
i have some issue with conditional formating (databar) used with formula.
I have
. A | B
20 | 10
08 | 04
If i select B1:B2 and want to apply a conditional formating (databar) refering to formula = A1, the B2 cell, 4, will not be compared to 8, but still to 20, not incremented with row, even if i choose A1 and not A$1.
Thanks for your help
Using LO
Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 8; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-GB (fr_FR.UTF-8); UI: en-US
Calc: threaded
You might need to post an example spreadsheet showing the error. At the moment A2 and B2 look like text
BTW you can format a table in this site, see the user guide, This is the guide - How to use the Ask site? - #6 by erAck. It says you can just copy from a spreadsheet or format it manually.
Thank you LeroyG. You can see in the CF data bar.ods that the conditional formating is not working well.
Indeed, the number 4 is databar colored vs 20, and not vs 8, which is normally expected.
Anyone with a solution, or is it a pure bug? (for me it is a bug). Thanks
I don’t think it is a bug, they are individual comparisons so each row needs a separate conditional format to exclude other data. Excel 2010 acts the same way.
I think the best way to do the comparison is to use a third column containing =B1/A1 and set minimum value as 0 and maximum as 1 (>1 if A can be smaller) depending on the your data and apply the conditional formatting to that. You could set font colour to white in column C.
1 Like
1 Like
Yes, thanks EarnestAI. It a way to do it, indeed, but not very convenient since adding column % (i need to use conditional formatting on several column, which means adding several ones).
I think the issue is a real bug, isn’t it?
Cheers
T
-
This is not a bug. A “Data Bar” type applies a single minimum and maximum to its whole range, to allow all bars in the range to show consistent data (it is designed to create comparable bars, so only the actual value is expected to change from cell to cell inside the range, not the min/max values). It is trivial to create data in such a way, that the bars would need not different min/max, e.g. operating on ratios like
=B1/A1, as @EarnestAl suggested. -
You have already posted two solutions. Please explain us, what is missing on this site, to persuade people to not press Suggest a solution, when they do not provide a solution to the question?
However, it is unexpected behaviour. Conditional format formulas expressions follow absolute, relative and mixed references. Why not this formula? $A$1 and A1 mean the same in this context.
Exactly because this would violate the design principle I explained. The “All Cells” conditional formatting rule must format all the cells in the range using the same min-max range. This rule allows to provide an alternative way to represent the value in cell, not “value created by a multiplication of several cells” (min/max are not “values in other cells”, but some constants in the scope of this feature).
Possibly some hint in the dialog could be useful, for people who might think that the formula would be “relative”.
Excel does display a warning dialogue to say it can only be a single cell if a range is entered in the relevant field of the conditional formatting rule and OK is clicked
This is different. The formula that confused OP is not a range, and having a “single value” check would not affect this confusion.
I’d guess that in other UI languages than English it’s still named Answer instead.
1 Like
Fully agree with this: Having $A$1 giving the same as A1 indicates, in my mind, losing an opportunity to give extra feature. In my case, it would have helped me a lot, and would have reduced the number of additional column of % (value / value) to be able to apply conditional formatting.
Thanks for all your reactions, still. I understand there is no hope so i will not look for update on this feature. Not dramatic. cheers T.