Excel Graphs and Null Values 2

[GummowN]

This is a fun one for you all. I am exporting data into excel from Access, some of these values are null. I pass this information to management who being management find it difficult to review numbers so instead want pretty pictures.

They simply click the Graph Wizard and get confused.

They complain, I then change the values so if it is null then it displays zero. They complain. I then change the value to be the previous records value if it is null, they complain. I now have to sit manually create the graph then drag the rows over where only the values are not null.

Does anyone know why excel does this? Or a quick work round so that when a chart is produced it marks null records and records that dont exist.

Thanks - I will put the rope away for a couple of hours

 

[SkipVought]

Hi,
Use Auto Filter to select NOT NULL. Your chart will only reflect displayed values. Skip,
SkipAndMary1017@mindspring.com

 

[onedtent]

when chart is highlited, under Tools, Options, Chart, u can select whether u want Blank cell Not Plotted, Zeros or Interpolated.

is that what u r lookin' 4?

 

[GummowN]

That doesnt work as when you select a column and choose non blanks it removes the whole row where the cell of that colunn was blank

 

[GummowN]

Onedtent - thanks but that doesnt help when they just run the wizard. If you get Excel97 to chart wizard a table with blank records it has a fit and uses all the records bar the last one as the name and the last value as the value for the series. Any more suggestions on this one.

 

[SkipVought]

Then it is not clear exactly what you want to do with the NULL value AND the row associated with it. Skip,
SkipAndMary1017@mindspring.com

 

[GummowN]

Take a table as below

Date Val1 Val2 Val3 Val4
Yesterday 3 6 7
Today 5 7 9 8
Tomorrow 7 5

I want a chart that plots all the values and ignores the nulls. Therefore you get Yesterday plotted with 3 datapoints, today with 4 and tomorrow with 2

 

[GummowN]

I can get it to work on some data but not others, I can mail anyone a spreadsheet with an example on if they want to try this

 

[GummowN]

I know this seems fairly strange but I have managed to find the reason why this occurs. The chart wizard has a fit when a row of data contains only 0 or null records, when this occurs the wizard produces the 1 value graph.

I am sure I can come up with a method of filtering out the data from access.

Thanks for all suggestions

 

[onedtent]

funny... that's exactly what i get when i plot your values

so, dunno what ur complaint is

u just want three bars for yesterday & two for tomorrow without any gaps? i would like to know too, pls post if u find out

good luck

 

[SkipVought]

Tiy need to change you table because you have non-normalized data. Val1 thru Val4 are all values of an identical type; ie the values for Yesterday could be...
3 6 7
or
7 3 6
or
6 3 7
OTHERWISE, the null has significance.

Therefore, your table should probably look like this with 10 rows, according to your example...
Date Value
Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

onetdent - it'll work if the cells are EMPTY but not if they just contain a zero length string or a 0

The only way round this I've found is to use a macro to clear the cells. Just select the data range and run it

Sub ClearForChart()
For Each c In Selection
If c.Text = "" Or c.Text = "0" Then
c.ClearContents
Else
End If
Next
End Sub Rgds
~Geoff~

 

[GummowN]

Bloody Hell!!!!

Top answer - I doubled starred I thought it was so good!

 

[xlbo]

Aw shucks
Happy to help Rgds
~Geoff~

 

[onedtent]

u don't really say aw shucks, do u?

anyway, since GummowN couldn't give u 2 stars, here is another one.

 

[SkipVought]

Select your chart

In Tools/Options/Chart select the option to Plot empty cells as not plotted

In your DATA, delete ALL values from the "emnpty" cells Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

Hey Skip - isn't that what my little sub does ??
[confused] Rgds
~Geoff~

 

[SkipVought]

Geoff,
I had not seen your posting. My point is that you can control what get plotted in the Options/Chart window when the data is consistant. Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

Fair 'nuff
and "praise be indeed for consistent data"
Rgds
~Geoff~