If there is a hyphen in graph data do not display 0

1
votes

I have data that shows if people said they had a problem in a survey, e.g.

     Shop           Problem         Month
     Argos          Yes             January
     Argos          Yes             February
     Argos          Yes             January
     Argos          No              January
     Argos          No              January
     Argos          Yes             February
     Argos          No              February
     Argos          Yes             January
     Argos          No              February
     Argos          Yes             February
     Argos          No              February
     Argos          No              January
     Argos          Yes             February
     Argos          No              April
     Argos          No              April
     Argos          No              April
     B&Q            No              January
     B&Q            No              January
     B&Q            Yes             January

With this data I have a formula that calculates the percentage of problems per month, so for January there were 6 surveys and 3 of them said they had problems so it was 50%.

This data is displayed on a bar chart, so it would show 50% for January, 57% for February, and 0% for April. Although I can show there are 0% problems for April using the Data Labels on the chart, the formula returns a "-" if there is no data, e.g. for March it would return a "-" as there is not any data, but this displays on the graphs as 0%.

So is there a way that I can include all of the months (January, February, March, and April) and show that there are 0% of problems for some months but there is no data for others - ideally by showing in the Data Label a "-" for the months with no data?

It is also worth noting that the chart only shows the data for one shop at a time, and the shop it depends on is selected by the user using a dropdown box - so, as an example, whilst there is no data for Argos in March, there maybe data for B&Q in March but no data for B&Q in April.

2
excelgraphchartsbar-chart
If there are not too many, you can manually select the individual data label, right click and select Edit Text and type in whatever you want. – Scott Holtzman
But, although I only used the one shop in the example, the graph depends upon what shop is selected in a drop down menu - so it might be that March has no data for this shop but for another it could be that February has no data. – Ben Smith
right, in that case you would have to adjust each time, which doesn't sound ideal ... I will keep looking at it ... is vba an option? – Scott Holtzman
The spreadsheet would be sent out to other people so it wouldn't be ideal for them to have to go into the data/calculation tabs to see if a month had any data. Yes VBA is an option, I appreciate your help. – Ben Smith
what type of chart is it? – Scott Holtzman

2 Answers

1
votes

I'm not sure about older versions of Excel, but Excel 2013 has a data label option "Value from Cell" that basically does what you want

enter image description here

EDIT

So I looked at Excel 2010 and I don't think it has this feature. If you want to avoid VBA, here are a couple options:

1.) Use blank values for missing data... enter image description here

2.) You can also do formatting tricks by defining a custom format like this...

#0.##; "negative shows this"; "zero shows this"; "All text is shows this"

The problem with this is that the series values that charts use require numeric values or blanks, so the text formatting doesn't carry through and you're left with a zero format instead, since text gets converted to zero for chart data.

enter image description here

3.) Then, of course, you also have the option of brute forcing it with VBA, something like this...

Private Sub Worksheet_Calculate()
    'Loops through each chart object
    For Each obj In ActiveSheet.ChartObjects
        'This syntax can also be used to grab a specific chart without the loop
        Set sc = ActiveSheet.ChartObjects(obj.Name).Chart.SeriesCollection
        'Loops through each series per chart
        For i = 1 To sc.Count
            SetLabelsToTrueSourceValue sc.Item(i)
        Next i
    Next
End Sub
Private Sub SetLabelsToTrueSourceValue(ByRef sr As Series)
    'Get source data range addresses from formula
    Dim SourceData() As String
    A = Split(sr.Formula, ",")
    'Get actual non-formatted values from value range
    Dim Values() As Variant
    Values = Range(A(2)).Value2
    i = 1
    On Error Resume Next 'Avoid fatality if no data label present
    For Each pt In sr.Points
        pt.DataLabel.Text = Values(i, 1)
        i = i + 1
    Next
End Sub
0
votes

I have provided some VBA code to accomplish this for you. The code is based on the sheet I have shown here, so you may need to adjust it for your own data set.

enter image description here

The code is below. Please pay close attention to the comments, because you will need to edit to fit your specific workbook. You can make a button (as shown in the picture) and assign the macro to the button and run it before you send the charts out to distribution, so it displays the correct data labels.

Sub UpdateLabels()

Dim cht As ChartObject, srs As Series, pts As Point
Dim wsC As Worksheet, wsD As Worksheet

Set wsC = Sheets("sheet-chart-is-on") ' change to the correct sheet name for you
Set wsD = Sheets("sheet-data-is-on") ' change to correct sheet name for you

Set cht = wsC.ChartObjects("Chart 2") 'change to the correct chart name for you

With cht

    For Each srs In .Chart.SeriesCollection

        Dim i As Long

        i = 1

        For Each pts In srs.Points

            Dim s As String
            s = wsD.Cells(i, 8).Value ' based on data range being from H1:H7, if you need to adjust the column and or row you can do so in the Cells arguments

            If s <> "-" Then s = Format(s, "#0%") 'formats as pct, you can change this by changing what is in ""

            srs.Points(i).DataLabel.Text = s

            i = i + 1

        Next

    Next

End With

End Sub

All that said, place the code inside a new module in the VBE. Here's a small tutorial (plus more) on how to do that.