View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.charting
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Growing/Shrinking/Selective Chart Data

THANK YOU very much Jon!!! That is just Awesome!



"Jon Peltier" wrote:

The order doesn't matter. The calculated array will be treated like this:

21
92
71
69
36
64
62
#N/A
76
71
48
43
50
48
65
50
188
48
23
#N/A
#N/A
10

I posted a simple workbook illustrating the technique I described at
http://peltiertech.com/Sample/DynoChartForRob.zip.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Rob" wrote in message
...
Sorry Jon but it isn't working for me.... I'm still getting 200 lines of
info
in my charts. All the formulas are working because I do not get any errors
but the chart still shows all the items instead of just what I want. :(

Does it make a difference the the ones I want to exclude are not in/at the
end of the series? Because they are all in between one another. Example of
the values below...

Column B
21
92
71
69
36
64
62
4
76
71
48
43
50
48
65
50
188
48
23
2
0
10


"Jon Peltier" wrote:

1. First, put the X values to the left of the Y values. It's not strictly
necessary, but Excel by default uses the left column for X.

2. I would use a dynamic charting approach, which accounts for a changing
range of data:
http://peltiertech.com/WordPress/200...ynamic-charts/

3. I would deal with the condition of plotting only Y5 by defining
another
name. In my example I used a name "VertValues" for the dynamic Y value
range. I would define these names:

Name: TheMinimum
Refers To: =5

Name: VertValues2
Refers To: =IF(VertValuesTheMinimum,VertValues,NA())

Use VertValues2 in the chart rather than VertValues.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Rob" wrote in message
...
I'm looking for a Dynamic means of populating a bar chart.

Cloumn A (Rows 2-200) Has the Names (Y Axis) of each Item and Cloumn B
(Rows
2-200) (X Axis) has the Number Values. My problem is twofold... One,
every
week the Y axis list can grow or shrink and Two, I only want to show
those
that have an X Axis value that is greater than five. Anything less than
or
equal to five I would like to have hidden from showing in the chart.

Is it possible to have some sort of way or formula that can make this
possible in a chart or in pivot table?

Thanks in Advance.