[conditional formatting] multiple range

English
,
#1

Hi fellows,
I am working on a simple salaries spreadsheet: first column contain year, second one contains month and third the numeric currency value, of salary;
e.g.
2023 January 1.333,33€
2023 February 1.444,44€
2023 March 1.555,55€

What I want? I’d like to highlight the highest and lowest payment OF THE YEAR
(spreadsheet contains salaries from 2019)
so, I used Conditional Formatting > Cell Value > is in top/bottom N elements, applying a different style;
here the issue comes: if I enter cells range of every single year, separated by semi-colon, it does not work
because LO intends the highest/lowest value of the ENTIRE range
NOT the highest/lowest value of SINGLES ranges (by year in a word)

e.g. with cell range: C1:C12;C13:C24
LO highlights the highest/lowest values of two years!

How can I fix it?

example spreadsheet:
test.ods (15.5 KB)

#2

Hallo
for example your Data starts in A2 and the salary in C2:
Select C2:C61
→Format→ConditionalFormatting →Condition→

  1. Cellvalue is equal MINIFS(C$2:C$61;A$2:A$61;A2)
    your Style for year_min

  2. Cellvalue is equal MAXIFS(C$2:C$61;A$2:A$61;A2)
    your Style for year_MAX

If (by random chance) your Locale is German? the Functions are named MINWENNS and MAXWENNS

#3

Quest: I bet on Italy. :slightly_smiling_face:

#4

@sokol92 Yeah, ITA

Anyhow, your solution does not work;
I attached a spreadsheet where I show exactly what I want (by colouring cells manually…)
briefly I want a MIN/MAX for each year

#5

In order to be able to copy conditional formats, the conditional formatting formula for the maximum in the range F5:F12 can be written as follows:

MAX(OFFSET(F5;IF(ISNUMBER(C5);-C5+1;-12);0;13;1))

Test.ods (16.7 KB)

1 Like
#6

It is working, thanks!
but I am not satisfied, I will continue to look for other solutions…

#7

:astonished:
What is the reason?

#8

not elegant

#9

Yes, I can’t do it that beautifully. :slightly_smiling_face:

1 Like
#11

Im also not satisfied… because the discrepancy between your description in your first post and the real content of your …ods!

bf_min_max_per_year.ods (17.4 KB)

#10

2 solutions:
test_resultsolution-v0001.ods (17.0 KB)
with problem in couple formula with conditional formatings

#12

a nearly perfect solution with each year in one tab you can easely add a next year with the new tab without copying all contents of cell. Have a look to this expanded table:
test_resultsolution-v0002.ods (22.5 KB)

#13

@sokol92 don’t be upset we are here to learn
@koyotak Thank you for your contribution, too;
I found solution-2 the best fitting for my needs.

I want to add a few words:

  1. at the beginning, I hoped there was a way to handle with cell ranges; but no one point out… so I thing it do not exist;
  2. anyhow, I think best solution (for my case) is to create a new rule for each year; unfortunately I have to do it manually since LO “merges” copied cells; for this reason I opened an issue in the official LO bugtracker [158187 – [feature request] copy rules from Manage < Conditional Formatting] proposing a “copy” button in the Conditional window that would ease the task;

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