Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a 'Blank' Cell (2002)

    I am trying to create a "blank" cell if certain conditions are met (divide by zero) so that the resultant value in the cell will trigger a "skip" when using the COUNTA function. As an example, I have a cell formula of =IF(ISBLANK(cell reference)," ",(fixed cell)/(cell reference)). If the cell reference is zero, the formula "should" return a blank.

    Unfortunately, " " (double quotation mark-spacebar-double quotation mark) does not create a "blank" that the COUNTA function recognizes.

    What is the correct way to create such a blank?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a 'Blank' Cell (2002)

    I'm afraid that there is no formula that will make a cell blank. A cell that contains a formula is by definition not empty, even if the formula returns an empty string ("" without a space in between). You'll have to find a different way to count non-blank cells. The test cellreference="" returns TRUE if the cell is really empty, but also if it contains a formula that results in "". If you have cells containing #N/A (or formulas resulting in #N/A), you must provide a way to handle those too.

    For example: =SUM(IF(ISNA(A1:A14),0,IF(A1:A14="",0,1))), array-entered (confirm with Ctrl+Shift+Enter) will count the number of cells in A1:A14 that are not empty or "" or #N/A.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a 'Blank' Cell (2002)

    Thanks. With a little tweaking, your solution worked PERFECTLY. Thanks.

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a 'Blank' Cell (2002)

    I have a similar problem, however, I am actually summing a range of data that may oftem times contain #DIV/0! in the cell as well as #NA. I understand why some of my formulas may throw this out as a result and thats find, however, I still need the sum to work for the cells that actually have numbers in them. How can I write a formula that will exclude any cell that has #DIV/0! or #NA in any given range?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a 'Blank' Cell (2002)

    A slight variation on my reply to Vikingpad will probably do what you want.

    To sum cells A1:A37, ignoring cells with text or error values, use

    =SUM(IF(ISERROR(A1:A37),0,A1:A37))

    It must be an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a 'Blank' Cell (2002)

    Great! Works exactly like I want.

    Thanks.

    LaMont

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a 'Blank' Cell (2002)

    A somewhat related question - I'm trying to graph some linked cells and I want the blanks in the original to show as blanks so they won't be plotted. I have a graph that changes as a user picks a different category from a drop-down control. The linked cells (that are the graph source) use indexing to find the correct row of data in the full data set. But if the full data set has blanks, the linked cells graph as zero's no matter what formula I use since the source is technically not blank. I could fill the source using a macro but I'd rather avoid that if possible so I won't have to deal with user education around security and opening a file with a macro. Any ideas?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a 'Blank' Cell (2002)

    You must use formulas that return #N/A if the original is blank. something like =IF(ISBLANK(A1),NA(),A1)
    #N/A is the only value that will be treated as an empty cell by a chart.
    See the thread starting at <post#=3686>post 3686</post#> for a discussion of this subject.

  9. #9
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a 'Blank' Cell (2002)

    Thanks - it works! I'd tried putting the #n/a in the original data, which worked but the customer didn't like. Forgot that I could do my link that way too.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create a 'Blank' Cell (2002)

    A slight clarification:

    #N/A is NOT equivalent to an empty cell in a plot

    a #n/a error does NOT plot the point but the point BEFORE and AFTER will be connected if you have it formatted to "connect the dots". This is the ONLY error that this will work for; all others will plot as if the number is a zero.

    A truly "empty cell" will NOT plot the point AND the LINE connecting the points will NOT be drawn "breaking the line". I have found no way to simulate this "blank cell" with a result, the ONLY way is to leave the cell BLANK/empty.

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a 'Blank' Cell (2002)

    Thanks Steve, that is useful knowledge.

Posting Permissions

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