3

What I want from Google Sheets

I have cell A1 with a value 10. I have cell A2 with value -11 and cell A3 with value 11 . I want to conditionally format cells so that when their value is less than -$A$1 they turn green, but use default formatting otherwise.

I told Google to format the range A2:A3 according to this rule: cell value less than -$A$1 (notice the explicit negation). Both cells turn green. I expect only A2 to turn green.

Working sample: https://docs.google.com/spreadsheets/d/1xEXR8q5RRonMrsUyz1w6a02uK6EpYPIXt8Wz2rER9Hk/edit?usp=sharing

What am I doing wrong?

Real world use case

I'm using Google Sheets as an accounting software for a friend because GNUcash is too much for her. I want to colour cells for a current (US: checking) account so that they're orange when her balance is below zero (she goes into overdraft) and red when her balance goes below -1000 (she's in real trouble now as she breaches her overdraft limit).

I'm doing crystal ball financial situation prediction (I enter provisional numbers at future dates), so this is useful if it works.

What I tried so far

I did try workarounds like:

  • have A1 hold a negative number and remove the negation in the formula
  • abuse: INDIRECT(ADDRESS(ROW(), COLUMN()))
  • RC, R[0]C[0] in custom formula
  • make another column next to the value which computes to TRUE or FALSE and then use R[0]C[1] or similar (I forgot what I did) in the conditional formatting - this sort of worked in a test, but I'd rather not added to the main sheets if I can help it.
Improve this question

2 Answers 2

Reset to default
3

Answering my own question:

=-$A$1 in a less than built-in condition

The = sign in front of the expression makes it an expression. Without it Google Sheets does weird stuff.

Improve this answer
3

To have the two different colors you need to have your conditional formats in order, so the first one being the red:

You can use format cells if less than or equal to and put -1000 as your value (see below images)

and then for the rest.

What I did was use indirect and address to dynamically refer any given cell to itself, and then point to a static number, in this case $A$1

=AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN(),4))),(INDIRECT(ADDRESS(ROW(),COLUMN(),4))<-$A$1))

enter image description here

enter image description here

Improve this answer
2
  • Holy Sheets...! Now imagine I have a whole year of cells (365 cells with this formatting). Will my computer commit suicide? :) I mean, I'll try this and see what happens, but the question stands. As I can guarantee that the cells are numeric or empty, I guess I can leave the AND and its first operand out. Based on your answer, and without trying anything yet, I was using ADDRESS wrong - no third parameter.
    – Radu C
    Jun 11, 2016 at 17:39
  • OK... I found something a lot less WTFy: =-N($A$1). I also realised that I wasn't using = in front of the expression. On a hunch I got it even shorter: =-$A$1 They need to document that = thing better.
    – Radu C
    Jun 11, 2016 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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