Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Leave other cells blank if no data in cell.

This is a discussion on Leave other cells blank if no data in cell. within the Excel Questions forums, part of the Question Forums category; Hi, I have 2 similar question. I'm using a formula that I got from a previously made excel sheet. The ...

  1. #1
    Board Regular
    Join Date
    May 2009
    Posts
    151

    Default Leave other cells blank if no data in cell.

    Hi, I have 2 similar question.

    I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
    =INT((D10-10)/2)

    The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.

    If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.

    I think it has to do with using an IF statement followed with ""? Am I on the right track?

    Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?

    I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.

    Thanks!

  2. #2
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,859

    Default Re: Leave other cells blank if no data in cell.

    Hi & Welcome,


    Problem #1, try =IF(D10="","",INT((D10-10)/2))

    Problem #2, try =IF(SUM(AP3:AQ6)=0,"",SUM(AP3:AQ6))
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  3. #3
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by shemayisroel View Post

    Problem #2, try =IF(SUM(AP3:AQ6)=0,"",SUM(AP3:AQ6))
    If there might be negative numbers involved checking for SUM = 0 might not be a good idea.

    Try this instead:

    =IF(COUNT(AP3:AQ6)=0,"",SUM(AP3:AQ6))

    Or:

    =IF(COUNT(AP3:AQ6),SUM(AP3:AQ6),"")

  4. #4
    Board Regular
    Join Date
    May 2009
    Posts
    151

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by #NAME? View Post
    If there might be negative numbers involved checking for SUM = 0 might not be a good idea.
    Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

    I now have an idea and I'm wondering if it's even possible to do with a formula.

    I currently have cell K20 with this formula:

    =SUM(Y20,AA20,AC20,AE20,AG20)

    The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

    Is that possible?
    Last edited by Oryan77; May 9th, 2009 at 01:53 AM.

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Leave other cells blank if no data in cell.

    Use the same technique:

    =IF(COUNT(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,699

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by Oryan77 View Post
    Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

    I now have an idea and I'm wondering if it's even possible to do with a formula.

    I currently have cell K20 with this formula:

    =SUM(Y20,AA20,AC20,AE20,AG20)

    The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

    Is that possible?
    Try...

    =IF(ISNUMBER(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

    Note that 0 is also a number.

  7. #7
    Board Regular
    Join Date
    May 2009
    Posts
    151

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by Aladin Akyurek View Post
    Try...

    =IF(ISNUMBER(Y20),SUM(Y20,AA20,AC20,AE20,AG20),"")

    Note that 0 is also a number.
    Well it seems to still be adding in the number that I have entered in cell AA20. It seems to be acting the same as when I had it figuring up the sum of all of those cells. I see no change.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,699

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by Oryan77 View Post
    Well it seems to still be adding in the number that I have entered in cell AA20. It seems to be acting the same as when I had it figuring up the sum of all of those cells. I see no change.
    Enter 1 in Y20.

    Enter L20:

    =ISNUMBER(Y20)

    what result do you get in L20?

  9. #9
    Board Regular scottylad2's Avatar
    Join Date
    Feb 2009
    Location
    East Lothian.....near Edinburgh
    Posts
    1,913

    Default Re: Leave other cells blank if no data in cell.

    Quote Originally Posted by Oryan77 View Post
    Nope, no negative numbers will ever be entered. So I think shemayisroal's formula is just what I needed. Thanks guys!

    I now have an idea and I'm wondering if it's even possible to do with a formula.

    I currently have cell K20 with this formula:

    =SUM(Y20,AA20,AC20,AE20,AG20)

    The thing is, I don't want it to add up those cells unless I have a number entered in cell Y20. So, if I have numbers in the other cells, but not in cell Y20, then I would like K20 to be left blank. But if I enter a number in cell Y20, then I would like K20 to go ahead and add up all of those cells.

    Is that possible?
    I've used my own cell refs, but this works
    Sheet1

     GHIJKL
    20 10152525 

    Spreadsheet Formulas
    CellFormula
    L20=IF(G20="","",SUM(H20,I20,J20,K20))


    Excel tables to the web >> Excel Jeanie HTML 4

  10. #10
    Board Regular scottylad2's Avatar
    Join Date
    Feb 2009
    Location
    East Lothian.....near Edinburgh
    Posts
    1,913

    Default Re: Leave other cells blank if no data in cell.

    Sheet1

     GHIJKL
    20251015252575

    Spreadsheet Formulas
    CellFormula
    L20=IF(G20="","",SUM(H20,I20,J20,K20))


    Excel tables to the web >> Excel Jeanie HTML 4

Page 1 of 4 123 ... 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