[Calc] How to highlight the minimum non-zero value in a row?

[quaytsar]

I’m trying to highlight (as in change the background colour of the cell) the minimum non-zero value in a row of numbers. I can set up conditional formatting to do this, but whenever I add a new row of numbers, the formatting extends to include the new row (e.g. highlight minimum of range B2:B6, insert a new row below, range is now B2:C6). But the new row needs to be compared against itself and not any other rows (row B compared only to row B, row C compared only to row C, etc.). How can I do this without having to manually adjust the formatting for every single row of the worksheet whenever I add new rows?

And clone-formatting doesn’t work for this either. It, also, changes the range of the formatting instead of applying the formatting to the new range (e.g. range B2:B6 gets cloned onto D2:D6, range is now B2:B6;D2:D6; it should be range B2:B6 and a separate rule for range D2:D6).

This is especially aggravating because I never had these problems in Office (it applied formatting exactly the way I expected it to) and I really don’t want to shell out $100+ for an Office licence to fix this one issue.

[mariosv]

Please attach your sample file, editing your questionl

[anon73440385]

Hello,

this answer assumes (that’s how I understood your question):

1. You want to highlight the Minimum in a row >0.
2. A row spans from column A to column F
3. Data begin in row 2 (Row 1 contains header;top left cell is A2)
4. There are 19 rows (bottom right cell is F20)

Condition: Cell value is equal to SUMPRODUCT(MIN(IF($A2:$F2>0,$A2:$F2,"")))
Cell Range: A2:F20

please see the following sample file

MinimumByRow-Conditional.ods and adapt ranges to fit your special needs.

Hope that helps.

[quaytsar]

I ended up using
Condition: formula is a2=minifs($a2:$g2,$a2:$g2,">0")
Cell Range: a2:g20
But your method works, too.

Edit: Your method fails when I add new rows and start inputting numbers into those new rows. The minifs method I described still works.

[anon73440385]

That’s a simple addition.

[mariosv]

With B2:B6;D2:D6 should work fine if the formula in the condition it’s done with relative addresses for B row.

[quaytsar]

I got it working by changing the formula from bottom N element 1 to formula is b2=minifs($b2:$g2,$b2:$g2,">0") over range b2:g6. Adding new rows within that range highlights cells as appropriate.