Excel question: Charts (1 Viewer)

[Vince Maskeeper]

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?

 

[Andrew Pratt]

Vince you could use a bar chart instead of a line.

 

[Jeff_Blanch]

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.

 

[Vince Maskeeper]

I'll just do it by hand, no biggie.

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

-Vince

 

[Mike Sogge]

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.

 

[Dan Mercier]

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

 

[Vince Maskeeper]

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.

 

[Kelvin Tucker]

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 ....