Help with Charting
Help with Charting
(OP)
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
Thanks in advance.
- - -Dennyd





RE: Help with Charting
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
RE: Help with Charting
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Help with Charting
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
RE: Help with Charting
Steve Braune
Tank Industry Consultants
www.tankindustry.com
RE: Help with Charting
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 =NA() function, so in you plotted dataset, you'll need something like =IF(A1=0,NA(),A1)
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.
RE: Help with Charting
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
RE: Help with Charting
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
RE: Help with Charting
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.