Ask Your Question
0

Conditional Formatting with Formula

asked 2019-10-14 17:02:39 +0100

tn1rpi3 gravatar image

updated 2019-10-14 18:37:28 +0100

Opaque gravatar image

Dear all

LibreOffice Calc - 5.1.6.2 (Ubuntu 16.04)

@conditional formatting:
If a value from a range1 in table1 is also found in range2 in table2,
the value from range1 in table 1 gets a special formatting.

In other words:
If a soccer team in (=table1.range1) ranks among the top three in (table2.range2),
I want that team to receive a special formatting.

I've tried MATCH("search criterion";lookup_array),
e.g.:
MATCH("TEXT";Table.A1:A3)
but I don't get the desired results.

Any ideas?

Many thanx

tn1rpi3

[Edit Opaque] Moved attachment from answer and deleted answer.

C:\fakepath\sample_file.ods

edit retag flag offensive close merge delete

Comments

If you are asking in the English speaking part of ask.libreoffice.org, please turn on option Tools -> Options -> LibreOffice Calc -> Formula -> Category: Formula Options -> Option: use English function names (The English equivalent is MATCH()).

Opaque gravatar imageOpaque ( 2019-10-14 17:15:18 +0100 )edit

will do..
So, MATCH("search criterion";lookup_array) doesn't do the trick.

Any other ideas?

Cheers tn1rpi3

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-14 17:25:22 +0100 )edit

see my answer - the comment is not meant as an answer.

Opaque gravatar imageOpaque ( 2019-10-14 17:29:26 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-10-14 18:54:50 +0100

@tn1rpi3,

image description

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

@Gilberto
Hi I've just verified that your approach is valid, too.
Thanks a lot for your input.

Imho it'd be helpful to have the formula assistant available
when doing conditional formatting. After all, I reckon most people don't learn formula structures by heart ;-)
What's your opinion on that?

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-15 12:26:48 +0100 )edit

@tn1rpi3, Certainly if the formula wizard for conditional formatting is triggered, it would be helpful, but nowadays the wizard is already having problems when working with different files.

Schiavinatto gravatar imageSchiavinatto ( 2019-10-15 13:41:26 +0100 )edit

@tn1rpi3

helpful to have the formula assistant available

This is not required, since you always can (and should) develop a complex conditional format formula within the sheet first, before you apply to a conditional format (and multiple ranges), since the condittiional formatting routines silently ignore almost all errors and just don't work.

Opaque gravatar imageOpaque ( 2019-10-16 00:27:56 +0100 )edit

@Opaque
I appreciate your viewpoint. -
With the conditional formatting routine ignoring errors in the output, there's obviously no other sensible way...

On the other hand, if I do the preflight check on the sheet as you suggest,
I can dig through a number of different formula options,
like setting the optional parameter (0,1,-1) or not, trying absolute vs. relative cell referencing, etc.),
all that without any hope of predicting the true outcome in the conditional formatting section.

So my line of thinking was like would it help speed up/ease the process of conditional formatting if we could use some kind of preview with it ..

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-16 14:09:25 +0100 )edit

I do not get the point of your comment ... using formulas in cells does not differ in any way (besides the = character), than using in conditional formats. But anyway this is not the place to discuss enhancement and I personally don't see no need for one. Feel free to file such request at bugzilla

Opaque gravatar imageOpaque ( 2019-10-16 14:21:17 +0100 )edit
0

answered 2019-10-14 17:28:17 +0100

Opaque gravatar image

updated 2019-10-14 20:05:48 +0100

Hello,

from my perspective you may fail due to leaving off the third parameter of function MATCH, which tells MATCH how the data on search matrix is ordered.

From Help:

Syntax
MATCH(SearchCriterion; LookupArray; Type)
......

Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.

This means, that your formula will only work, if the data in Table.A1:A3 are sorted.

Thus use (to be safe):
MATCH("TEXT";Table.$A$1:$A$3;0) (i.e. in German: VERGLEICH("TEXT";Table.$A$1:$A$3;0))

Notice: To match ordered data is faster than unordered data

Hope that helps.

Update 1 Fixed the search matrix (i.e. LookupArray), using $ to avoid shifting while applied to a range.

Update 2 Please see reworked file

-- deleted --

Update 3 Screenshot of final version (all in a single condition) image description

C:\fakepath\BedingteFormatierungTEAMs.ods

edit flag offensive delete link more

Comments

Thanks for the pointer.
Optional parameter
"0" will only format the first three entries found in table1.A1:A3,
instead of formatting all occurences within table1 that match the criteria from table2
"1" and "-1" will format 8 teams (seemingly random),
instead of (again) highlighting all occurrences of the top 3 ones.

I'm doubtful that MATCH is the right approach here..

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-14 17:53:06 +0100 )edit

Sorry - I don't understand that. Could you please provide an anonymized example file. From my pov MATCH is the thing to be used, if it is just about to answer does a certain value exist in another range. Your questions states

If a soccer team in (=table1.range1) ranks among the top three in (table2.range2), I want that team to receive a special formatting.

So, if everything works as per this condition, you should only find 3 teams in table1 am amongst the top three teams (as long as they are unique in table1). Ah - I see what's probably wrong. If you apply the MATCH condition to a range in table1 you need to avoid shifting the condtion as well: Thus fix the lookup array using $ is required:

MATCH("TEXT";Table.$A$1:$A$3;0)

Opaque gravatar imageOpaque ( 2019-10-14 17:58:12 +0100 )edit

I've uploaded a sample file. Hope that'll clarify my intentions.

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-14 18:39:32 +0100 )edit

Seems you did not fully understand how Conditional Formatting works with formula and ranges. Please see my reworked file. The thing is: The condition is applied to the first cell and the range specification at the bottom tells the formatting routine: Walk through all cells defined here and apply the rule as defined for the first cell but adapt row number in the condition, besides $ is used

Opaque gravatar imageOpaque ( 2019-10-14 18:42:57 +0100 )edit

Whoa, looking good!
Couldn't have worked that out by myself.
Needed the hint on how conditional formatting works behind the scenes! Thanks a lot!

tn1rpi3 gravatar imagetn1rpi3 ( 2019-10-14 18:51:15 +0100 )edit

Please consider to mark the question as answered by clicking the check mark (✔) next to the answer

Opaque gravatar imageOpaque ( 2019-10-14 19:00:11 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-14 17:02:39 +0100

Seen: 58 times

Last updated: Oct 14