Results 1 to 5 of 5

Thread: If isblank or equals zero
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If isblank or equals zero

    Hi guys,

    I hope you can help,

    I’m trying to achieve something here that I just can’t seem to get my head around.

    I want it to work out what i8/i7 is, but only if both of them have a value. I have a working formula for that. =IF(OR(ISBLANK(I7),ISBLANK(I8)),,I8/I7)

    However when they’re both showing 0, it returns #DIV/0! Because it can’t divide two zeros.

    How can I put if I7 or I8 is blank or if I7+I8=0, ignore the formula otherwise do I8/I7?

    Ultimately, I would like a formula that says if either cell is blank show 0, if the sum of both are equal to or less than 0, show 0, if they total more than 0, show the number.

    Is this possible or am I asking too much?

    I hope this makes sense.

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,324
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If isblank or equals zero

    Hi Asw091,

    Here's two possibilities, I'm sure there's more:

    =IF(OR(ISNUMBER(I7)=FALSE,ISNUMBER(I8)=FALSE),0,I8/I7)
    =IFERROR(I8/I7,0)

    HTH

    Robert

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,750
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: If isblank or equals zero

    It is the bottom number that cannot be 0 (dividing by 0 is undefined)... the top number is immaterial. So you might consider doing it this way...

    =IF(I7=0,"",I8/I7)
    Last edited by Rick Rothstein; Jun 16th, 2019 at 07:18 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,873
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: If isblank or equals zero

    Ultimately, I would like a formula that says if either cell is blank show 0, if the sum of both are equal to or less than 0, show 0, if they total more than 0, show the number.
    Another possibility maybe...

    Excel 2010
    IJ
    7-2
    880

    Sheet2



    Worksheet Formulas
    CellFormula
    J8=IFERROR(IF(OR(ISBLANK(I7),ISBLANK(I8)),0,IF(I8/I7>0,I8/I7,0)),0)

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    May 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If isblank or equals zero

    Quote Originally Posted by Rick Rothstein View Post
    It is the bottom number that cannot be 0 (dividing by 0 is undefined)... the top number is immaterial. So you might consider doing it this way...

    =IF(I7=0,"",I8/I7)


    🤯 no way. Talk about over thinking it..

    Thank you.

Some videos you may like

User Tag List

Tags for this Thread

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
  •