Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Excel 2007 varialbe chart data ranges

תשובה Excel 2007 varialbe chart data ranges

  • Monday, July 09, 2012 10:03 PM
     
     
    Sign In to Vote
    0

    I have data that I have gathered in time series for various groupings. The length of time I have data for varies for each group. I want to create a line graph of the data, but I do not want the bars to drop to 0 when a series no longer has data.

    These charts are populated when I refresh data from a SQL server. I will eventually have a few hundred of these, and I will no longer be able to manually update the charts to make them look right.

    Bad Chart

    Bad Chart

    Good Chart

    Good Chart

    Example (weight by age in months- clearly dummy data)

                  1     2     3     4     5     6     7     8     9     10   11  12    13   14
    Group A 120 160 200 240 280 320 360 400 440 480 520 560 600 640
    Group B 120 160 200 240 280 320 360 400 440 480 520 
    Group C 120 160 200 240 280 320 360 400 440 
    Group D 120 160 200 240 280 320 360 
    Group E 120 160 200 240 280 
    Group F 120 160 200 240

     

All Replies

  • Wednesday, July 11, 2012 1:24 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,
    Please check whether there are any content such as formula in the cells which seem to be nothing in it. After I tried in the chart, if I had a formula which returns nothing to the cell, the chart will show 0.
    Just clear the content of the “null” cells to check the issue.

    Jaynet Zhang

    TechNet Community Support

     
  • Wednesday, July 11, 2012 12:11 PM
     
     
    Sign In to Vote
    0
    That is an excelent point. There is a formula that returns a null if there is no value for the dynamic data series for that time period. Because my time periods will grow over time, I am pointing at a chart that has a static format and data range. Is there another way to do this?
     
  • Monday, July 16, 2012 6:09 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

    Here’s a workaround to solve the issue:

    Create the chart based on the copy of the data. Just copy the range of the data and paste, then run the macro to set the cells to null, the cells which have formula and the value of which is null.

    The macro will make you select the range of the data you will used in chart.

    The code is below:

    Sub test_formula()

    Dim cell As Range

    Dim test_range As Range

    On Error Resume Next

    Set test_range = Application.InputBox( _

    Prompt:="Please select the range you want to remove the formula", _

    Title:=Title, _

    Default:=ActiveCell.Address, _

    Type:=8) 'Range selection

    For Each cell In test_range

        If cell.HasFormula And cell.Value = "" Then

            cell.Value = ""

            cell.Formula = ""       

        End If

    Next

    End Sub

    Jaynet Zhang

    TechNet Community Support