Conditional Formatting - Greater then / Less then / 0 or blank

Thanks:  0
Likes:  0

# Thread: Conditional Formatting - Greater then / Less then / 0 or blank

1. ## Conditional Formatting - Greater then / Less then / 0 or blank

I'm trying to do something that I think is simple but just can't get the last conditional format.

I have 2 columns B (weight) & C (% up or down) with 32 Rows. It is a weight loss chart that colour codes the ups and downs.
Cells in Column C have the formula "=IF(B32="","",((B32-B31)/B31))" to leave the cell blank if the cell in column B has no typed in value.

The formatting I have is as follows:

1. If Cell C32 is Greater than 0 shade it Red - Works
(Cell Value Is less than 0)

2. If Cell C32 is Less than 0 shade it Green - Works
(Cell Value Is Greater than 0)

3. If Cell B32 is Empty than C32 should have no colour. - Problem is that Cells Stay Red.
Formula Is =IF(ISBLANK(B32),"",IF(B32=0,B32,""))

Any quick ideas?

....but now that I read it a couple times I would need something that also states if cell in column C is equal to zero do not shade as well.

2. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

Try:
Sum(B32) = 0

Sum() will ignore the "" values and alsos capture blanks as zeros. Should work for you I think... (though I'm not clear whether you are trying to conditionally format cells in column B or Column C, or both)

3. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

The conditional formatting is only in Column C.

That didn't seem to help any.

Is it possible upload the excel sheet at all?

4. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

=IF(ISBLANK(B32),"",IF(B32=0,B32,""))

This is a long formula just to tell if a cell is empty. It doesn't really make sense. Can you try again to describe clearly what you have and what you want to achieve? Formulas used in conditional formatting must evaluate to TRUE or FALSE, but your formula can return and empty string (""). It is as if you are describing the formulas in the cells rather than your conditional formatting.

Also of course the original formatting in the cell (when no conditional formatting applies) must not be a color such as red.

5. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

That formula ( =IF(ISBLANK(B32),"",IF(B32=0,B32,""))) is something I found online ( http://www.excelbanter.com/showthread.php?t=184071) and seemed to work only halfway though. But I am open to suggestions for sure.

What I'm trying to have happen is this:

Column B I type in my weight and in column C (formula spits out a percentage based on previous weight entry) I would like the cell to be red if it is greater than 0 or green if less than 0 or stay white if there is no number.

But I think because I have a formula in column c, it is treating it as a greater then value because all the empty cells are shaded red.

Just found the insert image button...created a online picasa album to upload a screenshot. Might help to see it. If that doesn't work go here: https://picasaweb.google.com/1005548...351/OddsNEnds#

6. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

You need to read up on conditional formatting so you understand it better. There is no need for a formula, just use:

Conditional format #1: Cell Value Greater Than 0, Format Red
Conditional format #2: Cell Value Less Than 0, Format Green

There is no need to take action if the cell is blank - that will be the default (normal or unchanged) format that is in the cell if neither conditional format applies.

ξ

7. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

I tried that again but the blank cells are still showing up as red.

I'm sure it's something simple but I'm blind for looking.

8. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

All I can say is keep trying and you'll get it eventually. It's the sort of thing that takes two seconds to show and 2 hours to explain. If you really want help you'll have to report what version of Excel you have and exactly (in excruciating detail) what you are doing to create the conditional format: what buttons you hit, what options you select, what values you enter, and so on.

ξ

9. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

I would also suggest you "practice" on a simple worksheet with only one cell - try to get it to show up green when you put a 1 in it Then add a second CF, to have it go red when you put a -1 in it. Then you're done. If you have it working in one cell you can "copy" the format to the rest.

Here's one walk through (for Excel 2007). You can find You Tube videos if you search google! It's a common topic, really. Just start searching Excel 2007 conditional formatting (or Excel 2003 if that's what you are using):
http://www.homeandlearn.co.uk/excel2...l2007s6p2.html

10. ## Re: Conditional Formatting - Greater then / Less then / 0 or blank

Thanks for trying and the shove in a good direction. I'll have a look at the sites suggested and see if I can get something to work. Like it shows in the first example over is red, under is green, zero is white...it's just that the C column cells that holds a formula in rows 13 to 32 keep turning red on me.

If I find something that works I'll post it back here so that way I might not be speaking so much Greek.

Thanks again for trying, and here's to learning.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•