Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

This isn't a VBA question, exactly, but I have to start somewhere.

Perhaps somebody can point me in the right direction.

I want a line chart not to plot any cell with a zero value. Various

methods of not *displaying* zeroes don't affect what a chart plots--it

plots the invisible zero.

Here's an example:

I have a column E filled with the following formula (adjusted, of

course, to reflect the row number in which each cell appears):

=IF(OR(A2=0,B2=0),"",D2*1.5)

I add entries to the data daily. As I enter values in columns A and B

in its row, the E cell displays the calculated result; until then it

appears blank. A line chart embedded in the same worksheet shows the

continuing effect of each entry.

At any given time the series shown on the chart may be set to plot the

contents of cells $E$2:$E$80, and,for example, only rows 2:15 have

data in them. Cells E21:E80 will display blank, but on the chart the

corresponding plot line will plummet to the X axis and stay there. The

result is ugly and unprofessional-looking. The plot line should just

end where the data stops.

True, I can tell the chart not to plot *empty* cells, but the presence

of a formula seems to mean the cells are not empty. I guess I'm trying

to find a way to tell the chart to treat a cell with a zero value as

an empty cell, and I'm beginning to think it can't be done.

Note: in the example given above, if I delete the contents of E21,

including the formula, so it really is a blank cell, the chart will

plot neither it nor any of the following cells in the column, even

though they still have the formula in them. I suppose I could use some

kind of VBA macro to put the formula in the E-column cells *only* as I

enter data in the A and B columns, but this seems to be a very awkward

kludge.

Anybody got any ideas?

Two solutions to this maybe:

1. The uglier solution, but quicker: where you've got

=IF(OR(A2=0,B2=0),"",D2*1.5)

change it to

=IF(OR(A2=0,B2=0),NA(),D2*1.5)

The chart won't plot the #N/A cells but will draw a line directly from

one valid point to the next, ie. no gaps. The ugly side of this is the

presence of a lot of cells displaying #N/A in the worksheet.

2. Leave your formula as it was, but create a named range with a

formula: Go into Insert|Name|Define... and enter say 'graph' (without

the single quote marks) in the 'Names in workbook' field. Then in the

'Refers to:' field paste something similar to the following:

=(IF(Sheet1!$E$2:$E$31<>"",Sheet1!$E$2:$E$31,NA()))

Then click the 'Add' button and close the dialogue box.

This will create a series of values like the cells in solution (1)

above, in the defined name 'graph' , and this is what we'll plot. Go

back to the chart and click on one of the points of your series so

that the whole series is selected. Look in the formula bar and you'll

see the likes of:

=SERIES(,,Sheet1!$E$2:$E$31,1)

It's the

'Sheet1!$E$2:$E$31'

that needs changing to

'Sheet1!graph,1)'

(without quote marks) and press Enter. (It may change to

=SERIES(,,Yourfilename!graph,1)

that's fine.)

Now you should see the same chart as in (1) above but without all the

#N/As in the worksheet.

A third solution is to create a dynamic named range instead of the

static one above when a chart as proposed in my previous answer would

be produced.

This is very strange. I KNOW I have done plots that simply omit non exsistent

data, but

couldn't figure out how i Used to do it [brain tells me there 'was' a

"Plot-Zero-Values"

option, but brain has been wrong B4].

I tried an X-Y plot - nope.

This may get you closer to a solution...

Here's what is the strangest.

I found that If I Paste Values (from the formula cells), the chart STILL

plots "empty" cells

as zero.

HOWEVER, if I Clear Contents in that cell, then the point and connecting

lines are

omitted!

So...

A "" is interpreted as a zero.

A Pasted Value of "" is interpreted as a zero.

a Cleared cell is undefined / unplottable.

So, do a TypeName on the three types of "blank" cells( "" in formula, a Pasted

"" from a

formula and a fully Cleared cell) to see what they are.

This may mean you'll need a macro to Clear a copy of the Zero valued cells.

Wa-Da-Ya-Think?

My recollection is clearly that it was on line charts. I rarely do

column charts. However, it is entirely possible that I may have

simply Cleared the values of the zero/blank cells. I also rarely do

plots of more than a few dozen values, so manual intervention for the

desired effect is always a possiblity.

I am, however, still curious what the types of those cells are...

So I checked. A Cleared cell is Type "Empty".

A cell containing "" is an [obviously null] "String"...which, of

course, "makes sense"...

SO, perhaps a macro to clear those pesky null cells (a copied

version) is in order.

Didn't find what you were looking for?
Find more on Want chart not to plot zero values?
Or get search suggestion and latest updates.

Related Topics:

- Macro to Suppress Rows w/ Zero Value
- Changing Chart attributes without activating the sheet/chart
- plotting graph in applet
- plotting in java
- Plotting a column vector
- Looping Through Data to do plots
- 2-line title on a xy line plot
- Stretch of x and y scales in plots
- Stretch of x and y scales in plots
- Free pop3 component wanted
- J2ee hosting wanted
- Writing Macro wanted Tips
- Looping thru values to match w/ other values
- Matching date values w/ dropdown values
- vba routine for eliminating rows with all zeros from spreadsheet
- Too many Zeros
- VBA to Change value in column AQ based on values in I, and AI
- Reading Binary Files with Leading Binary Zeros
- Need help removing trailing zeros
- JTable based on objects not updating when object values are changed
- Combobox value does not change
- Can I make all negative numbers have a value of zero?
- Code not updating cell value
- Bar Chart in VB.net
- Need help scraping data from java-chart