Ask Your Question
0

color scale maximum and minimum formula for each row

asked 2017-09-20 11:47:54 +0200

ppetak gravatar image

So, this is the same as here: https://ask.libreoffice.org/en/questi... which is closed with answer, but without solution.

What I want to achieve is color-scale format on rows, where red=min(row), yellow=mode(row), green=max(row)

If I try to repeat the steps you can use in 'cell value is; formatting option, I get different results:

  • set up condition with 'all cells' option, green color to formula, =MAX($A2,$U2)
  • set up yellow, red colors to their values
  • save all settings, see that the 1st line is formatted correctly
  • now open conditional formatting again, and for range select whole table

after these steps, values for 'cell value is' option will reference new row, and get new MIN, MAX values for each row. But 'color scale' does NOT reference new row, and only first row is referenced every time for calculations.

Moreover, also tried trick mentioned in referenced question, but without success. I put =mode(row) into column Z for each row, and used:

=INDIRECT("Z"&ROW())

also points all the time to first row. which means that row() function evaluates every time to first row of a set

So, this is dead end for me, and I think this is a bug in color scale. I welcome all advices, because the table is really long, and only workaround seems to be adding formatting for each line manually.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-09-20 14:00:13 +0200

librebel gravatar image

Hello @ppetak,

You do not have to format each row manually, but you can set the conditional format for 1 row, then at once copy it for all other rows.

  1. Select all the appropriate cells of your first row, e.g. cells $A2:$U2;
  2. Choose the menu "Format : Conditional Formatting : Color Scale...";
  3. Set the first two listboxes to "All Cells" and "Color Scale (3 entries)";
  4. Set the second row of listboxes to "Min", "Percentile", and "Max";
  5. Below the listbox "Percentile", enter the value 50;
  6. Click OK.

If all went well, the conditional format should now be set for the first row. Now to set this format for the rest of your rows:

  1. While the same cells $A2:$U2 are still selected, press CTRL+C (Copy);
  2. Select all the appropriate cells of all other rows that should be formatted;
  3. Choose the menu "Edit : Paste Special...";
  4. In the dialog that pops up, DEselect all checkboxes except "Formats";
  5. click OK.

Hope it helps, lib

edit flag offensive delete link more

Comments

Hi @librebel - unfortunately this does not work in my environment (win. 5.3). After special pasting, a format is created per cell.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-20 15:39:17 +0200 )edit

@librebel - I have tested with a version 5.4.1.2. Your procedure works but, in my environment, it is necessary to save and reopen the spreadsheet for formatting to apply. In this version, my workaround continues to work.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-21 09:03:55 +0200 )edit

I tried also copy-paste method, ending up with result as @pierre-yves-samyn in his comment above. Seems that i have 5.3 then :) I'm on linux, but i don't know office version, as I'm at work now. I will test it on the weekend more thoroughly.

thanks for advices so far.

ppetak gravatar imageppetak ( 2017-09-22 14:25:17 +0200 )edit
0

answered 2017-09-20 15:35:49 +0200

pierre-yves samyn gravatar image

Hi

I reproduce the problem. In my environment the following workaround works:

  • create the conditional format for the 1st row
  • create the conditional format for the 2nd row
  • select the first two lines, copy
  • select the other rows (range where the format should apply)
  • EditPaste special▸only Formats

Regards

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-20 11:47:54 +0200

Seen: 391 times

Last updated: Sep 20 '17