Forcing excel to ignore errors/blanks when plotting a chart

[spaghettyhoop]

Hello again all! Im after some help with a chart that uses dynamic data

I have a sheet that plots data on a chart to compare stats. It has up to 25 team members, and a variable category. All the data is pulled through from various charts using lookup.

The data looks like this in the layout...

Staff names at the top, are pulled through from a master sheets. All are lookups that change depending on what team is selected in a seperate drop down.
Category and category adjusted, are also lookups dependent on another seperate dropdown, with coresponding data using Lookup to populate.

Average, Average adjusted and guideline, simply reference an apropriate cell which updates based on unrelated criteria.

The graph itself plots Category, and category adjusted as Bars, and the other 3 as a straight line (to compare bars too)

[TABLE="class: grid, width: 850"]
<colgroup><col><col span="24"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Staff 1[/TD]
[TD]Staff 2[/TD]
[TD]Staff 3[/TD]
[TD]Staff 4[/TD]
[TD]Staff 5[/TD]
[TD]Staff 6[/TD]
[TD]Staff 7[/TD]
[TD]Staff 8[/TD]
[TD]Staff 9[/TD]
[TD]Staff 10[/TD]
[TD]Staff 11[/TD]
[TD]Staff 12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Category 1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Category 1 adjusted[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Average Adjusted[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Guideline[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

The problem I have is that the team will be all squished to one half on the smaller teams. the 12 in the example above obviously end up plotted just half way across.


Now. As far as I understand, if I use IF statements to evaluate any blank cells as an error, the chart should ignore them and not plot them. I set this up, making the staff names columns contain =IF(D3=0,NA(),"VLOOKUP formula")) (it equals zero as thats what the target contains when its "blank")

This correctly pulled an error through where needed. I then put similar in all of the data cells, referencing the "staff name" cell they coresponded to. For example. =IF(ISERROR(D3),NA(),"VLOOKUP formula")). This again gave errors exactly where I expected.

But the graph still plots the staff name, leaving my chart with the max 25 wide still, but instead of blanks now shows the NA error on the chart where the staff names go for the ones I want it to hide.


Im totally stumped, cos from what ive read this should work. Sorry for the long read, im really struggling to fix this!

 

[PeterThePan]

Just to make sure I understand you correctly: Your graph is supposed to show at maximum 25 data points but in many cases it will be less, right? Then you want to have for example 12 with the other 13 ignored completely (i.e. not even there as an empty data points) in the graph, right?

I dont know a straightforward solution to this but I know that there is a command which tells a graph to pick only a certain range of a data sheet for a graph. So for example if staff 1-25 would be time points (1990-2015) I've seen graphs where you can tell Excel that you want to see time 1995-2005 for example. If you find out about this, you could count the 'n' not-NAs in your row with the Staffs and then have Excel start at B2 in your case and take the first 'n' collumns into the graph, right? I've never worked with this command and I couldn't google it quickly but maybe this is the right direction?

I could also use the answer to this problem so: upvote! Can some pro here help?

 

[spaghettyhoop]

Yeah you have understood my problem correctly I want it too basically ignore all empty/error cells completely.

Ive googled it extensively, and it seems to be as simple as making any blanks return as an error using an if statement, and then excel ignores them completely automatically. However mine isnt doing that and its driving me nuts ha!

I get your idea, and that would also be a sollution in theory. Its not a method ive tried but im about to try looking into that too.