absolute reference conditional formatting

Discuss the spreadsheet application

absolute reference conditional formatting

Postby TengoKbailar » Mon Nov 13, 2017 4:07 am

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
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby acknak » Mon Nov 13, 2017 5:56 am

Put the calculations in a cell, then test the result from the CF
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22659
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: absolute reference conditional formatting

Postby Zizi64 » Mon Nov 13, 2017 9:43 am

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.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6003
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: absolute reference conditional formatting

Postby TengoKbailar » Mon Nov 13, 2017 7:23 pm

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
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby Zizi64 » Mon Nov 13, 2017 7:43 pm

I am using Openoffice 4.1.3

Please update your signature in this Forum.


And please upload an ODF type sample file here.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6003
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: absolute reference conditional formatting

Postby TengoKbailar » Mon Nov 13, 2017 10:12 pm

Hi Zizi64,
I updated my profile & attached is a small sample of the problem.
Thanks
Michael
Attachments
Conditional Format.ods
(9.91 KiB) Downloaded 8 times
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby Villeroy » Mon Nov 13, 2017 10:21 pm

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.
Last edited by Villeroy on Wed Nov 15, 2017 1:17 am, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24590
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: absolute reference conditional formatting

Postby acknak » Mon Nov 13, 2017 10:43 pm

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.
Attachments
Conditional Format_offset.ods
(8.68 KiB) Downloaded 7 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22659
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: absolute reference conditional formatting

Postby TengoKbailar » Tue Nov 14, 2017 2:22 am

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
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby Villeroy » Tue Nov 14, 2017 12:24 pm

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. 
Last edited by Villeroy on Wed Nov 15, 2017 1:16 am, edited 4 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24590
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: absolute reference conditional formatting

Postby TengoKbailar » Tue Nov 14, 2017 5:26 pm

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
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby Villeroy » Tue Nov 14, 2017 7:54 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24590
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: absolute reference conditional formatting

Postby TengoKbailar » Wed Nov 15, 2017 5:26 am

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
Attachments
Conditional Format with H.ods
(9.66 KiB) Downloaded 10 times
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm

Re: absolute reference conditional formatting

Postby Lupp » Wed Nov 15, 2017 2:42 pm

Nothing here specific to CF!

TengoKbailar wrote: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?
Attachments
aoo91188SkewRanking_1.ods
(10.87 KiB) Downloaded 3 times
On Windows 10: LibreOffice 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1479
Joined: Sat May 31, 2014 7:05 pm

Re: absolute reference conditional formatting

Postby TengoKbailar » Wed Nov 15, 2017 3:58 pm

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
OpenOffice 4.1.3 on MacOS X
TengoKbailar
 
Posts: 80
Joined: Sun Jan 17, 2010 10:00 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 33 guests