Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

This is a discussion on Conditional Formatting - Greater then / Less then / 0 or blank within the Excel Questions forums, part of the Question Forums category; I'm trying to do something that I think is simple but just can't get the last conditional format. I have ...

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    13

    Default 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. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,097

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

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Jan 2011
    Posts
    13

    Default 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. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,097

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

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    13

    Default 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. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,097

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

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Jan 2011
    Posts
    13

    Default Re: Conditional Formatting - Greater then / Less then / 0 or blank

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

    https://picasaweb.google.com/1005548...31184986741762

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

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,097

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

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,097

    Default 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
    Last edited by xenou; Feb 8th, 2011 at 11:17 PM.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    New Member
    Join Date
    Jan 2011
    Posts
    13

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com