Results 1 to 6 of 6

disregarding cells

This is a discussion on disregarding cells within the Excel Questions forums, part of the Question Forums category; hi there, I have a cell with the formula =AM20-AL20 in it. What i would like to do is that ...

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Posts
    93

    Default disregarding cells

    hi there, I have a cell with the formula =AM20-AL20 in it. What i would like to do is that am20 is a calculated cell. If am20 does not have a value in it, then i would like to disregard this so that i do not get a value error in the cell next to it, please help!!!!!

  2. #2
    Board Regular
    Join Date
    May 2004
    Location
    Vantaa, Finland
    Posts
    1,216

    Default Re: disregarding cells

    Would this do it

    =IF(ISNUMBER(AM20),AM20-AL20,"No value in AM20")

  3. #3
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: disregarding cells

    Or:-

    =IF(AM20="","",AM20-AL20)

    Or:-

    =IF(AM20,AM20-AL20,"")

    Pekkavee,
    You don't need to use ISNUMBER. My second formula will do the same thing as yours.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  4. #4
    Board Regular
    Join Date
    Jun 2004
    Posts
    93

    Default Re: disregarding cells

    thanks very much, all work

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: disregarding cells

    Quote Originally Posted by Todd Bardoni
    Or:-

    =IF(AM20="","",AM20-AL20)

    Or:-

    =IF(AM20,AM20-AL20,"")

    Pekkavee,
    You don't need to use ISNUMBER. My second formula will do the same thing as yours.
    Since the OP states that AM20 houses a formula with apparently delivering either a number or a formula-blank, the second formula you're referring to will fail with formula-blanks.

    The first formula and one with ISNUMBER would cover the same ground if AM20 is empty or houses either a number including a real 0 or a formula-blank. If one wants to exclude the real 0's, then:

    =IF(N(AM20),AM20-AL20,"")

    would be the course to take.

  6. #6
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: disregarding cells

    Thanks, Aladin.

    My apologies Pekkavee.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

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