Ask Your Question

Inconsistent conditional formatting using formula

asked 2020-09-15 05:56:20 +0200

424a57 gravatar image

Has anyone seen this? I am trying to highlight cells with values greater than 125% of row average.

  1. Create range of values with more than 10 rows and 30 columns
  2. Set conditional formatting for each row (Range A1:AF1) to "Greater than"; "1.25 * AVERAGE(A1:AF1)" (>125% of row average)
  3. Observe some cells are not formatted properly (formatted when they shouldn't be or no formatting when they should have it)

Cells incorrectly formatted highlighted in yellow

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-09-15 06:53:33 +0200

JohnSUN gravatar image

This is not Calc's fault, it is your carelessness. You did not take into account that when calculating the condition for the next cell, the range inside AVERAGE() will shift - for B1 it will become B1:AG1, for C1 it will become C1:AH1 (and capture the check number from AH1).

Just remember that links are relative and absolute and add dollar signs where appropriate. CorrectCF.png

edit flag offensive delete link more


Thank you for the slap in my face. As this was my first post on this forum, I will think long and hard before posting another question. Your manners leave something to be desired.

That said, adding the dollar signs in the condition allowed me to reduce the number of my conditions from 12 to 1. As I had each row defined as a range for the formatting and the condition formula matched the range, I don't see how or why the range should shift but the formatting certainly wasn't right before and it is now. In my real spreadsheet, I used the same formula in a cell to count the instances, and that behaved correctly. Apparently, only conditional formatting incorrectly calculates the average of a range without absolute references.

424a57 gravatar image424a57 ( 2020-09-15 07:56:06 +0200 )edit

@424a57 I didn't mean to offend you. Probably Google translator did it (I don't speak English well enough to double-check the result of automatic translation). Now I re-read my answer - yes, you are right, it looks rough (I meant exactly it, but I formulated it differently). Sorry again, this is just a misunderstanding.

About range offset. The formula specified in the condition applies to the first cell in the specified formatting range, A1. When calculating the condition for each next cell, the range will shift if it is not nailed down. (I hope that this time I did not write anything ambiguous)

JohnSUN gravatar imageJohnSUN ( 2020-09-15 08:49:39 +0200 )edit

let's try to be friendly, esp. to new users ... they are (sometimes) under stress reg. having a problem, are (sometimes) not used to LO, not used to the forum, and not used to the harsh tone which sometimes comes up here ...

newbie-02 gravatar imagenewbie-02 ( 2020-09-15 11:23:13 +0200 )edit

@JohnSUN Don't beat yourself up about your direct response nor the OPs sensitivity to being slapped over the wrists for a basic but understandable mistake. Sometimes you can't see the wood for the trees after staring at the screen for hours trying to resolve what seems a simple issue and a direct approach is sometimes all it takes. To the OP, don't be disheartened - yours was a good question well constructed +1 from me.

GrahamLees gravatar imageGrahamLees ( 2020-09-16 17:03:03 +0200 )edit

answered 2020-09-16 16:00:46 +0200

424a57 gravatar image

This looks like a long standing bug, and a well known work-around. Doesn't it make more sense that the condition should be evaluated for each cell in the range independently, rather than copying the condition to the cell before evaluating it.

  1. Create two rows, cells containing numbers 9 through 0
  2. Apply conditional formatting to the first range A1:J1, Accent if cell value is greater than AVERAGE(A1:J1)
  3. Observe cell values are accented as if the condition were copied to each cell in the range
  4. Apply conditional formatting individually to each cell in the second range A4:J4, Accent if cell value is greater than AVERAGE(A4:J4)
  5. Observe cell values are accented if the cell value meets the condition

Formatting range vs formatting individual cells

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-15 05:56:20 +0200

Seen: 98 times

Last updated: Sep 16 '20