Excel question: Charts

Discussion in 'Computers' started by Vince Maskeeper, Mar 10, 2004.

  1. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Guys,

    I have an excel table where I have charted my weightloss progress. In the same chart I have also counted calories, fat, carbs and physical activity (along with weight and measurement).

    The table has a row for each day since January 23rd, and a column for the various things I was keeping track of (Waist, Stomach, Chest, neck, Weight, Treadmill Time, Distance, MPH, Situps, Pushups, Calories, Fat, Carbs).

    Things like calories I chart everyday, however things like measurements are done once a week or so (sometimes less than, sometimes more- was just done when I had extra time in the morning).

    I would like to take this data up to this point and make some line/area charts. For example, I'd like to make a chart with measurements and weight over the course of the last 2 months.

    However, since i only have values for one row for every 6-7, everytime I try to make a chart it counts these empty values as 0-- making the chart look more like a pulse monitor (all the way at 0 for a week, then jumps to 235, all the way at 0 for a week then jumps to 233, etc.)

    I don't know excel very well- but I would like to find a way for it to ignor the empty values, but kind of keep the space for those days-- so i can see the full 60 days but it will only plot points for the days I have values.

    Any help?
     
  2. Andrew Pratt

    Andrew Pratt Producer

    Joined:
    Dec 8, 1998
    Messages:
    3,806
    Likes Received:
    0
    Vince you could use a bar chart instead of a line.
     
  3. Jeff_Blanch

    Jeff_Blanch Agent

    Joined:
    Dec 12, 2001
    Messages:
    32
    Likes Received:
    0
    One way to do it is to plot as an XY scatter. If you want to see a line you can then add a polynomial trendline. It won't be perfect, but if you want to keep the days, it will always think the empty days are data points.
     
  4. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    I'll just do it by hand, no biggie.

    Seems odd that technology can't help me, lol.

    -Vince
     
  5. Mike Sogge

    Mike Sogge Stunt Coordinator

    Joined:
    Nov 24, 2000
    Messages:
    85
    Likes Received:
    0
    Using the XY scatter plot with interconnecting lines...

    - For the data sets which have values for each day, create a series that uses all the dates for the X value, and all the data values for the Y values.

    - For the data sets which have sporadic values, create a series that has uses only the dates which have corresponding data values for the X values (make multiple cell selections with the CTRL key). Do the same for Y values.

    Doing this will allow you to track multiple datasets on the same chart w/o creating goofy results.
     
  6. Dan Mercier

    Dan Mercier Stunt Coordinator

    Joined:
    Aug 24, 1999
    Messages:
    56
    Likes Received:
    0
    Vince, if you want you could always post the spreadsheet
    data here and some of us could take a crack at it for you.
    I think Mike pretty much has listed what you have to do but
    I also have a few idea's I would need to try.

    You don't mind posting your weight to all of us do you?
    Just kidding. I'll play with this when I get off work as I
    need to do some graphing for other spreadsheets I have any.
    I'll get back to you with anything I find.

    Dan
     
  7. Vince Maskeeper

    Vince Maskeeper Producer

    Joined:
    Jan 18, 1999
    Messages:
    6,499
    Likes Received:
    0
    Well, the sheet is here:
    http://www.musicianassist.com/htf/diet.xls

    and I'm looking to basically create this
    [​IMG]

    and then a second chart for just weight, but also incuding ALL dates, not just ones with values.
     
  8. Kelvin Tucker

    Kelvin Tucker Stunt Coordinator

    Joined:
    Sep 17, 1999
    Messages:
    59
    Likes Received:
    0
    Charting Tips
    I was able to plot a line chart correctly by changing your black entries into "=NA()" entries. Seems that Excel will not plot those, in general.

    This may be what you need to almost get there. M$ does not make things like this easy, and the only way I know to go all the way is to program it in VBA ....
     

Share This Page