Excel question: Charts

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

Joined:
Jan 18, 1999
Messages:
6,499
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 Producer

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

3. Jeff_Blanch Agent

Joined:
Dec 12, 2001
Messages:
32
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.

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

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

-Vince

5. Mike Sogge Stunt Coordinator

Joined:
Nov 24, 2000
Messages:
85
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 Stunt Coordinator

Joined:
Aug 24, 1999
Messages:
56
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

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

and I'm looking to basically create this

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

Joined:
Sep 17, 1999
Messages:
59