Thread: Setting/Plotting Cell Value as "Empty"

What I'd like to accomplish:

From a calculated data set which I am then plotting, when the value is a pre-defined character/code ie "-" (accomplished through a basic IF statement), that the Chart plotting this data set reads these specific data points as being empty, so it does not appear in the plot at all. I've found that any value in the cell will at least plot as a zero value on the chart...

I don't want to have to manually go in and delete whatever values are in the cell since my data is dynamically changing based on peripheral settings.

How can this be accomplished?
Is there a way to set/format/define a cell as "empty" for charting purposes via some formula or code?
Is there a way to set/format/define a chart as potting pre-defined values as "empty" or non-values?

Please help.

Thanks for posting a reply Oldchippy,

The link you posted is one I've come across trying to find an answer to this little problem of mine and unfortunately it's not quite what I'm looking for.
What you point to is the setting that tells Excel how to treat emty cells, what I'm trying to do is to find a way to define cells as "empty".

Hmmm...not sure if I'm being too obtuse.
Basically I have a cell, it has a formula in it (or formatting on it), such that under a specific condition, this cell registers (for charting purposes) as being empty, even though there's a formula there...can this be done?

Oldchippy,

Thanks again for your help, anything you can suggest will likely be helpful!


I've attached the file to try and explain...some quick notes:
- Column's B and C, although they're integer values in this sheet, are actually going to be pulled data through LookUp's from a much larger data set and will be pulled through a separate qualifier, ie Model Number (which will be user entered and will update the tables automatically)
- it's the last three data points in Column D (for this specific case) that I'm looking to get some help on

As you can see, in the plot the "-" points in the CFR data on row's 21-23 are plotting as zero value...I'd prefer it treat these cells as "empty" ie, they don't appear on the plot at all. This way I can trend the CFR line without having the zero values skew it.

Possible?

Oldchippy,

What you propose works, however, it is a manual excercise that I'll have to repeat every time I adjust the data.

I made some changes to our "example" to show you the bigger picture.
If you notice on this version changing the Model from 1 to 2 will auto-populate the table and in turn re-calculate the CFR value...

Is there any way to set up this sheet so that Excel will auto-detect the "-" and not plot it without the user having to go in and do it manually?

Oldchippy,

Sorry to go back and forth on this, I hope it's not getting too annoying for you, I really do appreciate your time.
I did notice your note before to remove the data points.
However this would be purely a cosmetic change...

Try this, if you add a Polynomial trendline to the line that's there now, you'll see it shows a relatively straight line with a downward trend at the end, now when you remove the data for those last few "-" points (ie by going in and deleting the cell contents), you'll see that the trend line becomes a parabolic U shape. It's this U shape I'm after...but I don't want to have to remove the cell contents manually every time, since whether or not they have a "-" value will depend on the data-set as defined by Model number.

Thanks a lot for trying OldChippy!!!!

Anyone else know how to accopmplish this...or even if it can be done?

This is what you need to do.
Go to your name manager (should be under formulas) and create a new name, like "data"

Set the scope to the sheet with your data.

In the Refers To: field put this:

Code:

=OFFSET(Sheet1!$A$1,1,0,(COUNTIF(Sheet1!$A:$A,">0")+COUNTIF(Sheet1!$A:$A,"<=0")),1)

in case someone is wandering i had to use COUNTIF instead of COUNTA because my empty cells have formulas in them that returns "" if no value is presented yet. For whatever the reason COUNTA does not see those cells as blank. Also i could not use (COUNTIF(Sheet1!$A:$A,"<>"""), i kept getting an error.

Anyway once you have the name set when you go to import the data you need to have this in your data range:

=Sheet1!data

This should do the trick.
HTH

Hey HTH,

Thanks a lot for your help, unfortunately I'm having some trouble following your instructions. I guess I'm pretty much a noobie in this respect.

Where can I find the "name manager" you refer to?...By under formulas, do you mean I should have a Formula menu?

Or are you referring to naming a range as a single variable name....as in what you'd do to set up a drop-down list referece when trying to validate cell content...

When you say the "sheet with your data" are you referring to the raw data where line items are captured...of the table that sorts and counts it ultimately populating the chart? I don't beleive I've ever come across a "refers to" field that was labeled as such...

Is there perhaps a tutorial or something you can reccomend so I can become familiar with the terminology you're using to help me re-create what you've suggested? You obviously know quite a lot about this stuff...and I woudn't want to burden you with having to re-explain your suggestion...

Thanks for your time...
E

1. Do Insert > Name > Define

2. In the box Names in workbook, enter CFR

3. In the Refers to box, enter =OFFSET(Sheet1!$D$4, 1, 0, COUNT(Sheet1!$D$4:$D$23)), then press OK

4. Select the series in your chart, and in the formula bar appears =SERIES(,Sheet1!$A$5:$A$23,Sheet1!$D$5:$D$23,1). Edit it to read =SERIES(,Sheet1!$A$5:$A$23,Sheet1!CFR,1).

You're now plotting a dynamic named range.

Dude, you rule!


That worked perfectly...thanks a bunch!!!