Absolute reference conditional formatting

[TengoKbailar]

Hi,
I have this in the conditional formatting formula is box

IF(ISNUMBER(L196);L196>=LARGE(L$169:L$220;L$167))

But when I insert rows unlike the spread sheet formatting does not move the reference so it loses the formatting.
ICan someone solve this for me please because I cannot.

Thanks

Michael

[acknak]

Put the calculations in a cell, then test the result from the CF

[Zizi64]

Michael
OpenOffice 3.1 on MacOS X

If your problem related to a bug then you need upgrade your very-very old version of the OpenOffice to the latest Still LibreOffice ot to the latest Apache OpenOffice version. Nobody will fix the bugs of the older versions.

[TengoKbailar]

Hi,
I am using Openoffice 4.1.3

The formula works in CF until I insert rows then it loses the correct range which moves upward.

The absolute formules I have in the cells change when rows are inserted but the formula in CF does not.


Thanks

Michael

[Zizi64]

I am using Openoffice 4.1.3

Please update your signature in this Forum.


And please upload an ODF type sample file here.

[TengoKbailar]

Hi Zizi64,
I updated my profile & attached is a small sample of the problem.
Thanks
Michael

[Villeroy]

Works well wth AOO 4.1.4 andLibreOffice 5.4.

Edit: My bad. I confused the conditionally formatted range with any reference used in the condition formula.

Notice the "expand references" option in the options dialog (options>calc>general
It makes a difference when you insert on top and directly below the referenced range.

With this option being turned off, if the reference is C11:C26 and you insert at row 11, you move down the referenced range to C12:C27.
With this option being turned off, if the reference is C11:C26 and you insert at row 27, the reference remains the same.

With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.
With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.

[acknak]

This is one (more) reason I try to avoid complex formulas in the conditional formatting.

But, if you still want to, and have to use older versions of Calc, here's one approach: use OFFSET to find the range of cells for LARGE. This is still subject to the option & behavior Villeroy has pointed out.

[TengoKbailar]

Thanks for your help
Maybe I asked the wrong question.

Should I have asked - How can I find and highlight the largest number in a column?

Also acknak are you saying that if I update from 4.1.3 to 4.1.4 I will not have the problem with CF ?

Thanks

Michael

[Villeroy]

The problem you describe did exist for many years. References in conditional formats did not expand like references in cell formulas. To my surprise, I can not reproduce this problem anymore.

Edit: My bad. I confused the conditionally formatted range with any reference used in the condition formula.

[TengoKbailar]

Hi Villeroy,

Which version of OO are you using?
If its 4.1.4 will it work with Mac OS X 10.9.5 Mavericks. I know I am going to have to upgrade my Mac but with Mavericks everything just works!! LoL

Thanks

Michael

[Villeroy]

STOP! Forget what I wrote yesterday. The validated range behaves normally. It expands according to the inserted rows, but not the reference within the condition formula. The bug is still in AOO, but it is not in LibreOffice.

[TengoKbailar]

HI,
I am still playing with this and have come to a way of achieving what I want
but now I have a different problem.
When there are duplicate numbers and I ask for the the top 3 it counts the duplicates individually so in the attached example it will not show the top two numbers until I enter 4 in C9. I need it to count the 3 x 13 as 1 top number and show 10 as the second largest number.
Can this be done?
Thanks for all the help
Michael

[Lupp]

Nothing here specific to CF!

When there are duplicate numbers and I ask for the the top 3 it counts the duplicates individually ...

That's the way any ranking I know does. The standard functions RANK(), LARGE() and SMALL() are made for cases of standard ranking. You cannot use LARGE() or SMALL() if you actually need to count an arbitrary number N of equal values for one "rank" without omitting (N-1) rank numbers.

There is a workaround, however, but there are limitations to it: You can disambiguate equal entries by adding little "sub-significant" values based on some unambiguous deterministic generator. The attached example is demonstrating it using the ROW() function for the purpose. This is simpler and more efficient than someting based on COUNTIF() e.g.

Out of curiosity: Can you concisely explain the aspects of your task needing to apply such an uncommon ranking?

[TengoKbailar]

Hi Lupp,
Thanks for your answer, I will play with the example you sent.
For your curiosity
I have a list of 50 different activities and I want to see clearly which one is giving the best result or second best. At the moment I have to watch the list as I change the top chosen number, say 2 to 4, to see when the highlights change. What I really need is to show just the chosen biggest number and duplicates of same.
Ummm While I am trying to explain this I am thinking that I am going about it in the wrong way maybe it is not as easy as I first thought. LoL

Thanks again
Michael