N/A Question for Chart

[Victor Li]

Hi All,

I want to know how do I make it so that for a set of 2 data bars on a chart, if the corresponding cells have N/A values, then it just won't show up on the bars at all. I am sure its just an if statement but not sure how to implement it, any advice would be well appreciated.

[MrShorty]

I'm not sure it is a simple If statement.

This is the most comprehensive discussion I know of on "gaps" in charts: https://peltiertech.com/mind-the-gap...g-empty-cells/ For bar and column charts, N/A entries will still be present in the chart. For that matter, just having the category present in the x values range causes there to be category in the chart. It might depend on exactly what you mean by "won't show up on the bars at all". In my experience, the only way to completely remove a category from a bar/column chart is to completely remove the category from the source data. You could use filters which should eliminate categories from the chart, or some kind of helper range where you make copies (by formula or VBA or other means) of all of the real data.

As suggested, if you will upload a good sample of your data and chart and explain exactly what you want to do, we can be more helpful.

[Victor Li]

Hi I have attached my sample workbook, so the goal is to have values on top of the bars for the last 2 set of existing values at all times everytime the chart updates. However, the values should be null or empty when the cell have a value of 0 or N/A.

[AliGW]

Did you read post #5? I will remove your latest post until you have addressed that issue.

[Victor Li]

ok, here's the link to that other post: https://www.mrexcel.com/forum/excel-...-question.html

[Victor Li]

Is that the one you are talking about?

[AliGW]

Thank you.

Please read our forum rules before posting again. For your convenience, here is a link to them: https://www.excelforum.com/forum-rul...rum-rules.html

[AliGW]

Is that the one you are talking about?

If there are other forums where you have asked the same question, you need to provide links to those, too. Are there?

[Victor Li]

Nope that's it.

[AliGW]

OK - thanks. I've linked to the forum rules in post #7 above so that you can make sure that you don't fall foul of anymore here.

You do not yet seem to have attached a workbook.

[Victor Li]

I thought I attached it during my previous post, I'll attach it again here.

[MrShorty]

The thread on the other forum has gone down the how to use IFERROR() path. What I see in your sample file is a simple "dynamic named range" "dynamic chart" kind of question. There are multiple tutorials on the net. Here are the first two I pulled up when I put 'dynamic charts with dynamic named ranges excel" into my favorite internet search engine:
http://www.criticaltosuccess.com/dyn...-named-ranges/
https://www.techrepublic.com/blog/mi...arts-in-excel/

The main difficulty is that, if you let the lookup functions return N/A, the count functions that tend to go into the OFFSET() function of the named range will not like the error values. It will probably be valuable to include the IFERROR() or IFNA() functions as described in the MrExcel thread to return a text string or something that the counting functions can ignore.

[Victor Li]

So I have to inherently change the formula in the cells regardless? There is no other way?

[MrShorty]

I'm optimistic that it can be done. Your counting function in the OFFSET() function will just need to handle those error values. You might try the COUNT() function (COUNT(A2:A10)) and see if it has any trouble with the error values. If it doesn't then you can simply use that. If it does, find a different counting function that doesn't have trouble with the error values. FWIW, in my copy of Excel, the COUNT() function did just fine ignoring the N/A errors, so the COUNT() function might be good enough.

[Victor Li]

Got it, thanks for the response guys!

Anyways here's the code that I'm working with right now:

Option Explicit

Sub Chart2_Click()
Dim mySrs As Series
Dim iPts As Long
Dim bLabeled As Boolean
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
For Each mySrs In ActiveChart.SeriesCollection
bLabeled = False
With mySrs
For iPts = .Points.Count To 1 Step -1
If bLabeled Then ' series has already received valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label if it's not the last point
mySrs.Points(iPts).HasDataLabel = False
On Error GoTo 0
Else ' series does not yet have valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label (linked labels otherwise resist reassignment)
mySrs.Points(iPts).HasDataLabel = False

' test for 0 or #N/A (empty)
If mySrs.Values(iPts) <> 0 Then
If Not IsEmpty(mySrs.Values(iPts)) Then
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=False, _
ShowCategoryName:=False, ShowValue:=True, _
AutoText:=True, LegendKey:=False
bLabeled = (Err.Number = 0)
End If
End If

' 2010 no error if point doesn't exist: label applied, but it's blank
If bLabeled Then bLabeled = (Len(mySrs.Points(iPts).DataLabel.Text) > 0)
If Not bLabeled Then
' remove blank label
mySrs.Points(iPts).HasDataLabel = False
End If
On Error GoTo 0
End If
Next
End With
Next
End If
End Sub

I was able to resolve the issue of not having N/A values or 0 values appear on my chart. However, I wanted to make sure that only the last set of bars on the chart have values, so the last problem right now is that if one of the bars have an empty value, the second last bar will have value on them, which I don't want. I think there is an internal conflict within the code.. any help would be much appreciated!

[MrShorty]

As you discovered earlier in this discussion, this forum can be strict about rules -- including the one about putting code in code tags. I recommend that you edit your last post to comply with the rule to avoid getting your topic locked.

Until someone can have a closer look at it, the next thing I would do (it will almost certainly be the first thing I do when I can look at this) is to add a stop statement somewhere very early in the procedure to enter debug mode. That should allow me to step through the procedure and follow along each statement and change to see if it is doing what I expect it to do, and help identify where the internal conflict might be. In case you don't want to wait for someone else, you might try debugging this yourself and see what you learn. If you are unfamiliar with VBA's debugging tools: http://www.cpearson.com/excel/DebuggingVBA.aspx

[AliGW]

I gave you a link to our forum rules in post #7. It would appear that you STILL have not read them.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

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