Function that allows to plot graphs without 0s or N/A showing

Originally Posted by Richard Buttrey

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Apologies, I have adjusted the title.

Generally speaking (since we dont have a sample)...

If the question is how do you omit (aka not plot) null records as plot points in your chart, then it depends on your definition of a null value. If you can make the records you want to omit 0 or empty then there is an option built into the chart itself for how to treat them. If you open the select data dialog on the chart there is a button for "hidden and empty cells". You can treat an empty cell as a 0, a gap, or in some chart types connect data points with a line (like in a line chart).

By extension of the above dialog, it is inferred that the default is to also not include hidden cells in a chart. So you could with some arrangements also hide the rows/columns that you do not want to include in the chart so they are not plotted.

If the version of Excel in your profile thing isnt correct, Excel 2013+ also have built in filters to filter out data points from the chart (3 buttons to the right of a chart when its selected).

Otherwise your best bet is to setup a second table that either statically or dynamically has just the data you want and base the chart on that.

If I am mistaken and you simply do not want to show the label of 0 in your graph but still want the plot point there, then you can use number formatting on the labels to simply not show 0. Just format the labels number format using a custom format to something like:

General;-General;

Replace General and -General with whatever formats you prefer. In the above the first entry prior to the semi colon is how to represent a positive numeric value. The entry between the 1st and 2nd semi colon is how to represent a negative number. The entry (empty) after the second semi colon is what to do if the value is 0...and since its empty it displays nothing for 0 instead of 0.

Without an example I am afraid thats the most helpful I may be.

Hi,

Please find the sheet attached. I have highlighted the cells with the different functions.

Cell E14 has the function:

Please Login or Register  to view this content.

This comes back with a blank cell but it plots the data point as 0 as shown in the graph. If I use this function throughout I am able to calculate the max, averages, min etc. but the graph will give me data points I do not want.

Cell E15 has the function:

Please Login or Register  to view this content.

This comes back with #N/A and will not show up in the graph as shown. However, as can be seen in cell E28:30 this results in the Max, Average and Min being #N/A.

I would like to have one function that enables me to plot the graph and use other functions for analysis.

Hope that helps and is clear.

Thanks