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

Conditional formatting of cells based on formula result not equalling zero

This is a discussion on Conditional formatting of cells based on formula result not equalling zero within the Excel Questions forums, part of the Question Forums category; I want to highlight cells containing formulas based on the result of the formula not equalling zero. Two columns Column ...

  1. #1
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Conditional formatting of cells based on formula result not equalling zero

    I want to highlight cells containing formulas based on the result of the formula not equalling zero.

    Two columns
    Column I Column J
    Account Name 1 $0.00
    Account Name 2 $20.00
    Account Name 3 $0.00
    Account Name 4 ($10.00)


    Column J contains a VLookup formula.

    I know I need to use a formula in conditional formatting, but can't get it working.

    Any suggestions?
    Last edited by elemenopee; Mar 20th, 2017 at 07:32 PM.

  2. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    I want to highlight cells containing formulas
    Does that mean you are only looking at cells that contain formulas, not entries?

    if so, formulas and functions (generally) look at what is displayed in the cell, not what it contains - ie it would not be able to "see" if a cell contained data or formula

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Sorry probably wasn't clear. I want to set conditional formatting on columns J and I where the result of formulas in column J is not equal to zero. Every cell in in column J contains a vlookup formula.

    I thought a lot of functions looked at the content of the cells and not the result. For example if I set the condition as J11<>0 it won't work. I've tested the IF function by keying into a test cell on the actual spreadsheet =if(j11<>0,"yes","no") and the returned result is "no". This suggests to me that the IF function looks at cell content ie the formula and not the result.

    I've played around with a few other functions eg ISNUMBER and SEARCH but keep on coming up blank, most likely because I'm not formatting the formula correctly.
    Last edited by elemenopee; Mar 20th, 2017 at 07:54 PM.

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Quote Originally Posted by elemenopee View Post
    I thought a lot of functions looked at the content of the cells and not the result.
    No, just the other way round. As a test, if A1 contains =10*200 it will display 2000
    If you used =LEFT(A1,3) it would return 200 not =10

    The only time =J11<>0 would not work, would be if J11 did not contain EXACTLY 0. even 0.000000000001 is <>0

    How have you tried to apply the CF so far?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    The VLOOKUP formula is referencing an import file from a financial system. Results are in dollars and cents ie numbers with two decimal places. Nil results are always exactly 0, no chance of any .0000000001.

    I can't remember everything I've tried - have keyed in dozens of different formulas. I've been googling for solutions and tried combos of suggested solutions ISNUMBER and SEARCH and played around with them - but chances are high I had the formatting incorrect.

    Setting the condition as j11*1<>0 didn't work either.
    Last edited by elemenopee; Mar 20th, 2017 at 08:12 PM.

  6. #6
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    The VLOOKUP formula is referencing an import file from a financial system.
    That probably means that the amounts are not really numbers, but text looking like a number. Do a quick test to find out...
    =isnumber(cell-ref)
    FALSE indicates text and we will need to convert

    are high I had the formatting incorrect.
    Formatting only changes the visible appearance of a cell - it's cosmetics/how it looks, it does not change the underlying value, and if the cell contents are text anyway, then no formatting will change that to a number
    Last edited by FDibbins; Mar 20th, 2017 at 08:16 PM.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  7. #7
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    isnumber result is true.

    When I said formatting incorrect, I wasn't referring to the format of a cell, I meant the formatting or syntax of the formula I was using as conditional formatting.

  8. #8
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    OK so it looks like we are dealing with a valid number

    Assuming J11 is 1 of the cells you are testing, what does it contain, and what do you get if you just put that formula =J11<>0 into a cell somewhere?
    (working blind, so trying to narrow down the options here -bare with me lol)

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  9. #9
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Yep j11 is one of the cells I'm testing.

    formula result of =J11<>0 is false

  10. #10
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    OK so J11 contains 0

    And you want to test for cells NOT containing zero, correct?

    If so...
    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =$J11<>0 format fill as needed

    edit: what cell does your table start in? Adjust the J11 to match the 1st row you are testing
    Last edited by FDibbins; Mar 20th, 2017 at 08:42 PM.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

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