Ask Your Question

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

asked 2019-11-15 22:52:51 +0200

quaytsar gravatar image

updated 2020-08-07 19:53:46 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete


Please attach your sample file, editing your questionl

m.a.riosv gravatar imagem.a.riosv ( 2019-11-16 00:01:26 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2019-11-16 00:28:11 +0200

Opaque gravatar image

updated 2019-11-16 00:42:55 +0200


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

C:\fakepath\MinimumByRow-Conditional.ods and adapt ranges to fit your special needs.

Hope that helps.

edit flag offensive delete link more


I ended up using
Condition:formula isa2=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.

quaytsar gravatar imagequaytsar ( 2019-11-16 01:10:00 +0200 )edit

That's a simple addition.

Opaque gravatar imageOpaque ( 2019-11-16 12:23:17 +0200 )edit

answered 2019-11-16 00:03:03 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more

answered 2019-11-16 01:00:31 +0200

quaytsar gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-15 22:52:51 +0200

Seen: 550 times

Last updated: Nov 16 '19