box & whisker ignore blanks&n/a OR expanding table which ignores blanks

Hi,

I am trying to make dynamic a box and whisker plot chart for a filtered data set. My issue is that some of the data points are "n/a", or in other words, not every row has applicable data. If I select that table of data and make it into a box and whisker plot, it will plot blanks and n/a's as zero, which messes up the data. This data will also be filtered, so the number of rows changes.

First of all, is it possible to for box and whisker plots to ignore blank cells? For some reason I cannot access "Hidden/Empty Cells" for this type of chart.

If not, is it possible to make a table that will automatically expand/retract to the number of rows that have values in them instead of rows which contain formulas? The issue is that to remove the n/a's from the data, I've had to make a formula that will only display numerical values. However, because the formula extends down the entire column, the table expands to more than just the numbers I want to plot and plots the blanks as zeros.

Thanks

From my experience, no. I have been battling the same thing with Excel for years. If the value is not available Excel counts it as zero. If I am wrong, someone please come in and correct me, but I have never found a way around this other than manually filling in the missing data by hand.

You could create an auto fill calculation that takes the previous value (A) calculate the difference between the ending value (B) and then adds or subtracts values depending on ascending or descending orders and empty space; like so:

VALUE = A [+/-] ((B – A) / C)

Please Login or Register  to view this content.

The above calculation was taken from this blog post on how to graph 3D data inside excel.

~Frab