Graph is plotting #N/A

[blakius84]

Hi guys, this is my first post and I hoping someone can help me.

I have created a data spreadsheet that automatically filters out data according to if statements and index function. I am left with a basic table with a number of actual figures and some #N/A values. I have used the function =IF(A1="",NA(),A1), to return back #N/A if there are no values. As there are multiple variables in the data tables I am using I need the graphs to be able to plot a single value up to 30 values, unfortunately it keeps plotting all 30 values regardless of it being a true value or a #N/A. I have done similar graphs in the past, where this does work perfectly but for some reason it is not working now. Any help would great.

[martindwilson]

post your non working chart with the data

[blakius84]

Hi, thanks for the reply.

I have attached the non-working data with a simple display of values that would come out.

Basically you choose the PM (Project Manager) and it will list all of there of projects in their portfolio and graph them. As PM's can have from 1 to 30 projects i need to be able to graph all 30 points, majority of the time there will be a number of #N/A if the PM does not have 30 projects.

Overall i want to graph the "Actual Totals" and "Forecast Totals" as a start and will possibly move on to graphing other similar totals.

[Steve N.]

I converted your data range to a 'Table'. See if it does what you need. If you type a new project number "Project 18" into cell B24 a new row is created to accomodate all your data elements. Your formulas will aslo auto calculate in each column.

Will this work for you?

[blakius84]

Cheers for that Steve,

Unfortunately it does not work for me. the sample sheet i attached prior is a small part of the workbook i am working on (for obvious reasons i cannot attach that spreadsheet). I am using a few functions to pull the data i require for each PM. At the moment if no project exists i have it returning a #N/A value as Excel apparently does not plt #N/A, which in this case it does.

I am now looking using a macros to auto deselect the #N/A value with the filtering tool and the graph does work that way.

It would nice if i could run it all automatically without macros.

I just bedazzles me why excel is graphing #N/A...

[jeffreybrown]

Are you looking for some dynamic named ranges?

[blakius84]

Hi Jeff,

i am not familar with dynamic name ranges, but i wont to be able to graph rows 7-48 (i.e. Actual Totals Q6:Q48), if a #N/A pops up i down want it to graph it.

[jeffreybrown]

What is the sample I posted doing that you do not want?

[blakius84]

Yes the graph is working from what i can see, but i cannot see what data you have selected. In the graph the data is referring back to =[0]!MyActuals and =[0]!MyForecasts, which is not working when i open the spreadsheet.

Have you actually named the data range that i want graph?

[Steve N.]

Hmmmmm... Not sure what you're looking for then. Both solotions keep the #N/A from plotting on the graph.

Data privacy considered, can you provide another clue that may help?

[jeffreybrown]

When you look at the chart >> select data you will have the axis and two series

Axis Label Range =Sheet1!$B$7:$B$48
Series values: =Sheet1!$P$7:$P$48
Series values: =Sheet1!$Q$7:$Q$48

If you select >> Ctrl + F3 >> you will see three dynamic named ranges.

In the chart data, everything to the right of the ! point will be replaced with the named range

=Sheet1!MyAxis
=Sheet1!MyForcasts
=Sheet1!MyActuals

[blakius84]

Try the attached.

I have made alittle more like what i am doing with some mock-up figures. You can see how i looking at a sepearte table and asking it to return either a true value if true and a #N/A if not a true value (i.e. ="").

So from this is there anyway for the #N/A not be graphed. But as data is enetered into the cells it will need to plot it.

[jeffreybrown]

Yes, #N/A is a method not to graph a point, but your series is still looking at the entire range. This is why you use dynamic named ranges so they can grow and shrink with the data. Unless I am totally off you will not get there in the way you are trying.

Look at sheet2 which might be a good example of using the #N/A.

[blakius84]

I can now see how u r using dynamic name ranges. i have never used dynamic before, so i will have play with that to sort it out.

I did keep getting errors on mine as it would not update if the graph was changed. Either way i will be able to use what u have done on the graphs. I also wasn't aware you could use dynamic or even a name range inside of the data selection for the graph. I am still learning on graphs but i appreciate your help.