# 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.

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.

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.

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))
``````

• 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. 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. Jun 11, 2016 at 17:52