• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Chart only categories that have a value above zero

M

msquared99

Member
I have a spreadsheet with data to track errors. Not all categories will have an error each month. Example: Items are in column A, Jan column B, Feb column C and, Mar column D


Jan Feb Mar

Item A 8 7 0

Item B 0 6 2

Item C 4 6 5

Item D 0 2 0

Item E 1 0 8


How do I make a dynamic graph to show only the categories that have values each month? I only want the data for each month as it occurs, for instance March only.


January would graph:

Item A 8

Item C 4

Item E 1


Then February comes and it would graph:

Item A 7

Item B 6

Item C 6

Item D 2


Then comes March:

Item B 2

Item C 5

Item E 8


I've been wracking brain on this. I've tried some formulas but the pie chart still picks up the blank or #N/A.


Many thanks,


Mike
 
Instead of using a pie chart, could you consider using a stacked column chart? Would let you more easily put all the data for different months on 1 graph, items with 0 errors aren't shown, and since your using a more easily readable chart than pie charts, you don't need to have value labels.
 
Hi Mike,

There are a few articles on this site that discuss dynamic charts. (Since the site's spam blockers block posts with URLs, I am not able to paste the links to them.) As such, try searching for "Dynamic Charts" using the search bar in the upper right side of this window.


Cheers,

Sajan.
 
Hi Mike ,


Can you check out the file here , and see if it helps ?


https://docs.google.com/file/d/0B0KMpuzr3MTVazFTSllFdDR0aUk/edit?usp=sharing


Narayan
 
I ended up keeping the pie chart. The stacked bar chart still plotted the #N/A's.


I used data validation to create a dropdown list of months.


I then put in two helper columns:


Helper A: Based on the month it pulls the data and assigns the value, blank, or #N/A

=IF(J3>0,B3,"")


=IF(HLOOKUP($J$1,$C$2:$E$7,G3,0)>0,HLOOKUP($J$1,$C$2:$E$7,G3,0),NA())


Helper B: If an item is blank or has #N/A then assign #N/A


=IF(I3>0,I3,NA())


=IF(J3>0,J3,NA())


Here is the catch. I tried having only one helper column by giving any cell under the month that had a value of zero a #N/A but no matter which graph I used it plotted the #N/A. I find that very odd since I have many other graphs where it does not plot #N/A. I retested it this morning with the same results.


Thanks,


Mike
 
Assuming you have the dates as the x-axis, how does a column chart plot the #N/A values? There should be nothing there...or do you have labels turned on?
 
Luke,


I figured it out, it is all in how you show the data labels. You have to delete the legend choose Label Options for each series then check Series Name and Value. By doing this it does not dhow the #N/A's.


Thanks,


Mike
 
You must log in or register to reply here.
Back
Top