# Can a formula result in an 'empty' cell?

1. ## 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. ## 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. ## 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>

4. ## 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?

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

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

Formula:
`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.

6. ## 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!

#### Thread Information

##### Users Browsing this Thread

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

#### 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