Has anyone seen this? I am trying to highlight cells with values greater than 125% of row average.
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.
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 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)
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 …
@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.
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.
