1. Guest,
    If you need help getting to know Xenforo, please see our guide here. If you have feedback or questions, please post those here.
    Dismiss Notice

Excel question: Charts

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

  1. Vince Maskeeper

    Vince Maskeeper Well-Known Member

    Joined:
    Jan 18, 1999
    Messages:
    6,505
    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 Well-Known Member

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

    Jeff_Blanch Active Member

    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 Well-Known Member

    Joined:
    Jan 18, 1999
    Messages:
    6,505
    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 Well-Known Member

    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 Well-Known Member

    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 Well-Known Member

    Joined:
    Jan 18, 1999
    Messages:
    6,505
    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 Well-Known Member

    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