color scale maximum and minimum formula for each row

[ppetak]

So, this is the same as here:

Copyable conditional color scale

I have a (very large) confusion matrix which I would like to colorize with the ‘Color Scale’ conditional formatting. Each row has a different number of inputs (e.g. the sum of each row is different). The row sum is stored in a cell to the right of the last row value. I want to color the cells from white to black, complete black only being used if the cell value is equal to the row sum. I achieved this for the first row by applying a color range formatting with 2 entries. For the ‘left’ one …

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.

[librebel]

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

[PYS]

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

Regards

[PYS]

@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

[ppetak]

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.

[PYS]

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)
• Edit▸Paste special▸only Formats

Regards