Charting Selective Data

[bgetson]

I am trying to create a chart of monthly percent gain across a given
year, and I have separated the data into two rows:

Jan Feb Mar Apr ........... Dec
#N/A #N/A 5% 12% .......... 0%

The #N/A signifies that there is no data for the given months, and the
0% signifies that the month is not completed. They are a result of the
formula being used to grab the data.

I'm not sure of what method is the best, but I simply want a chart
that only displays the months that contain a percent value (other than
0%). Any help would be greatly appreciated with this problem.

Thank you.
-bgetson

 

[Guest]

Why don't you create the chart with the data you have and then when there is
a new month to add simply highlight that data and either click and drag it
onto the chart or use copy and paste special to add the data
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Jon Peltier]

So he has to continually update the chart? Better to make a dynamic chart:

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

- Jon

 

[Jon Peltier]

Change the formula so instead of returning 0 for an unfinished month, it
returns NA(), which looks like your #N/A in the first couple cells.

- Jon

 

[bgetson]

Thank you for your help, and sorry for my delay.

Jon, you're right, I want to create a dynamic chart. I've had some
success with that in the past; however, I can't figure out how to get
that to work with this problem.

The formulas themselves are dynamic - they constantly update
themselves for each year and month, but I don't know how to make my
chart dynamic as well. Whenever I try, I usually end up graphing the
#N/A data, which gives me blank values on my chart.

Can you give me some more directed help so that I no longer grab the
#N/A data to chart? I want my x-axis to only include months with real
data.

Thank you again.
-bgetson

 

[Jon Peltier]

Are the #N/A values all at the end of the range? If so, using the COUNT
function should let you size the range to include only the numbers. Suppose
the data in A1:B7 looks like this:

X Y
1 1
2 3
3 5
4 7
5 #N/A
6 #N/A

Define a name 'Yvalues' that refers to:
=OFFSET(B1,1,0,COUNT(B:B),1)

and a range 'Xvalues' that refers to:
=OFFSET(Yvalues,0,-1)

Set up the chart to use Sheet1!Xvalues and Sheet1!Yvalues, or edit the
series formula to read:

=SERIES(Sheet1!$B$1,Sheet1!Xvalues,Sheet1!Yvalues,1)

- Jon

 

[bgetson]

Thank you - this looks like exactly the help that I needed. In the
beginning, I think that I was just trying to do too much in one step.
Breaking it up until I got to this point helped me to better
understand what I needed to accomplish.

I can easily implement what you mentioned. You were a lot of help in
solving this simple problem which has been plaguing me for so long.

Thank you.
-bgetson