Treatment of blank cells in chart driven by a combo box

[K. Georgiadis]

On this messageboard, I was provided with an effective
solution to convert non-numerical cells to blank cells
(using the ISNUMBER function) so that the text cells
would not be plotted as zero values.

HOWEVER, when I set up a chart driven by a combo box, the
blank cells were once again plotted as though they were
zeroes. The INDEX function used to select rows of data
seems to interpret blank cells as zeroes after all. The
fact that under Tools/Options/Chart I chose to NOT plot
blank cells has no effect.

Temporarily I overcame the problem by manually typing =NA
() in all the empty cells. Is there a more elegant way
around this problem?

Thanks in advance.

 

[Tushar Mehta]

You need to provide some more specifics about what you are doing.
Maybe, the actual formulas you are using might help.

Also, and this might only be tangentially related to your current
problem, but ISNUMBER cannot possibly convert anything to anything. It
simply reports whether a cell contains a number or not.
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[K. Georgiadis]

Hi!

I started with a data set of 12 rows x 15 columns of
data, mostly containing numbers 1 to 5. However, some of
them contained text ("nr", meaning not rated). My
objective was to create an XY scatter graph with each
row. In order to prevent the "nr" cells from being
plotted as zeroes, I used a formula such as: =IF(ISNUMBER
(A1),A1,""), converting the non-numerical cells into
blanks; I was successful in plotting the data without
zeroes.

However, as the next step, I created a chart driven by a
combo box, containing the 12 row titles. By using the
INDEX function, I was able to chart each row of data,
according to the selection in the combo box. The problem
was that, once I used the INDEX function to select the
rows of data, the empty cells were again plotted as
zeroes -- not good.

As I indicated in my previous email, I was able to
temporarily circumvent the problem by manually typing =NA
() in each empty cell.

I hope that this is an adequate explanation.

Thanks

 

[K. georgiadis]

as a follow up to my previous response, the solution has
been staring me in the face all this time:

1) transfer the data to a new location with the formula
such as =IF(ISNUMBER (A1),A1,NA()),
2) the cells containing text will be replaced with #NA
and, as a result, will not be plotted even when using the
INDEX function to create a combo-box driven chart.

Thank you for the willingness to help.

 

[Jon Peltier]

Good job. Thanks for letting us know that you fixed it, and how you
fixed it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______