Conditional formatting not working

[BiotechBen]

Hey guys, trying to set up a spreadsheet with conditional formatting, but Sheets is doing something very strange.

When I set up formatting on a single cell, with just a numerical value, it seems to work perfectly. But when I apply it to the cells that are the end of a formula: it behaves one of two ways: it either randomly applies the formatting or applies none of it at all.

(The last number set in the list should format as red)

Is there something I am not understanding about how conditional formatting is applied?

[BiotechBen]

Update:

The formatting seems to apply the formatting color to the first 4 cells in a series and without following the format rules beyond a "selected color, 4 cells"

[Avocado Diaboli]

First off, you're sitting at a computer. Take screenshots, not pictures of your monitor. Between the blur and the Moiré pattern, it's unnecessarily hard to figure out what I'm looking at.

 

Second, why are you recalculating the values here when you already have cells that have these values? I mean here, the top formula is just the same as the value of N9 and the bottom one is the same as the value of N7. Just refer to the those values directly instead of typing the same calculactions into every conditional format

 

Lastly, you need to use absolute cell references. By default, if you refer to a cell by its address, all other cells in the range that format applies to use that as a relative starting point. So let's say if you want to format the cells from A1:A5. We want to color them based on a value in B1, but we only use a relative cell reference. So cell A1 looks at the content of B1 and formats itself accordingly. A2 doesn't look at B1, it looks at B2, because by default the cell reference is relative to the cell you want to format. To Sheets, it looks like you gave it the following order for A1: "Look at the cell next to you on the right and check what value it has." So when it's A2's turn, it looks at B2, A3 looks at B3, etc. because those are the cells right next to them.

 

To use absolute cell references, you need to type in the cell name like this $B$1. The $-sign in front of each element says that you want to make sure it only looks at what you specify. So for example if you use $B1, it means that it only looks at column B, but it can be on any row on that column. B$1 means that it only looks at row 1, but it can be in any column on that row. $B$1 means that only that single cell is used for the calculation, regardless of what the target range is. So in your case, it should look something like this:

 

 

As you can see, I'm using the values calculated in column N directly in the calculation, not doing the same calculation again and again for each format.

 

[BiotechBen]

11 hours ago, Avocado Diaboli said:

First off, you're sitting at a computer. Take screenshots, not pictures of your monitor. Between the blur and the Moiré pattern, it's unnecessarily hard to figure out what I'm looking at.

My bad, will do better. Was beyond frustrated at the time.

 

11 hours ago, Avocado Diaboli said:

Second, why are you recalculating the values here when you already have cells that have these values? I mean here, the top formula is just the same as the value of N9 and the bottom one is the same as the value of N7. Just refer to the those values directly instead of typing the same calculactions into every conditional format

That was a last ditch effort, I'd found some amount of success with full formulas instead of referencing just the cell

 

11 hours ago, Avocado Diaboli said:

To use absolute cell references, you need to type in the cell name like this $B$1. The $-sign in front of each element says that you want to make sure it only looks at what you specify. So for example if you use $B1, it means that it only looks at column B, but it can be on any row on that column. B$1 means that it only looks at row 1, but it can be in any column on that row. $B$1 means that only that single cell is used for the calculation, regardless of what the target range is. So in your case, it should look something like this:

The absolute references have been the bane of my existence in troubleshooting. Would've thought it would occur to me by now, but I guess it still hasn't caught on with me.

Many thanks.