Conditional Formatting with Formula

[tn1rpi3]

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.

sample_file.ods

[anon73440385]

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()).

[tn1rpi3]

will do…

So, MATCH(“search criterion”;lookup_array) doesn’t do the trick.

Any other ideas?

Cheers
tn1rpi3

[anon73440385]

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

[anon73440385]

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)

BedingteFormatierungTEAMs.ods

[tn1rpi3]

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…

[anon73440385]

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)

[tn1rpi3]

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

[anon73440385]

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

[tn1rpi3]

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!

[anon73440385]

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

[schiavinatto]

@tn1rpi3,

---

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 to the left of the answer, to finish the question.

[tn1rpi3]

@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?

[schiavinatto]

@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.

[anon73440385]

@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.

[tn1rpi3]

@anon73440385

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 …

[anon73440385]

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