Hey Guys,
Bit of advice really.
I have a graph, and within the series there are some points which are '0'.
What I'm trying to do is if these points are 0 not to display the series on the x axis.
I know of 2 ways to do it
1) Hide row - This will take out that series point
2) Using Auto filter - Similar to above
This isn't always practicle as there is sometimes important data to the left, or right of the table which I don't want to hide using the above methods.
So is there any other way?
Attached is a sample work book, with sample data.
JJ,
you need to calculate the list of values you want to show, create a list of these values only, create a dynamic range that grows and shrinks automatically and then chart that dynamic range.
Clear as mud? See attached with formulas doing the work. No array formulas!!
cheers
Last edited by teylyn; 02-24-2010 at 05:18 PM.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Ok,
Works just how I need it to but I am stumped on 1 thing.
When I insert>name>define name and set ChartLabels to:
='Data (ALL)'!$O$3:INDEX('Data (ALL)'!$O:$O,COUNTA('Data (ALL)'!$O$3:$O$61)-2,1)
It seems that its Indexing to much as it highlights beyond the displayed text?
EDIT:
The above problem still exists, but I've found something else.
On the attached example if you change one of the catagories from 0 to a number, eg Orange.
The graph only has a defined range, not a dynamic one as its set and cuts data off?
Last edited by jj72uk; 02-25-2010 at 07:42 AM.
attached where?attached example
hard to tell without seeing your data.It seems that its Indexing to much as it highlights beyond the displayed text?
In the file I attached you can change any zero to a number and it will pop up in the chart.On the attached example if you change one of the catagories from 0 to a number, eg Orange.
The graph only has a defined range, not a dynamic one as its set and cuts data off?
Last edited by teylyn; 02-25-2010 at 07:48 AM.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Sorry Tey, I was referring to your own example, if you have more than 6 catagories the graph will cut it off.
Looks like your graph only shows a max of 6 catagories?
I've attached my sheet that I'm working on it in the Data (ALL) tab and its the bottom graph, the top graph is a pivot chart and will be deleted once this works
JJ, didn't get to download your chart, but realised I have to refine my INDEX skills.
replace the range definition for ChartLabels with the (volatile) Offset variant
=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!E:E)-1,1)
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Don't worry about my sheet as its a straight cross over of the demo sheet.
The OFFSET deffinatly helps and works.
The only down side is now we have blank catagories on the chart?
New take. Awake now. A few hours of sleep can do wonders.
formula for E2 and down
=IF(ROW(D2)-ROW(D$2)+1>COUNT(D$2:D$10),0,INDEX(A:A,SMALL(D$2:D$10,1+ROW(D2)-ROW(D$2))))
Index formula for range name ChartLabels
=Sheet1!$E$2:INDEX(Sheet1!$E$1:$E$10,MATCH("zzz",Sheet1!$E$1:$E$10,1),0)
see attached.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks