Conditional Formatting - Range error

Question

I am using the new Google Spreadsheet application. Here is my data...

• Cell B3 = 60
• Cell B5 = 47
• Cell B6 = 52
• Cell B7 = 75.1
• Cell B8 = 68.4
• Cell B9 = 56

I setup this conditional formatting rule...

For some reason that I do not understand, cell B6 turns red. All the other cells are formatting the way I expect them to be. When I change the "Range" in the conditional formatting rule to only "B6", it does not turn the cell red. Am I misunderstanding the "Range" field? Is this a defect or by design?

Can anyone explain this?

Answer 1

Something seems to be off with the conditional formatting. One solution, anyway, as Vidar suggested, is to use Custom formula is instead of Greater than in the dropdown. Use the formula =B5>$B$3 with your range.

Answer 2

I actually figured out a more direct way than the answer I originally selected as the correct answer. Conditional formatting can use Greater than with a formula. I was just using the wrong formula...

This is wrong... Greater than =sum(B3)

This is right... Greater than =$B$3

Using the $ to separate the column and row selector stops it from being dynamic. Thus, B3 will not become B4, B5, B6, etc, as the formula gets applied to each row. It will always point to the value of B3, even if B3 is itself based on a formula (aka: not static).