Error in answer that has been closed for comment

English
#1

In the question “Calc howto: conditional format a row based upon a column” an answer has been accepted that does not work quite the way it was answered. The formula option for conditional formatting does not apply correctly if the formula is non-absolute. So if for example the formula is “G43=0” to change the colour of row G, the formatting is not applied. If however the the formula is “$G43=0” it is applied correctly.

I’m using LO Version: 7.6.3.2 (X86_64) / LibreOffice Community
Build ID: 29d686fea9f6705b262d369fede658f824154cc0
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-ZA (en_ZA); UI: en-ZA
Calc: CL threaded

I also tested this on Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 12; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-ZA (en_ZA.UTF-8); UI: en-US
Calc: threaded

Both have the same problem, so this is either a bug, or if the fomula must be absolute, then it should be added to the answer and/or added to the documentation.

#2

The accepted answer quotes only absolute:

For example: $A1=6008

1 Like
#3

There is no indication that an absolute reference is required. The Libreoffce documentation simply refers to “a formula that evaluates to true” and this answer says: “For example”. Everybody knows that formulas can be relative or absolute, so let’s at least have a note somewhere saying “the reference to the formula column or row must be absolute.”

Why let users waste a lot of time having to figure this out, when a simple comment in that answer and in the documentation would immediately address the issue?

#4

No, the references in the formulas in the conditional format aren’t limited to absolute. It’s the specific task defines, if a formula has to use (partially) absolute references or not. And this indeed requires the user to understand what they are doing.

E.g. here: your intention is to color a row, i.e., many cells in it, based on a value in one specific column of it. Every cell in that row can only refer to that column using absolute column reference. The accepted answer was universal, using the specific question task as an example. Its wording is perfect.

And by the way, nothing prevents you from commenting there. Commenting is allowed even in closed topics, you just didn’t try.

Note that there’s no row G.

#5

Yes, of course, I meant column G.

As to your cheeky comment that I didn’t even try to comment: I’m logged in and this is what the screen looks like:

image
image1164×684 76.6 KB

How do you suggest I comment on that answer?

#6

Maybe there is a user level limitation. I (as leader) can see the comment icons.

1 Like
#7

Then you either made another mistake (and actually want to change the color of row 43), or you are trying to solve a different task compared to the Q&A that you referred to. I really can’t see how to change column color using the $G43=0 formula, but maybe you can explain the task better.

I apologize for an incorrect assumption, that if I see the “comment” option, then it is also available to you.

#8

I was simply referring to random cell… but I can see that I need to be much more explicit.

Here is a sheet with both rows and columns conditionally formatted.

image
image757×250 19.3 KB

In the sheet I have conditionally formatted regions: A3:D3, A4:D4, F3:F10 and G3:G10 each with their own condition.

So, I now understand how it works, as do those of you who responded. However, if the documentation (and the question’s answer were a little more expressive, it would be really helpful to many users out there that may not have the experience and insight that seasoned users have. After all, surely it’s a good thing to help users along a clearly and as simply as possible, not?

conditional formatting an area.ods (11.8 KB)

#9

IMO, the best is when users understand what they do. If they need that answer expanded, it’s not the answer’s fault, it’s they don’t understand either relative/absolute addressing, or conditional formatting’s relativity (i.e., the fact that the formula in the conditional formatting is relative to the first cell of the CF range).

And everybody can find somethin that they personally don’t know; trying to expand each and every piece of advice with infinite amount of details that happened to be important to some specific users will result in every small answer turning into the Calc Guide. I didn’t - and don’t - see a point of your request. Each answer is best, when it is not overwhelming - and your proposal is exactly toward making in overwhelming, because why stop here?

#10

I disagree with your assessment. It is not logical that the reference to the column or row, depending on the area that is being conditionally formatted, must be absolute. A specified area is formatted based on the result of a formula and that again is specified.

I don’t understand the general resistance here to improve things that are not clear?? As it is, the documentation is spartan to put it mildly and there are very few examples in it, so mostly it’s not worth the trouble to do there in the first place. I’m not going to waste more time advocating for a better user experience, since clearly just because you know how, everybody should struggle to get to that knowledge too. It just baffles me.

#11

Exactly because of this phrase. It shows that you do not understand the logic - because it is logical.

In your example, the conditional formatting in A3:D3 uses formula A3="black". You somehow expect your formula to know that it needs to only refer to column A … why? And if you used the range A3:D6, then would you also expect the rows 4, 5, 6 also get color based on the fixed cell A3? Or would you want row 4 depend on A4, row 5 depend A5, and row 6 depend on A6?

Just as with normal cells, you create a formula in one cell, then copy to other cells by expanding the range. Your formula here should look like: “if cell in the same row, in fixed column A is “black”, then apply this format”. It is applied per cell, so - for any given cell, you want unfixed, relative row, but fixed column.

In F3:F10, you show exactly why it is logical. So - this is just “I don’t understand the relativity of conditional format’s formula” case.

And thus, if you created this question asking what is the logic behind this conditional formatting behavior, it would be a good question.
But when you tell that you wanted to “improve” the answer, without really understanding it, you tell clearly, that your “improvement” would most definitely be confused and confusing itself.

1 Like
#12

There mere fact that we’re having this discussion shows why it should be documented.

I’m not expanding the range as you suggest. I set a fixed area and set a formula that should be evaluated to determine if that formatted conditionally. I can prove it to you.

Select any range (eg A2:A4) and set a formula as a condition (eg B2=1). Don’t use absolute addressing. Now right-click and cell in that range and inspect the formula. In all cells the formula is still B2=1, even in D2. So your argument that is “copies” die formula to the expanded range may be so internally, but there is nothing that indicates this to an ordinary, even somewhat savvy user.

Since it’s not intuitive and only logical to those that know the internal machinations, I want other people to know this.

This project doesn’t seem keen to help people understand the product better, which is a shame. I’ll create a blog post and hopefully google and other will help people find the info.

#13

Do what you want. You repeatedly show that you don’t understand what you intend to explain (not understanding is OK, people ask questions just to learn what they don’t yet know / understand); but your attitude is not “I want to learn, and then maybe improve the documentation”, but “I want to explain what I don’t understand, and I don’t want to learn”. That baffles me.

1 Like
#14

Please refer to the Calc Guide for more documentation.

Note that the same principles apply to Excel.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.