+ Reply to Thread
Results 1 to 6 of 6

Can a formula result in an 'empty' cell?

  1. #1
    Registered User
    Join Date
    09-30-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    Can a formula result in an 'empty' cell?

    First post. I hope it's not too dumb.

    My little problem is fairly simple, I add new data to a spreadsheet every day (or week) and have it show up on a chart. But, some of the chart data is created by a formula based on the data I input. So, in order for the chart to look 'good' (relative term), I have to have the blank or zero results come out as NA() for all the columns I have yet to enter data for - per Jon Peltier's 2009 article (http://peltiertech.com/WordPress/min...g-empty-cells/).

    That's OK, but it there any way of making the empty (future) data portion look nicer? If I use NA() as the result of my formula then, of course, this results in '#N/A' (or '###' if the column is too slim) in the cell. And if I try to use 0, space (" "), blank, or copy a blank cell, then the chart treats this as a zero and the chart has a waterfall effect.

    This may not have a solution (that fact that I've got a formula in the cell that I want my chart to treat as 'empty' seems to indicate this).

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can a formula result in an 'empty' cell?

    Any chance you could upload a sample spreadsheet file with chart so we can reference your sheet while exploring the article?

    Be sure to de-sensitize the data.

  3. #3
    Registered User
    Join Date
    09-30-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    Re: Can a formula result in an 'empty' cell?

    Took a while to work out the upload but here's one I prepared earlier. This was a simple one I've just done to track my weight using squiggly lines (and show my quandary). Only 3 rows are used in the chart, with the '1' row on the horizontal axis and kg ('2') and %('5') on the vertical.
    To show my question, I've created the value in the '5' row using "=IF(B3>0,B3+65,NA())".
    Any other value in the false result causes the chart to use the value 0.

    <EDIT>I may have de-sensitised the data by faking my weight and body fat - or did I?</EDIT>
    Attached Files Attached Files
    Last edited by Cloth Ears; 10-01-2014 at 01:09 AM. Reason: humour

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can a formula result in an 'empty' cell?

    I am not a chart expert at all, but I can think of 2 ways to *maybe* get around this.

    1. hide the columns for the dates not reached
    2. If is a case of having a "good looking" table and a "good looking" chart, maybe have a separate table feeding the chart, and have the "chart" table fed from your "gtood looking" table?

    Im sure other members will offer a better suggestion, but food for thought?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can a formula result in an 'empty' cell?

    Try selecting B5 through QW5 and conditionally formatting by this formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Be certain that B5 has relative addressing (i.e. no $$$'s).

    Now choose the color white for your font color.

    Does this do what you are wanting?

    And BTW: It's not a dumb post.
    Last edited by FlameRetired; 10-01-2014 at 11:10 AM.

  6. #6
    Registered User
    Join Date
    09-30-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    Re: Can a formula result in an 'empty' cell?

    Thanks FlameRetired. And FDibbins.

    You got me thinking about hiding, so I set the font colour to white and added a conditinal format to show if the value was greater than 0. Which solves the visual 'problem' when it comes to looking at the data.

    I guess it also answers my other question in the title - you can really only have an empty cell when it's empty. Annoying for someone from an assembly background. Empty could mean hex 0's, or a variable length of 0 - either one should be able to be reproduced (i.e. EMPTY() as a function).

    I'm getting grumpy in my old age...

    Ciao

    P.S. FlameRetired - I call it a 'dumb' question as I wasn't able to find a solution out there. Maybe not strictly dumb, per se, but I felt a little foolish posting it. After 30+ years IT-ing around, you'd have thought I could find an answer. Much obliged!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-04-2012, 02:19 PM
  2. Replies: 10
    Last Post: 12-09-2011, 11:03 AM
  3. Replies: 6
    Last Post: 09-16-2010, 05:19 AM
  4. Formula Return result from non empty cell
    By innocent_eyes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2008, 03:23 AM
  5. [SOLVED] Formula result as real empty/blank cell
    By Excelerate-nl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2006, 01:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1