2

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already.

From what I could gather, if I want to apply a conditional formatting based on the value of another cell, I have to use a custom formula. However, in the list, there are a couple options that would seem to be much more relevant: "Greater than (or equal to)", "Lesser than (or equal to)", "Is (not) equal to", "Is (not) between". But I'm starting to think that they are not so useful.

Here I have a column of numbers ranging from 10 to 30; I want to highlight them in green if they're greater than or equal to a number (20) set in another cell. From a few other questions here and on SO, it would seem that I should use a custom formula as something like =K7>=K4, but instead I still wanted to try the options in the list that seemed more intuitive.

enter image description here

As you can see though, it does some weird things, where it consider 11 to be under 20 (which is true AFAIK), but everything else above 11 is either equal or greater than 20.

My guess is that putting =K4 in the "Value or formula" field didn't quite link to the actual K4 cell, but I can't figure out why it does what it does here.

If I put "20" in the field, it highlights the right cells (but it's not dynamic anymore).

Edit: putting =K$4 works like a charm. Quite literally, in this case.

2

You identified and solved the issue correctly: =K4 is a relative reference, so when it is applied to the range K7:K26, the following happens:

  • K7 (being the upper left corner of the range) gets compared to K4, as stated in the formula
  • K8 (one cell down) gets compared to K5 (one cell down)
  • K9 (two cells down) gets compared to K6 (two cell down), and so on.

If this sort of comparison is not what you want, then the absolute reference =K$4 should be used.

Note that the above logic applies equally well to custom formula formatting, and to "greater than", "less than", etc.

  • OHHH oh wow, that makes so much sense; that is actually really useful to know! Thank you very much! I'll wait a couple more days before accepting as per norm, but this answers my question perfectly already. – Linkyu Nov 1 '15 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.