Help with Charting 1

[Dennyd]

I'm generating some year-to-date charts with weekly data. I have everything set up the way my boss wants it, with the whole year shown on the x-axis. The problem is that my plot line drops to zero as soon as it encounters a week with no data. Can someone please help me to find a way to limit the plot line to the most recent data, without dropping to zero? I'm looking at naming ranges but would need a way to easily update the range as soon as the next week's data is entered. I'd consider macros if that will do it.

Thanks in advance.

- - -Dennyd

 

[GregLocock]

Use x y plots and put a space into the empty cells. This just stops the curve at the last plottable point.


eg

1 45
2 46
3 44
4 (space)

plots as just three points in a line

you can even use this as a trick



1 45
2 46
3 44
4 (space)
5 87
6 67

will plot one line with 3 points and one with two


Cheers

Greg Locock

 

[joerd]

With the chart selected, take a look at Tools menu / Options / Chart tab. There you can set how empty cells are displayed in the active chart (leave gaps, zero, interpolate).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Dennyd]

Thanks for the replies but these still don't solve my problem. Greg, I've used the blank space technique before and it works well when applicable. Thanks, Joerd, I didn't think to look under Tools. I had been scouring the Chart options. My default was already set to "Plot empty cells as: Not plotted (leave gaps)". I also tried all the other options in that dialogue box, but none worked.

Here is a clearer picture of my situation and why I'm having a problem. My raw data exists in a large spreadsheet and is rather spread out. Therefore I have referenced all those cells into a local area for ease of plotting, to wit, cells B93:B145 contain my date range for all the x-axes, C93:C145 contain Data1, D93:E145 contain Data2 and Data3. I have three charts. Since I have partial year data, cells C93:C116 (currently) contain the YTD values for Data1-Data3. The date range is fully populated. I believe the problem comes from the "empty" cells that have a null value and blank display, not a zero. They are null value because the cells they refer to in the raw data are also null value. However, these cells referenced by the data series are not actually empty and the chart is interpreting them as zero value and therefore plots them as zero. If I delete the cell reference in the first null cell the chart displays the way I want.

Now I'm thinking a macro might be the best way to go. I was thinking it could copy the appropriate cell references into the data range; check for null values then copy a blank cell into nulls. This is not my first choice by any means, but I'm just looking to get this solved cleanly.

This spreadsheet also needs to be pretty foolproof. If I were doing it for myself then manually handling this might not be an issue. But this is for my boss who is then going to give it to an administrative assistant. I'm going to be protecting and hiding cells as a matter of SOP to help prevent someone goofing something up.

Got any ideas how to make this slick and easy?

Thanks,

- - -Dennyd

 

[JoeTank]

I have solved this problem by using an x-y scatter plot. Click on the ploted line and you will note that the data being ploted is highlighted... a box is highlighted around the data rows and columns. Then click on the lower corner of the highlighted data range and drag it to the last point you want plotted. Everytime you add more data for the year to date plot you will have to click and drag the data rows and columns accordingly.

Steve Braune
Tank Industry Consultants

http://www.tankindustry.com

 

[joerd]

The problem I see with a macro is that you'll either have to have your boss or his assistant press a button after updating the chart data, or use the Worksheet_Change event to fire it.
One thing I found out while trying a few things is that a cell containing an #N/A error will be ignored (no point displayed, the line connecting the chart points will go from the previous point to the next one). Now, you can generate a #N/A error by using the [tt]=NA()[/tt] function, so in you plotted dataset, you'll need something like [tt]=IF(A1=0,NA(),A1)[/tt]
I hope this suits what you're trying to accomplish, but I'll be happy to help you with the macro if you need it.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Dennyd]

Joerd,

Thanks and a star for you for getting me there. When I tried to use what you suggested it didn't work.

=IF(A139>0,A139,NA()) or =IF(A139>0,A139,#N/A)

The numbers in A139 are either positive percentages or null. If there was a value in A139 then this cell showed the correct value. However, if A139 showed a null value this didn't display the #N/A. I tried other numeric and sign tests of the value of A139, but none worked. After more digging I found that I needed to check to see if the value in A139 was numeric!

=IF(ISNUMBER(A139),A139,NA()) Eureka! I've found it!

I used this in all the cells and got the results I needed. This is an elegant and automatic solution!! (I was disappointed in not finding information on what "values" get plotted in Excel's help. Your tip that #N/A doesn't plot but other error types do was the clincher!)

Incidentally, I did run across another solution in the internet from "Charting Dynamic Data",

http://www.ozgrid.com/Excel/DynamicRanges.htm.

This would work, but is a little more involved than the solution stated above. Both are nice alternatives to using a macro, don't you think?

Thanks for all the help!!

- - -Dennyd

 

[pmover]

Dennyd,

definitely, dynamic ranges are extremely useful for various uses in excel. one of which is charting data.

i'd practice/experiment with a workbook that has a small amount of data before implementing on workbook that you use.

while i've not investigated the particular feature listed in posted website, i can tell you that other information posted in ozgrid website has been helpful for me.

dynamic ranges, once created are extremely simple and useful. for example, i monitor vehicle mpg and chart the results (among other factors) using dynamic ranges. just enter the data and the results are plotted, no further action is needed.

good luck and good posting!

btw, joerd is definitely "an expert" in this forum.

-pmover

 

[joerd]

Thank you guys, makes me cheer up for the day!

Another site which has tons of charting examples, tutorials, tips & tricks is Jon Peltier's:

http://peltiertech.com/Excel/Charts/index.html

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.